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

需求：统计最近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

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

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

两个方案,第一就是前段去处理,第二就是数据库自己维护一张日历表,作为参考值 left join 就行了.这个日历表你自己维护. 一年365条数据.

在select 里面可以加需要的字段的，比如max（name） 等，其他的你还想要什么？如果需要数据转换的话也可以外面再包装，看需要

我用的自己表随便写了一下 你自己参照一下
select a.click_date,ifnull(b.count,0) as count
from (
    SELECT curdate() 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
                 union all
    SELECT date_sub(curdate(), interval 10 day) as click_date
) a left join (
  select date(ORDERDATE) as datetime, count(*) as count
  from ey_order_h
  group by date(ORDERDATE)
) b on a.click_date = b.datetime;