如何按周分组并以每日为列统计Employee表的新增员工数

需要对Employee表按周分组，统计每周内各工作日的新增员工数量，结果需包含以下字段：

• Week：显示每周的起止日期，格式为[周起始日期] - [周结束日期]（示例格式：11/26/2023 - 12/02/2023）
• Sunday至Saturday：分别对应周日到周六当天的新增员工数量
• Total：统计该周的新增员工总数

示例结果 #

输入表Employee结构与数据 #

解决方案 #

MySQL版本 #

SELECT
    CONCAT(
        DATE_FORMAT(week_start, '%m/%d/%Y'),
        ' - ',
        DATE_FORMAT(week_start + INTERVAL 6 DAY, '%m/%d/%Y')
    ) AS Week,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 1 THEN 1 ELSE 0 END) AS Sunday,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 2 THEN 1 ELSE 0 END) AS Monday,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 3 THEN 1 ELSE 0 END) AS Tuesday,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 4 THEN 1 ELSE 0 END) AS Wednesday,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 5 THEN 1 ELSE 0 END) AS Thursday,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 6 THEN 1 ELSE 0 END) AS Friday,
    SUM(CASE WHEN DAYOFWEEK(JoiningDate) = 7 THEN 1 ELSE 0 END) AS Saturday,
    COUNT(*) AS Total
FROM (
    SELECT
        JoiningDate,
        DATE_SUB(JoiningDate, INTERVAL (DAYOFWEEK(JoiningDate) - 1) DAY) AS week_start
    FROM Employee
) AS weekly_data
GROUP BY week_start
ORDER BY week_start;

SQL Server版本 #

SELECT
    CONCAT(
        FORMAT(week_start, 'MM/dd/yyyy'),
        ' - ',
        FORMAT(DATEADD(DAY, 6, week_start), 'MM/dd/yyyy')
    ) AS Week,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 1 THEN 1 ELSE 0 END) AS Sunday,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 2 THEN 1 ELSE 0 END) AS Monday,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 3 THEN 1 ELSE 0 END) AS Tuesday,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 4 THEN 1 ELSE 0 END) AS Wednesday,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 5 THEN 1 ELSE 0 END) AS Thursday,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 6 THEN 1 ELSE 0 END) AS Friday,
    SUM(CASE WHEN DATEPART(WEEKDAY, JoiningDate) = 7 THEN 1 ELSE 0 END) AS Saturday,
    COUNT(*) AS Total
FROM (
    SELECT
        JoiningDate,
        DATEADD(DAY, -(DATEPART(WEEKDAY, JoiningDate) - 1), JoiningDate) AS week_start
    FROM Employee
) AS weekly_data
GROUP BY week_start
ORDER BY week_start;

内容的提问来源于stack exchange，提问作者Rohit Rehni