SQL中如何将分存的日、月、年字段按周分组？

这个问题太常见了——当日期被拆成日、月、年三个单独字段存储时，确实没法直接用常规的单日期字段分组方法。核心思路其实很简单：先把三个字段拼接成完整的日期类型，再用数据库对应的周分组函数处理。下面针对几种主流数据库给出具体实现：

MySQL 实现 #

首先用STR_TO_DATE把拼接后的字符串转成日期，然后用YEARWEEK函数生成带年份的周编号（避免跨年时周数混淆，比如2023年第52周和2024年第1周不会被当成同一组）：

SELECT
  YEARWEEK(STR_TO_DATE(CONCAT(Year, '-', Month, '-', Day), '%Y-%m-%d')) AS week_identifier,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY week_identifier
ORDER BY week_identifier;

如果需要更直观的周起始日期，可以用DATE_FORMAT配合YEARWEEK来转换：

SELECT
  DATE_FORMAT(STR_TO_DATE(CONCAT(Year, '-', Month, '-', Day), '%Y-%m-%d'), '%Y-%u-1') AS week_start_date,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY week_start_date
ORDER BY week_start_date;

PostgreSQL 实现 #

PostgreSQL有个非常方便的MAKE_DATE函数，直接传入年、月、日就能生成日期，然后用DATE_TRUNC截断到周级别：

SELECT
  DATE_TRUNC('week', MAKE_DATE(Year, Month, Day)) AS week_start,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY week_start
ORDER BY week_start;

默认情况下DATE_TRUNC('week')会把周一作为每周的起始日，如果需要改成周日，可以调整数据库的datestyle参数，或者用EXTRACT生成带年份的周编号：

SELECT
  CONCAT(EXTRACT(YEAR FROM MAKE_DATE(Year, Month, Day)), '-', EXTRACT(WEEK FROM MAKE_DATE(Year, Month, Day))) AS week_id,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY week_id
ORDER BY week_id;

SQL Server 实现 #

用DATEFROMPARTS函数直接构造日期，然后用DATEPART获取周数，建议拼接年份和周数作为分组键：

SELECT
  CONCAT(DATEPART(YEAR, DATEFROMPARTS(Year, Month, Day)), '-', DATEPART(WEEK, DATEFROMPARTS(Year, Month, Day))) AS week_id,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY CONCAT(DATEPART(YEAR, DATEFROMPARTS(Year, Month, Day)), '-', DATEPART(WEEK, DATEFROMPARTS(Year, Month, Day)))
ORDER BY week_id;

如果需要周起始日期，可以用DATEADD和DATEDIFF组合计算：

SELECT
  DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(Year, Month, Day)), 0) AS week_start_date,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(Year, Month, Day)), 0)
ORDER BY week_start_date;

Oracle 实现 #

用TO_DATE把拼接后的字符串转成日期，推荐用ISO标准的周格式IYYY-IW（每年最多53周，周一为周起始），避免不同年份周数重叠：

SELECT
  TO_CHAR(TO_DATE(Year || '-' || Month || '-' || Day, 'YYYY-MM-DD'), 'IYYY-IW') AS iso_week,
  COUNT(*) AS total_records
FROM your_table_name
GROUP BY TO_CHAR(TO_DATE(Year || '-' || Month || '-' || Day, 'YYYY-MM-DD'), 'IYYY-IW')
ORDER BY iso_week;

小提示 #

• 如果你的Day/Month字段是一位数（比如示例中的2或1），不用额外补零，上面的函数都能自动识别并正确解析日期。
• 确保Year、Month、Day字段是数值类型或可转换为数值的字符类型，避免出现日期解析错误。

内容的提问来源于stack exchange，提问作者L..