鍍金池/ 問答/HTML5  數(shù)據(jù)庫/ sql語句查詢本周每天的數(shù)據(jù)量，如果沒有該天，數(shù)據(jù)量默認(rèn)為0；

sql語句查詢本周每天的數(shù)據(jù)量，如果沒有該天，數(shù)據(jù)量默認(rèn)為0；

我的sql語句

SELECT 
   WEEKDAY(osr.created_date) AS weekDay, 
   IF (count(1) IS NULL, 0, count(1)) AS num 
FROM osr_work AS osr
INNER JOIN el_user AS el  ON el.id = 141
WHERE 
    osr.teacher_id = 141 AND 
    YEARWEEK(date_format(osr.created_date,'%Y-%m-%d')) = YEARWEEK(now()) 
GROUP BY WEEKDAY(osr.created_date);

結(jié)果：圖片描述

0-6:分別代表周一至周日
我希望得到周一至周日所有的數(shù)據(jù)，因?yàn)橹芤缓椭芪?，周六，周日沒有數(shù)據(jù)，就默認(rèn)num為0；我該如何寫，求指導(dǎo)?。。?/p>

回答
編輯回答
別硬撐

建議建一張輔佐表 weekDay 0-6，num 全部為0， left join輔佐表就可以了。

2018年1月26日 11:54
編輯回答
情殺

改用LEFT JOIN就行了呀。

2017年4月3日 21:56
編輯回答
冷溫柔

日期left join 你要查詢的條件，然后在從這個(gè)獲取的數(shù)據(jù)集中查詢數(shù)據(jù)，然后判斷num是不是null就行了

2018年5月7日 10:37
編輯回答
咕嚕嚕
SELECT
    osr_weekday.weekDay,
    count(1) AS num
FROM
    osr_work osr,osr_weekday
WHERE
    WEEKDAY(osr.created_date) = osr_weekday.weekDay AND
    osr.teacher_id = 141 AND 
    YEARWEEK(date_format(osr.created_date,'%Y-%m-%d')) = YEARWEEK(now())
GROUP BY osr_weekday.weekDay


union ALL

SELECT 
    osr_weekday.weekDay ,
    0 
FROM 
    osr_weekday 
WHERE 
    osr_weekday.weekDay not in (
        SELECT 
            osr_weekday.weekDay
        FROM 
            osr_work osr,
            osr_weekday
        WHERE
            WEEKDAY(osr.created_date) = osr_weekday.weekDay AND
            osr.teacher_id = 141 AND 
            YEARWEEK(date_format(osr.created_date,'%Y-%m-%d')) = YEARWEEK(now())
        GROUP BY osr_weekday.weekDay
 )
ORDER BY weekDay

想把not in優(yōu)化，折騰了半天還是老樣子～有帶飛的嗎

2017年9月2日 18:34
編輯回答
神經(jīng)質(zhì)
select day, max(num)
from (
    SELECT 
       WEEKDAY(osr.created_date) AS day, 
       IF (count(1) IS NULL, 0, count(1)) AS num 
    FROM osr_work AS osr
    INNER JOIN el_user AS el  ON el.id = 141
    WHERE 
        osr.teacher_id = 141 AND 
        YEARWEEK(date_format(osr.created_date,'%Y-%m-%d')) = YEARWEEK(now()) 
    GROUP BY WEEKDAY(osr.created_date)
    UNION
    SELECT * from (
        select 0 day, 0 num
        UNION
        select 1, 0  
        UNION
        select 2, 0  
        UNION
        select 3, 0  
        UNION
        select 4, 0  
        UNION
        select 5, 0
        UNION
        select 6, 0  
    ) b
) c 
group by day
2017年10月20日 15:03