坑爹的mysql

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

下面找出status = -2的范围

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

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

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 

下面才是

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 

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

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