测试表：用户登录日志
login_log ---------------------------------------- |log_id | customer_id | add_time ---------------------------------------- |1 | 1 | 2022-09-10 ---------------------------------------- |2 | 2 | 2022-09-10 ---------------------------------------- |3 | 1 | 2022-09-11 ---------------------------------------- |3 | 4 | 2022-09-11 ----------------------------------------
1.计算每个客户id的访问总数
mysql> SELECT customer_id,COUNT(*) as mcount FROMlogin_log
-> GROUP BY customer_id
结果：
---------------------------------------- | customer_id | mcount ---------------------------------------- |1 | 2 ---------------------------------------- |2 | 1 ---------------------------------------- |4 | 1 ----------------------------------------
2.只获取用户id = 1,2的总数
mysql> SELECT * FROM login_log
-> GROUP BY SUBSTR(customer_id,1,2)
3.获取登录2次及以上的客户
mysql> SELECT * FROM login_log
-> GROUP BY SUBSTR(customer_id,1,2)
-> HAVING COUNT(*) > 1
222