坑爹的mysql

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

kingdelee

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

阅读量75

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/kingdelee/article/details/84484149

版权

1.用case when中那些想当然的错误

下面找出status = -2的范围


select hid, status from news_host where update_time > '2013-09-28' group by status;

[img]http://dl2.iteye.com/upload/attachment/0089/7497/260addb8-957b-30ba-bd55-865d64ada415.jpg[/img]

下面的结果绝对不是你想要的


SELECT hid, SUM(CASE status WHEN status=-2 THEN scount ELSE 0 END) AS 'preFailCount'  FROM ( SELECT hid, status, COUNT(status) AS scount 
	FROM news_host 
	WHERE status !=-10 AND hid > 0 AND update_time >= '2013-09-28 00:00:00' AND update_time < "2013-09-29 00:00:00" GROUP BY hid,status) AS tb_h GROUP BY hid

[img]http://dl2.iteye.com/upload/attachment/0089/7501/ec32b4fa-1467-3786-bce2-3c83245013fd.jpg[/img]

下面才是


SELECT hid, SUM(CASE status WHEN -2 THEN scount ELSE 0 END) AS 'preFailCount'  FROM ( SELECT hid, status, COUNT(status) AS scount 
	FROM news_host 
	WHERE status !=-10 AND hid > 0 AND update_time >= '2013-09-28 00:00:00' AND update_time < "2013-09-29 00:00:00" GROUP BY hid,status) AS tb_h GROUP BY hid

[img]http://dl2.iteye.com/upload/attachment/0089/7499/7a46495f-cb74-3301-8f27-8c5de37c205c.jpg[/img]

这种也对，以后用这样子的


SELECT hid,SUM(CASE  WHEN status = -2  THEN scount ELSE 0 END) AS 'preFailCount'
FROM ( SELECT hid, status, COUNT(status) AS scount 
	FROM news_host 
	WHERE status !=-10 AND hid > 0 AND update_time >= '2013-09-28 00:00:00' AND update_time < "2013-09-29 00:00:00" GROUP BY hid,status) AS tb_h GROUP BY hid

还有这个


SELECT hid, SUM(CASE  WHEN status BETWEEN -6 AND -1 THEN scount ELSE 0 END) AS 'preFailCount'  FROM ( SELECT hid, status, COUNT(status) AS scount 
	FROM news_host 
	WHERE status !=-10 AND hid > 0 AND update_time >= '2013-09-28 00:00:00' AND update_time < "2013-09-29 00:00:00" GROUP BY hid,status) AS tb_h GROUP BY hid