mysql case when

最新推荐文章于 2024-09-04 17:17:14 发布

原创最新推荐文章于 2024-09-04 17:17:14 发布 · 420 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #user #unix #join #null

SQL SERVER / Mysql 专栏收录该内容

25 篇文章

订阅专栏

本文介绍了一个具体的SQL查询案例，展示了如何从多个表中选择特定的数据，并通过复杂的条件筛选和排序来优化查询效率。重点关注了AdQualityTaskQueue和AdQualityTask表的联接操作，以及如何根据不同状态和优先级进行数据排序。

SELECT
	`AdQualityTaskQueue`.`id`,
	`AdQualityTaskQueue`.`ad_quality_task_id`,
	`AdQualityTaskQueue`.`ad_quality_workgroup_id`,
	`AdQualityTaskQueue`.`tag_id`,
	`AdQualityTaskQueue`.`created`,
	`AdQualityTaskQueue`.`checked`,
	`AdQualityTaskQueue`.`user_id`,
	`AdQualityTaskQueue`.`completion_time`,
	`AdQualityTaskQueue`.`status`,
	`AdQualityTaskQueue`.`refresh_times`,
	`AdQualityTaskQueue`.`lock_user`,
	`AdQualityTaskQueue`.`lock_expire`,
	`AdQualityTask`.`id`,
	`AdQualityTask`.`name`,
	`AdQualityTask`.`interval`,
	`AdQualityTask`.`duration`,
	`AdQualityTask`.`priority`,
	`AdQualityTask`.`ad_quality_workgroup_id`,
	`AdQualityTask`.`created`,
	`AdQualityTask`.`modified`
FROM
	`ad_quality_task_queues` AS `AdQualityTaskQueue`
LEFT JOIN `ad_quality_tasks` AS `AdQualityTask` ON(
	`AdQualityTaskQueue`.`ad_quality_task_id` = `AdQualityTask`.`id`
)
WHERE
	`AdQualityTaskQueue`.`ad_quality_workgroup_id` = 1
AND(
	(
		`AdQualityTaskQueue`.`lock_user` IS NULL
	)
	OR(
		`AdQualityTaskQueue`.`lock_user` = 162
	)
	OR(
		UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(
			`AdQualityTaskQueue`.`lock_expire`
		)>= 600
	)
)
AND `AdQualityTaskQueue`.`status` IN('DUE', 'OVERDUE')
AND `AdQualityTask`.`priority` IN('MEDIUM', 'HIGH')
ORDER BY
	CASE
WHEN AdQualityTaskQueue.`status` = 'OVERDUE'
AND `AdQualityTask`.`priority` = 'High' THEN
	1
WHEN AdQualityTaskQueue.`status` = 'OVERDUE'
AND `AdQualityTask`.`priority` = 'Medium' THEN
	2
WHEN AdQualityTaskQueue.`status` = 'DUE'
AND `AdQualityTask`.`priority` = 'High' THEN
	3
WHEN AdQualityTaskQueue.`status` = 'DUE'
AND `AdQualityTask`.`priority` = 'Medium' THEN
	4
END ASC,
 `AdQualityTaskQueue`.`checked` ASC
LIMIT 1

case field when xx then xx else xx end;

case when field == xx then xx else xx end;

两种用法区别在于NULL不能够用在第一种情况，因为不能用null通过比较符号< , >, ==, <> 比较。

只能用第二种用法：

case when field is null then xx else xx end;