查询当前时间10天内每天发生的数量SQL语句

SELECT data.click_date AS "time", IFNULL(alarm.count,0) AS `count`
FROM (
SELECT DATE_SUB(CURDATE(),INTERVAL 0 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 2 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 3 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 4 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 5 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 6 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 7 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 8 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 9 DAY) AS click_date )AS `data`
LEFT JOIN (
SELECT DATE(create_date) AS `datetime` ,COUNT(1) AS `count` 
FROM `xf_alarm` GROUP BY `datetime` 
) AS alarm ON data.click_date =alarm.datetime ORDER BY `time` DESC

查询每天的sql

SELECT DATE_FORMAT(create_date,'%Y-%m-%d') AS days,COUNT(*) COUNT FROM `xf_alarm` GROUP BY days;

多个count 查询

SELECT COUNT(1) AS count1, COUNT(IF(bf_status=0 OR bf_status=3,TRUE,NULL)) AS count2,COUNT(IF(bf_status=1 OR bf_status=2,TRUE,NULL)) AS count3 FROM `lt_pf_apply_for`