坑爹的sql

统计不同状态，不同超时状态的总数
select verify_user,timeout, count(timeout), sign_status from verify_sign_info where sign_time > '2014-01-08 00:00:00'  group by verify_user, sign_status, timeout;
+-------------+---------+----------------+-------------+
| verify_user | timeout | count(timeout) | sign_status |
+-------------+---------+----------------+-------------+
|   500000679 |       0 |             36 |           0 | 
|   500000679 |       0 |              9 |           2 | 
|   500000679 |       1 |              2 |           2 | 
|   500000679 |       0 |              9 |          30 | 
|   500000679 |       1 |              2 |          30 | 
|   500000679 |       0 |             18 |          31 | 
|   500001357 |       0 |              1 |           2 | 
|   500001357 |       0 |              2 |          31 | 
+-------------+---------+----------------+-------------+

SELECT uid, 
MAX(CASE WHEN timeout = 1 and status = 30 THEN quit_count ELSE 0 END) AS 'pre_time_out', 
MAX(CASE WHEN timeout = 1 and status = 31 THEN quit_count ELSE 0 END) AS 're_time_out', 
MAX(CASE WHEN status = 0 THEN quit_count ELSE 0 END) AS 'pre_quit_count',  
MAX(CASE WHEN status = 2 THEN quit_count ELSE 0 END) AS 're_quit_count' 
FROM (SELECT verify_user AS uid, timeout, COUNT(sign_status) AS quit_count, sign_status AS status FROM verify_sign_info WHERE sign_time > '2014-01-08 00:00:00' and  verify_user = 500000679 GROUP BY verify_user, sign_status, timeout) AS a GROUP BY uid;
+-----------+--------------+-------------+----------------+---------------+
| uid       | pre_time_out | re_time_out | pre_quit_count | re_quit_count |
+-----------+--------------+-------------+----------------+---------------+
| 500000679 |            4 |           7 |             36 |            11 | 
+-----------+--------------+-------------+----------------+---------------+
1 row in set (0.17 sec)

group_concat


不删除，有则更新无则插入

	@SQL("INSERT INTO $TABLE(card_id, update_time) VALUES(:1, :2) ON DUPLICATE KEY UPDATE update_time = :2, scount = scount + 1")
	int saveCardSearchInfo(@ShardBy long cardId, Date updateTime);