SQLite按客户ID与年份分别统计销售数据的SQL语句求助

原始数据表 #

期望统计结果 #

已尝试的SQL语句 #

最初的语句仅能按id汇总，无法区分年份：

SELECT id, SUM(pcs), SUM(dollars) FROM Table GROUP BY id

尝试的子查询语句执行失败：

SELECT id, 
(SELECT SUM(pcs) FROM Table WHERE id=id AND year=2021) AS pcs2021, 
(SELECT SUM(dollars) FROM Table WHERE id=id AND year=2021) AS dollars2021, 
(SELECT SUM(pcs) FROM Table WHERE id=id AND year=2022) AS pcs2022, 
(SELECT SUM(dollars) FROM Table WHERE id=id AND year=2022) AS dollars2022, 
FROM Table GROUP BY id

错误原因 #

1. 子查询中id=id属于自引用，无法关联外部查询的id，导致统计范围是全表符合年份的数据，而非对应id的记录；
2. 语句末尾多了一个逗号，存在语法错误。

正确的SQL语句 #

方案1：条件聚合（推荐，简洁高效） #

利用CASE WHEN在聚合时按年份筛选，直接生成目标格式结果：

SELECT 
    id,
    SUM(CASE WHEN year = 2021 THEN pcs ELSE 0 END) AS pcs2021,
    SUM(CASE WHEN year = 2021 THEN dollars ELSE 0 END) AS dollars2021,
    SUM(CASE WHEN year = 2022 THEN pcs ELSE 0 END) AS pcs2022,
    SUM(CASE WHEN year = 2022 THEN dollars ELSE 0 END) AS dollars2022
FROM 
    Table
GROUP BY 
    id;

方案2：分组后JOIN #

先按id和年份分组统计，再通过自连接合并结果，适合年份较多或需动态扩展的场景：

WITH yearly_stats AS (
    SELECT 
        id,
        year,
        SUM(pcs) AS total_pcs,
        SUM(dollars) AS total_dollars
    FROM 
        Table
    GROUP BY 
        id, year
)
SELECT 
    t1.id,
    t1.total_pcs AS pcs2021,
    t1.total_dollars AS dollars2021,
    t2.total_pcs AS pcs2022,
    t2.total_dollars AS dollars2022
FROM 
    yearly_stats t1
LEFT JOIN 
    yearly_stats t2 ON t1.id = t2.id AND t2.year = 2022
WHERE 
    t1.year = 2021
UNION ALL
SELECT 
    t2.id,
    0 AS pcs2021,
    0 AS dollars2021,
    t2.total_pcs AS pcs2022,
    t2.total_dollars AS dollars2022
FROM 
    yearly_stats t2
WHERE 
    t2.year = 2022
    AND NOT EXISTS (SELECT 1 FROM yearly_stats t1 WHERE t1.id = t2.id AND t1.year = 2021);

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