自定义大类分组的许可证统计：嵌套查询结果行结构调整及实现方案咨询

嘿，看起来你已经迈出了自定义分组统计的第一步，但遇到了结果行结构的问题——当前查询把两个分类的结果挤在了同一行，而你需要的是每个分类单独占一行的统计结果。我来帮你拆解问题，给出几种实用的解决方法，同时也会把你提到的临时表用法讲清楚，帮你理清思路：

问题根源：为什么结果会在同一行？ #

你当前的查询用了逗号连接两个子查询（这是SQL里旧版的交叉连接语法），它会把第一个子查询的结果和第二个子查询的结果做笛卡尔积，也就是横向拼接成一行。这不是你要的纵向合并多行结果的逻辑，所以需要调整写法。

方案1：用UNION ALL纵向合并子查询结果（快速适配你的现有代码） #

这是最贴近你当前写法的修改方式，只需要把多个独立的统计子查询用UNION ALL连接，就能让每个分类的结果单独占一行：

DECLARE @CATEGORY1 NVARCHAR(40) = 'Commercial - New'
DECLARE @CATEGORY2 NVARCHAR(40) = 'Commercial - Modifications'
DECLARE @CATEGORY3 NVARCHAR(40) = 'Multi-Family - New'
DECLARE @CATEGORY4 NVARCHAR(40) = 'Multi-Family - Modifications'
DECLARE @CATEGORY5 NVARCHAR(40) = 'Residential - New'
DECLARE @CATEGORY6 NVARCHAR(40) = 'Residential - Modifications'

-- 第一个分类的统计
SELECT 
    @CATEGORY4 AS 'Group Category',
    COUNT(DISTINCT P1.PermitNum) AS 'Count',
    SUM(P1.EstimatedValue) AS 'SUM'
FROM Permit P1 
WHERE P1.PermitTypeMasterID IN (57,60,59) 
  AND P1.CreatedDate >= '2025-01-01'

-- 用UNION ALL合并第二个分类的统计
UNION ALL

SELECT 
    @CATEGORY5 AS 'Group Category',
    COUNT(DISTINCT P2.PermitNum) AS 'Count',
    SUM(P2.EstimatedValue) AS 'SUM'
FROM Permit P2 
WHERE P2.PermitTypeMasterID IN (1,46,78,79) 
  AND P2.CreatedDate >= '2025-01-01'

-- 后续要加其他分类，继续按这个格式加UNION ALL和子查询即可

注意点： #

• UNION ALL要求所有子查询的列数、列类型、列顺序完全一致，所以每个子查询都要返回Group Category、Count、SUM三列
• 如果你想自动过滤掉重复的结果（这里几乎不会出现），可以用UNION代替，但UNION ALL的效率更高，因为它不会去重

方案2：用CASE WHEN + GROUP BY一次性统计（更高效简洁） #

这个方案只需要扫描一次Permit表（比多个子查询多次扫描表更高效），通过CASE语句动态给每个许可证类型分配分组类别，然后直接按类别聚合统计：

DECLARE @StartDate DATE = '2025-01-01'

SELECT
    -- 用CASE语句定义分组规则：给每个PermitTypeMasterID映射到对应的大类
    CASE 
        WHEN P.PermitTypeMasterID IN (57,60,59) THEN 'Multi-Family - Modifications'
        WHEN P.PermitTypeMasterID IN (1,46,78,79) THEN 'Residential - New'
        -- 在这里继续添加其他分类的映射规则
        -- WHEN P.PermitTypeMasterID IN (...) THEN 'Commercial - New'
        ELSE 'Other' -- 可选：处理未匹配到任何分组的许可证类型
    END AS 'Group Category',
    COUNT(DISTINCT P.PermitNum) AS 'Count',
    SUM(P.EstimatedValue) AS 'SUM'
FROM Permit P
WHERE P.CreatedDate >= @StartDate
  -- 可选：只统计需要的许可证类型，避免无关数据干扰
  AND P.PermitTypeMasterID IN (57,60,59,1,46,78,79)
-- 按上面的CASE分组规则聚合
GROUP BY
    CASE 
        WHEN P.PermitTypeMasterID IN (57,60,59) THEN 'Multi-Family - Modifications'
        WHEN P.PermitTypeMasterID IN (1,46,78,79) THEN 'Residential - New'
        ELSE 'Other'
    END

优势： #

• 只扫描一次表，数据量大时性能远好于多个子查询
• 分组逻辑集中在CASE语句里，后续修改分组规则只需要调整这里，维护更方便

方案3：用临时表管理分组映射（适合复杂分组规则） #

如果你的分组规则非常复杂，或者需要在多个查询中重复使用这个分组逻辑，临时表是个不错的选择——它能把“许可证类型→大类”的映射关系单独存储，让统计逻辑更清晰：

步骤1：创建临时表存储分组映射 #

-- 创建临时表（#开头的是会话级临时表，会话结束后自动销毁）
CREATE TABLE #PermitGroupMapping (
    PermitTypeMasterID INT,
    GroupCategory NVARCHAR(40)
)

-- 插入所有分组映射数据
INSERT INTO #PermitGroupMapping (PermitTypeMasterID, GroupCategory)
VALUES
(57, 'Multi-Family - Modifications'),
(60, 'Multi-Family - Modifications'),
(59, 'Multi-Family - Modifications'),
(1, 'Residential - New'),
(46, 'Residential - New'),
(78, 'Residential - New'),
(79, 'Residential - New')
-- 可以继续添加其他分类的映射，比如商业类、其他类

步骤2：关联临时表完成统计 #

DECLARE @StartDate DATE = '2025-01-01'

SELECT
    pg.GroupCategory AS 'Group Category',
    COUNT(DISTINCT P.PermitNum) AS 'Count',
    SUM(P.EstimatedValue) AS 'SUM'
FROM Permit P
-- 关联临时表，获取每个许可证对应的大类
JOIN #PermitGroupMapping pg ON P.PermitTypeMasterID = pg.PermitTypeMasterID
WHERE P.CreatedDate >= @StartDate
-- 按大类分组统计
GROUP BY pg.GroupCategory

-- 可选：手动删除临时表（会话结束后会自动销毁，所以不是必须的）
DROP TABLE #PermitGroupMapping

优势： #

• 分组规则和统计逻辑完全分离，修改分组只需要更新临时表的数据，不需要改统计查询
• 适合分组规则频繁变化、或者需要在多个查询中复用分组逻辑的场景

给你的选择建议 #

• 如果只是快速验证少量分类的统计，用**方案1（UNION ALL）**最直接，适配你现有的代码
• 大多数情况下，**方案2（CASE+GROUP BY）**是最优选择：简洁、高效、易维护
• 如果分组规则复杂或需要复用，再考虑方案3（临时表）

内容来源于stack exchange