将MS Access透视查询适配至SQL Server并补充统计列需求

DevOps 小满

2026-6-22

问题

将MS Access数据库迁移至SQL Server后，原有透视查询无法适配。原Access查询如下：

TRANSFORM FIRST(eff.efi) AS efici

SELECT eff.semana_ano, FIRST(eff.data), LAST(eff.data), ROUND(Avg(eff.efi), 2) AS media_semana
FROM 
    (SELECT dia_semana, semana_ano, efi 
     FROM tmp_lista) AS eff 
GROUP BY eff.semana_ano
PIVOT eff.ndia_semana

SQL Server中的表结构及数据：

CREATE TABLE tmp_lista
(   
    dia         date, 
    dia_semana  varchar(5), 
    semana_ano  int,
    efi         decimal(5,2) 
);

INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-01', 'D5', 5, 0.98);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-02', 'D6', 5, 0.5);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-03', 'D7', 5, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-04', 'D1', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-05', 'D2', 6, 0.49);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-06', 'D3', 6, 0.65);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-07', 'D4', 6, 0.3);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-08', 'D5', 6, 1.18);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-09', 'D6', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-10', 'D7', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-11', 'D1', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-12', 'D2', 7, 0.57);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-13', 'D3', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-14', 'D4', 7, 0.51);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-15', 'D5', 7, 0.65);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-16', 'D6', 7, 0.49);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-17', 'D7', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-18', 'D1', 8, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-19', 'D2', 8, 0.34);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-20', 'D3', 8, 0.46);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-21', 'D4', 8, 0.43);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-22', 'D5', 8, 0.32);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-23', 'D6', 8, 0.62);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-24', 'D7', 8, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-25', 'D1', 9, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-26', 'D2', 9, 0.62);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-27', 'D3', 9, 0.44);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-28', 'D4', 9, 0.54);

已完成基础透视查询适配：

SELECT
    semana_ano, D1, D2, D3, D4, D5, D6, D7 
FROM
    (SELECT dia_semana, semana_ano, efi FROM tmp_lista) AS dp 
PIVOT
    (SUM(efi) FOR dia_semana IN (D1, D2, D3, D4, D5, D6, D7)) result_dp;

现需补充周最小日期（min(dia)）、周最大日期（max(dia)）及7天平均值（avg(7 dias)）列，期望结果格式：

semana_ano|D1    |D2    |D3    |D4    |D5    |D6    |D7    |min(dia)  |max(dia)  |avg(7 dias)|
----------|------|------|------|------|------|------|------|----------|----------|-----------|
5         |[NULL]|[NULL]|[NULL]|[NULL]|  0.98|  0.50|[NULL]|2024-02-01|2024-02-03|   0.74|
6         |[NULL]|  0.49|  0.65|  0.30|  1.18|[NULL]|[NULL]|2024-02-04|2024-02-10|   0.66|
7         |[NULL]|  0.57|[NULL]|  0.51|  0.65|  0.49|[NULL]|2024-02-11|2024-02-17|   0.56|
8         |[NULL]|  0.34|  0.46|  0.43|  0.32|  0.62|[NULL]|2024-02-18|2024-02-24|   0.43|
9         |[NULL]|  0.62|  0.44|  0.54|[NULL]|[NULL]|[NULL]|2024-02-25|2024-02-28|   0.53|

解决方案

通过先聚合周维度统计数据，再与透视结果关联的方式实现需求，完整SQL语句如下：

WITH WeeklyStats AS (
    SELECT
        semana_ano,
        MIN(dia) AS min_dia,
        MAX(dia) AS max_dia,
        ROUND(AVG(efi), 2) AS avg_7dias
    FROM tmp_lista
    GROUP BY semana_ano
),
PivotedData AS (
    SELECT
        semana_ano,
        D1, D2, D3, D4, D5, D6, D7
    FROM (
        SELECT dia_semana, semana_ano, efi 
        FROM tmp_lista
    ) AS dp
    PIVOT (
        SUM(efi) FOR dia_semana IN (D1, D2, D3, D4, D5, D6, D7)
    ) AS result_dp
)
SELECT
    ps.semana_ano,
    ps.D1, ps.D2, ps.D3, ps.D4, ps.D5, ps.D6, ps.D7,
    ws.min_dia,
    ws.max_dia,
    ws.avg_7dias
FROM PivotedData ps
JOIN WeeklyStats ws ON ps.semana_ano = ws.semana_ano
ORDER BY ps.semana_ano;

说明

WeeklyStats CTE：按semana_ano分组，计算每周的最小日期、最大日期和efi的平均值（保留两位小数），对应原Access查询的media_semana。
PivotedData CTE：复用已完成的基础透视查询，得到按天维度透视的结果。
关联查询：将两个CTE通过semana_ano关联，合并得到完整的期望结果。

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

将MS Access透视查询适配至SQL Server并补充统计列需求

说明 #

说明