明白了!你希望新增的字段 `WeekLabel` 不仅标识“本周”、“过去第1周”等语义标签,还要**显示对应的完整周区间**,例如:
- **本周**:2025-07-28 至 2025-08-03
- **过去第1周**:2025-07-21 至 2025-07-27
- **过去第2周**:2025-07-14 至 2025-07-20
- ...
- **过去第13周**:2025-04-28 至 2025-05-04
---
## ✅ 一、更新建表语句(新增字段)
我们将在 `dm2yj_kdyy_week_tj` 表中新增两个字段,用于记录周的起止日期和带日期区间的标签:
```sql
ALTER TABLE dm2yj_kdyy_week_tj
ADD COLUMN WeekStartDate DATE COMMENT '本周起始日期(周一)',
ADD COLUMN WeekEndDate DATE COMMENT '本周结束日期(周日)',
ADD COLUMN WeekLabelWithDate VARCHAR(50) COMMENT '带日期的周标签,如:本周(2025-07-28 - 2025-08-03)';
```
---
## ✅ 二、更新抽取逻辑(计算带日期的 WeekLabel)
以下是完整的插入语句,包含周起止日期和带日期格式的 `WeekLabelWithDate` 字段:
```sql
INSERT INTO dm2yj_kdyy_week_tj (
GzWeek, YjFlDm, YjFl, YjKs, ExamType, CheckFl,
Kd_Cnt, Kd_Jf_Cnt, Yy_Dt_Cnt, Yy_Gt_Cnt, Yy_Cnt1, Yy_Cnt2, Yy_Cnt3, Yy_Cnt4,
ZdYy_Xm_Cnt, Kd_Wc_Cnt, Kd_Wc_Cnt1,
Time1_Wc_Rate, Time2_Wc_Rate, Time3_Wc_Rate, Time4_Wc_Rate, Time5_Wc_Rate, Time6_Wc_Rate,
Dj_Wjc_Cnt, Jc_Wbg_Cnt, Yy_Wc_Cnt, Yy_Wc_Rate, OpenSl_Cnt, KcSl_Cnt, KcSl2_Cnt,
Time1_Wc_Num, Time2_Wc_Num, Time3_Wc_Num, Time4_Wc_Num, Time5_Wc_Num, Time6_Wc_Num,
Time_Wc_Den, Yy_Wc_Num, Yy_Wc_Den,
WeekStartDate, WeekEndDate, WeekLabelWithDate
)
SELECT
CONCAT(YEAR(a.GzRq), '-W', LPAD(WEEK(a.GzRq, 1), 2, '0')) AS GzWeek,
a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.CheckFl,
SUM(a.Kd_Cnt), SUM(a.Kd_Jf_Cnt), SUM(a.Yy_Dt_Cnt), SUM(a.Yy_Gt_Cnt),
SUM(a.Yy_Cnt1), SUM(a.Yy_Cnt2), SUM(a.Yy_Cnt3), SUM(a.Yy_Cnt4),
SUM(a.ZdYy_Xm_Cnt), SUM(a.Kd_Wc_Cnt), SUM(a.Kd_Wc_Cnt1),
ROUND(SUM(a.Time1_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4),
ROUND(SUM(a.Time2_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4),
ROUND(SUM(a.Time3_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4),
ROUND(SUM(a.Time4_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4),
ROUND(SUM(a.Time5_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4),
ROUND(SUM(a.Time6_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4),
SUM(a.Dj_Wjc_Cnt), SUM(a.Jc_Wbg_Cnt),
SUM(a.Yy_Wc_Cnt),
ROUND(SUM(a.Yy_Wc_Num) / NULLIF(SUM(a.Yy_Wc_Den), 0), 4),
IFNULL(SUM(c.OpenSl_Cnt), 0),
IF(IFNULL(SUM(c.OpenSl_Cnt), 0) - IFNULL(SUM(b.Yy_cnt), 0) < 0, 0,
IFNULL(SUM(c.OpenSl_Cnt), 0) - IFNULL(SUM(b.Yy_cnt), 0)),
IF(IFNULL(SUM(e.OpenSl_Cnt), 0) - IFNULL(SUM(d.Yy_cnt), 0) < 0, 0,
IFNULL(SUM(e.OpenSl_Cnt), 0) - IFNULL(SUM(d.Yy_cnt), 0)),
SUM(a.Time1_Wc_Num), SUM(a.Time2_Wc_Num), SUM(a.Time3_Wc_Num), SUM(a.Time4_Wc_Num),
SUM(a.Time5_Wc_Num), SUM(a.Time6_Wc_Num), SUM(a.Time_Wc_Den),
SUM(a.Yy_Wc_Num), SUM(a.Yy_Wc_Den),
DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY AS WeekStartDate,
DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY AS WeekEndDate,
CASE
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 0 AND 6 THEN
CONCAT('本周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 7 AND 13 THEN
CONCAT('过去第1周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 14 AND 20 THEN
CONCAT('过去第2周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 21 AND 27 THEN
CONCAT('过去第3周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 28 AND 34 THEN
CONCAT('过去第4周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 35 AND 41 THEN
CONCAT('过去第5周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 42 AND 48 THEN
CONCAT('过去第6周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 49 AND 55 THEN
CONCAT('过去第7周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 56 AND 62 THEN
CONCAT('过去第8周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 63 AND 69 THEN
CONCAT('过去第9周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 70 AND 76 THEN
CONCAT('过去第10周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 77 AND 83 THEN
CONCAT('过去第11周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 84 AND 90 THEN
CONCAT('过去第12周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 91 AND 97 THEN
CONCAT('过去第13周(',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'),
' - ',
DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'),
')')
ELSE '历史周'
END AS WeekLabelWithDate
FROM dw2yj_kdyy_tj a
-- 此处保留原 JOIN 逻辑
-- 省略部分 JOIN 子句,保持与你原始逻辑一致
WHERE a.GzRq >= CURRENT_DATE - INTERVAL 14 WEEK
GROUP BY
CONCAT(YEAR(a.GzRq), '-W', LPAD(WEEK(a.GzRq, 1), 2, '0')),
a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.CheckFl,
DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY,
WeekLabelWithDate
ORDER BY WeekStartDate DESC;
```
---
## ✅ 三、关键说明
- `WeekStartDate`:本周周一日期。
- `WeekEndDate`:本周周日日期。
- `WeekLabelWithDate`:格式如 `本周(2025-07-28 - 2025-08-03)`,便于在 BI 报表中直接使用。
- 使用 `WEEKDAY()` 函数获取周一日期,确保符合 ISO 周标准。
- 所有字段都支持动态生成,无需硬编码。
---
## ✅ 四、相关问题