坑爹的sql

最新推荐文章于 2024-06-13 18:21:44 发布

kingdelee

最新推荐文章于 2024-06-13 18:21:44 发布

阅读量95

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/kingdelee/article/details/84518619

本文介绍了一种使用SQL进行数据统计的方法，包括不同状态和超时状态的总数统计，并通过复杂查询实现了更细致的数据分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


统计不同状态，不同超时状态的总数
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);