MySQL : Group by all weeks of a year with 0 included

Question

我对一些 mysql 代码有疑问。

我有一张表引用了一些员工的到达日期和项目 ID。我想计算企业中的所有条目并按周分组。

这一刻，我能有这样的结果

Project ID | Week | Count
1 | 2019-S01 | 2
1 | 2019-S03 | 1
2 | 2019-S01 | 1
2 | 2019-S04 | 5
2 | 2019-S05 | 3
2 | 2019-S06 | 2

这很好，但我想返回所有周数，即使一周的结果为 0 也是如此:

Project ID | Week | Count
1 | 2019-S01 | 2
1 | 2019-S02 | 0
1 | 2019-S03 | 1
...
2 | 2019-S01 | 1
2 | 2019-S02 | 0
2 | 2019-S03 | 0
2 | 2019-S04 | 5
2 | 2019-S05 | 3
2 | 2019-S06 | 2
...

这是我的实际代码:

SELECT
        AP.SECTION_ANALYTIQUE AS SECTION,
        FS_GET_FORMAT_SEMAINE(AP.DATE_ARRIVEE_PROJET) AS SEMAINE,
        Count(*) AS COMPTE
FROM
        RT00_AFFECTATIONS_PREV AP
WHERE 
        (AP.DATE_ARRIVEE_PROJET <= CURDATE() AND Year(AP.DATE_ARRIVEE_PROJET) >= Year(CURDATE())) 
GROUP BY 
        SECTION, SEMAINE
ORDER BY 
        SECTION

有人有解决办法吗？

我在网上搜索了一些东西，但没有找到任何准确的东西:(

提前致谢! :)

Answer 1

满足此要求的经典方法是创建一个引用表来存储所有可能的星期。

create table all_weeks(week varchar(8) primary key);
insert into all_weeks values
    ('2019-S01'), ('2019-S02'), ('2019-S03'), ('2019-S04'), ('2019-S05'), ('2019-S06');

完成此操作后，您可以使用 CROSS JOIN 和 LEFT JOIN 与原始表生成所有可能部分和周的笛卡尔积。

根据您的代码片段，这应该如下所示:

 SELECT
    s.section_analytique AS section,
    w.week AS semaine,
    COUNT(ap.section_analytique) AS compte
FROM
    (SELECT DISTINCT section_analytique from rt00_affectations_prev) s
    CROSS JOIN all_weeks w
    LEFT JOIN rt00_affectations_prev ap
        ON s.section_analytique = ap.section_analytique AND w.week = FS_GET_FORMAT_SEMAINE(ap.date_arrivee_projet)
GROUP BY s.section_analytique, w.week
ORDER BY s.section_analytique

PS:注意不要在 WHERE 子句中将条件放在原始表上:这会破坏 LEFT JOIN 的目的。如果您需要进行一些过滤，请改用引用表(您可能需要向其中添加几列，例如一周的开始日期)。