求问大神们，统计最近10天的数据sql语句怎么写？

Question

需求：统计最近10天内的学生考勤情况。

问题是：当某一天没有数据也要显示日期和个数为0。

我现在的sql语句是这样的，只会显示有数据的日期和数据，求各位大神帮我优化一下。

SELECT
    DATE_FORMAT( deta.TIME, '%Y-%m-%d' ) AS dateTime,
    sum( CASE WHEN deta.STATUS = '6' THEN '1' ELSE '0' END ) leaveAmount,
    sum( CASE WHEN deta.STATUS = '1' THEN '1' ELSE '0' END ) attendanceAmount
FROM
    tb_detail deta 
WHERE
    DATE_SUB( CURDATE(), INTERVAL 10 DAY ) <= date( deta.TIME ) 
GROUP BY
    dateTime 
ORDER BY
    dateTime ASC

Answer 1

SELECT
    tb1.date_str AS dateTime,
    tb2.leaveAmount AS leaveAmount,
    tb2.attendanceAmount AS attendanceAmount
FROM(
    SELECT @d := DATE_ADD(@d, interval - 1 day) AS date_str FROM (SELECT @d := DATE_ADD('结束时间'， interval 0 day) FROM tb_detail) temp1 WHERE @d > '开始时间'
) tb1
LEFT JOIN (
    SELECT
    DATE_FORMAT( deta.TIME, '%Y-%m-%d' ) AS dateTime,
    sum( CASE WHEN deta.STATUS = '6' THEN '1' ELSE '0' END ) leaveAmount,
    sum( CASE WHEN deta.STATUS = '1' THEN '1' ELSE '0' END ) attendanceAmount
FROM
    tb_detail deta 
WHERE
    DATE_SUB( CURDATE(), INTERVAL 10 DAY ) <= date( deta.TIME ) 
GROUP BY
    dateTime 
ORDER BY
    dateTime ASC
) tb2
ON tb1.date_str = tb2.dateTime

左边生成日期列表连接右边查询的数据