SQL基于Project、Unit列去重后新增9-999标识临时记录的方法咨询

调整后完整代码 #

WITH cteDistinctProjectUnit AS 
(
    -- 先按需求过滤数据，再取唯一的Project+Unit组合及公共维度字段
    SELECT DISTINCT 
         JobScrub.[OUnit Code]
        ,JobScrub.[Project_ProjID]
        ,JobScrub.[Project Code]
        ,JobScrub.[Project Name]
        ,JobScrub.[Phase]
        ,JobScrub.[Unit]
        ,JobScrub.[Unit Status] 
    FROM [someDBName].[db].[tJobScrub] as JobScrub
    INNER JOIN [someDBName].[dbRems].[project] as B with(nolock) 
        on B.[projid] = JobScrub.[Project_ProjID]
    WHERE B.[compcode] = '1007' -- 过滤条件提前，确保只生成需要的compcode对应的汇总行
)
-- 第一部分：新增的Summary虚拟行
SELECT  
     'Summary' AS [Line Type]
    ,[OUnit Code]
    ,[Project_ProjID]
    ,[Project Code]
    ,[Project Name]
    ,[Phase]
    ,[Unit]
    ,[Unit Status]
    , NULL AS [Model]
    , NULL AS [Model Description]
    , NULL AS [Elev]
    , NULL AS [Model + Elev]
    , NULL AS [Actual Close]
    , '9-999' AS [Major Code] -- 按需求修改为指定的MajorCode
    , '汇总行' AS [Major Description]
    , NULL AS [Latest Estimate]
    , NULL AS [Invoiced Amount]
    , NULL AS [Open Balance]
    , NULL AS [Invoiced + Open]
    , NULL AS [Lot] -- 补充排序需要的字段，设为NULL即可
    , NULL AS [Minor Code] -- 补充排序需要的字段，设为NULL即可
FROM cteDistinctProjectUnit

UNION ALL

-- 第二部分：原有全部业务记录
SELECT 
   JobScrub.[Line Type]
  ,JobScrub.[OUnit Code]
  ,JobScrub.[Project_ProjID]
  ,JobScrub.[Project Code]
  ,JobScrub.[Project Name]
  ,JobScrub.[Phase]
  ,JobScrub.[Unit]
  ,JobScrub.[Unit Status]
  ,JobScrub.[Model]
  ,JobScrub.[Model Description]
  ,JobScrub.[Elev]
  ,JobScrub.[Model + Elev]
  ,JobScrub.[Actual Close]
  ,JobScrub.[Major Code]
  ,JobScrub.[Major Description]
  ,JobScrub.[Latest Estimate]
  ,JobScrub.[Invoiced Amount]
  ,JobScrub.[Open Balance]
  ,JobScrub.[Invoiced + Open]
  ,JobScrub.[Lot]
  ,JobScrub.[Minor Code]
FROM [someDBName].[db].[tJobScrub] as JobScrub
INNER JOIN [someDBName].[dbRems].[project] as B with(nolock) 
    on B.[projid] = JobScrub.[Project_ProjID]
WHERE B.[compcode] = '1007'

-- 排序放在整个查询最后，确保所有行按规则排序
ORDER BY [Project Code], [Unit], [Line Type] DESC, [Lot], [Major Code], [Minor Code]

核心修改点说明 #

• 过滤逻辑前置：将compcode='1007'的过滤条件同时作用于汇总行生成逻辑和原有数据查询逻辑，避免出现多余的其他主体数据；同时将原LEFT JOIN改为INNER JOIN，原写法中LEFT JOIN后加右表过滤条件实际等价于INNER JOIN，显式修改更易读。
• 汇总行参数修正：将汇总行的MajorCode从原代码的0-0000修改为需求要求的9-999，符合业务规则。
• 字段对齐：为汇总行补充排序所需的Lot、Minor Code字段，避免UNION ALL后字段数量不匹配、排序报错的问题。
• 排序逻辑调整：将ORDER BY放在整个查询语句的最末尾，确保所有行（含汇总行）统一按规则排序，新增的[Line Type] DESC可以让Summary汇总行排在对应Project+Unit组合的所有明细行之前，也可以根据需要调整为ASC放到明细之后。
• 去重逻辑对齐：CTE直接基于过滤后的业务数据取唯一Project+Unit组合，确保不会生成不存在业务数据的冗余汇总行。

内容的提问来源于stack exchange，提问作者David.L