如何将SQL中当年与上年百分比计算的两个查询合并为单个查询

合并查询的实现方案 #

有两种常用方案可以实现合并查询，都只需要扫描一次表，比两次独立查询执行效率更高： #

方案1：按年份分组返回两行结果 #

这种方案会返回两行数据，每行分别对应年份和对应的百分比结果，适合需要同时展示多年份数据的场景：

DECLARE @temptable TABLE (Id INT,FaceId INT,TimeStamp DATETIME)

INSERT INTO @temptable (Id,FaceId,TimeStamp) VALUES 
(1,1,'2021-08-31 18:29:27.103'),(2,1,'2021-05-28 18:29:27.103'),(3,2,'2020-08-31 18:29:27.103'),(4,1,'2020-03-15 18:29:27.103')

SELECT 
    YEAR(h.TimeStamp) AS 统计年份,
    CAST((100 *(2*(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END))) /
    (2 *(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END) +AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN h.FaceId=3 THEN 1.0 ELSE 0 END))) AS DECIMAL(5,2)) AS 百分比
FROM @temptable h 
WHERE YEAR(h.TimeStamp) IN (YEAR(GETDATE()), YEAR(GETDATE())-1)
GROUP BY YEAR(h.TimeStamp)
ORDER BY 统计年份 DESC

WHERE条件会先过滤只保留当年和上年的数据，再按年份分组计算，避免扫描多余的历史数据。 #

方案2：单行返回当年和上年两个结果 #

这种方案会在一行返回两个计算结果，不需要额外处理就能直接拿到两个值，适合需要把两个结果作为字段直接使用的场景：

DECLARE @temptable TABLE (Id INT,FaceId INT,TimeStamp DATETIME)

INSERT INTO @temptable (Id,FaceId,TimeStamp) VALUES 
(1,1,'2021-08-31 18:29:27.103'),(2,1,'2021-05-28 18:29:27.103'),(3,2,'2020-08-31 18:29:27.103'),(4,1,'2020-03-15 18:29:27.103')

SELECT 
    CAST((100 *(2*(AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE()) AND h.FaceId=1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE()) AND h.FaceId=2 THEN 1.0 ELSE 0 END))) /
    (2 *(AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE()) AND h.FaceId=1 THEN 1.0 ELSE 0 END) +AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE()) AND h.FaceId=2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE()) AND h.FaceId=3 THEN 1.0 ELSE 0 END))) AS DECIMAL(5,2)) AS 当年百分比,
    CAST((100 *(2*(AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE())-1 AND h.FaceId=1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE())-1 AND h.FaceId=2 THEN 1.0 ELSE 0 END))) /
    (2 *(AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE())-1 AND h.FaceId=1 THEN 1.0 ELSE 0 END) +AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE())-1 AND h.FaceId=2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN YEAR(h.TimeStamp) = YEAR(GETDATE())-1 AND h.FaceId=3 THEN 1.0 ELSE 0 END))) AS DECIMAL(5,2)) AS 上年百分比
FROM @temptable h 
WHERE YEAR(h.TimeStamp) IN (YEAR(GETDATE()), YEAR(GETDATE())-1)

如果表数据量很大，建议给TimeStamp字段加索引，能进一步提升查询效率。

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