求按周一至周日统计周数、年份及数值和并插入表的SQL方案

首先核心需求是将日期按「周一至周日」为一周的规则分组，统计每一周的年份、周数及数字总和，再将结果插入目标表。下面针对几种主流数据库给出具体实现：

1. MySQL 实现 #

MySQL的WEEK()函数支持指定周起始规则，参数1代表「周一为一周第一天」，正好匹配需求：

先验证统计结果 #

SELECT
  WEEK(`Date`, 1) AS WeekNumber,
  YEAR(`Date`) AS Year,
  SUM(Number) AS SumOfNumber
FROM your_source_table  -- 替换成你的源表名
GROUP BY YEAR(`Date`), WEEK(`Date`, 1)
ORDER BY Year, WeekNumber;

插入到指定数据表 #

如果目标表（比如weekly_stats）已经存在且字段匹配，直接用INSERT ... SELECT：

INSERT INTO weekly_stats (WeekNumber, Year, SumOfNumber)
SELECT
  WEEK(`Date`, 1) AS WeekNumber,
  YEAR(`Date`) AS Year,
  SUM(Number) AS SumOfNumber
FROM your_source_table
GROUP BY YEAR(`Date`), WEEK(`Date`, 1)
ORDER BY Year, WeekNumber;

2. SQL Server 实现 #

SQL Server中可以用DATEPART(ISO_WEEK, ...)获取「周一至周日」的ISO周数，这是符合国际标准的周规则：

验证统计结果 #

SELECT
  DATEPART(ISO_WEEK, [Date]) AS WeekNumber,
  YEAR([Date]) AS Year,
  SUM(Number) AS SumOfNumber
FROM your_source_table
GROUP BY YEAR([Date]), DATEPART(ISO_WEEK, [Date])
ORDER BY Year, WeekNumber;

插入到指定数据表 #

INSERT INTO weekly_stats (WeekNumber, Year, SumOfNumber)
SELECT
  DATEPART(ISO_WEEK, [Date]) AS WeekNumber,
  YEAR([Date]) AS Year,
  SUM(Number) AS SumOfNumber
FROM your_source_table
GROUP BY YEAR([Date]), DATEPART(ISO_WEEK, [Date])
ORDER BY Year, WeekNumber;

3. PostgreSQL 实现 #

PostgreSQL用EXTRACT(ISO_WEEK FROM ...)来获取周一起始的周数，需要注意类型转换：

验证统计结果 #

SELECT
  EXTRACT(ISO_WEEK FROM "Date")::INTEGER AS WeekNumber,
  EXTRACT(YEAR FROM "Date")::INTEGER AS Year,
  SUM(Number) AS SumOfNumber
FROM your_source_table
GROUP BY EXTRACT(YEAR FROM "Date"), EXTRACT(ISO_WEEK FROM "Date")
ORDER BY Year, WeekNumber;

插入到指定数据表 #

INSERT INTO weekly_stats (WeekNumber, Year, SumOfNumber)
SELECT
  EXTRACT(ISO_WEEK FROM "Date")::INTEGER AS WeekNumber,
  EXTRACT(YEAR FROM "Date")::INTEGER AS Year,
  SUM(Number) AS SumOfNumber
FROM your_source_table
GROUP BY EXTRACT(YEAR FROM "Date"), EXTRACT(ISO_WEEK FROM "Date")
ORDER BY Year, WeekNumber;

关键注意点 #

• 替换SQL中的your_source_table为你实际的源表名，weekly_stats为目标表名
• 确保目标表的字段类型与统计结果匹配（比如WeekNumber和Year用整数类型，SumOfNumber用数值类型）
• 以上SQL的统计结果完全匹配你给出的示例：比如2018-01-01（周一）和2018-01-04同属第1周，总和15；2018-02-28属于第9周，总和10等

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