柘月十七
2022-06-03 11:38
采纳率: 100%
浏览 68

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

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;

img

以上是单个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;

img

请求支援!感谢

2条回答 默认 最新

相关推荐 更多相似问题