多表连接时，where多条件对查询出的结果造成乱序影响？

Question

select DATE_FORMAT(a.click_date,'%d') days,ifnull(b.count,0) as count
from (
    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
) a left join (
SELECT
    date(create_time) datetime,
    count(*) count
FROM t_video 
WHERE DATE_SUB( date_sub(curdate(), interval 1 day), INTERVAL 7 DAY ) <= date(create_time) 
GROUP BY datetime
) b on a.click_date = b.datetime;

以上是单个where过滤条件的SQL和结果，这样的一个结果顺序是我想要的（因为是日期，今天起过去的一周内的统计），但是我多加一个过滤条件就出现了乱序问题了，不理解下面这样是什么原因。

select DATE_FORMAT(a.click_date,'%d') days,ifnull(b.count,0) as count
from (
    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
) a left join (
SELECT
    date(create_time) datetime,
    count(*) count
FROM t_video 
WHERE DATE_SUB( date_sub(curdate(), interval 1 day), INTERVAL 7 DAY ) <= date(create_time) 
    and category_id = (select id from t_category where category_name ='考证')
GROUP BY datetime
) b on a.click_date = b.datetime;

请求支援！感谢

Answer 1

你最外层加个排序就行了 order by days asc