首页 > 代码库 > 使用for xml path 分组查询

使用for xml path 分组查询

2024-08-23 22:36:09   221人阅读

 SELECT 
OLevel,
WorkOrgID,
WorkOrgName,
PlanNum,
PlanFinishNum,
PlanUnFinishNum,
PlanCanceledNum,
PlanChangedNum,
OrtherNum,
 PlanTime,
 ActualTime,
 RIGHT(PlanIDs,LEN(PlanIDs)-1) AS PlanIDs
 FROM (SELECT  
        ( CASE MIN(Parent)
            WHEN ‘E13744A1-7E2C-42E8-8E7B-4D52F9E04A08‘ THEN MIN(ROLevel)
            ELSE MIN(OLevel)
          END ) AS OLevel ,
        ( CASE MIN(Parent)
            WHEN ‘E13744A1-7E2C-42E8-8E7B-4D52F9E04A08‘
            THEN MIN(RegulatoryOrgID)
            ELSE WorkOrgID
          END ) AS WorkOrgID ,
        ( CASE MIN(Parent)
            WHEN ‘E13744A1-7E2C-42E8-8E7B-4D52F9E04A08‘ THEN MIN(RoName)
            ELSE WorkOrgName
          END ) AS WorkOrgName ,
        MIN(RegulatoryOrgID) AS RegulatoryOrgID ,
        COUNT(1) AS PlanNum ,
        SUM(CASE FormStatusCode
              WHEN ‘Finish‘ THEN 1
              ELSE 0
            END) AS PlanFinishNum ,
        COUNT(CASE WHEN FormStatusCode != ‘Finish‘ THEN 1
                   ELSE 0
              END) AS PlanUnFinishNum ,
        SUM(CASE WHEN FormStatusCode = ‘Canceled‘ THEN 1
                 ELSE 0
            END) AS PlanCanceledNum ,
        SUM(CASE WHEN FormStatusCode = ‘Changed‘ THEN 1
                 ELSE 0
            END) AS PlanChangedNum ,
        SUM(CASE WHEN FormStatusCode IN ( ‘Approved‘, ‘Begining‘, ‘Begin‘ )
                 THEN 1
                 ELSE 0
            END) AS OrtherNum ,
        SUM(CASE WHEN DATEDIFF(mi, PlanBeginTime, PlanEndTime) < 0 THEN 0
                 WHEN DATEDIFF(mi, PlanBeginTime, PlanEndTime) IS NULL THEN 0
                 ELSE DATEDIFF(mi, PlanBeginTime, PlanEndTime)
            END) AS PlanTime ,
        SUM(CASE WHEN DATEDIFF(mi, CheckInApproveTime, CheckOutApproveTime) < 0
                 THEN 0
                 WHEN DATEDIFF(mi, CheckInApproveTime, CheckOutApproveTime) IS NULL
                 THEN 0
                 ELSE DATEDIFF(mi, CheckInApproveTime, CheckOutApproveTime)
            END) AS ActualTime ,
        ( SELECT    ‘,‘ +‘‘‘‘+ CAST(wp.PlanID AS VARCHAR(40)) +‘‘‘‘
          FROM      dbo.PL_WorkPlan wp
                    LEFT JOIN dbo.PL_WorkDetail wd ON wd.PlanID = wp.PlanID
                    LEFT JOIN dbo.PT_WorkPoint pp ON wp.PlanID = pp.PlanID
                    INNER JOIN dbo.SEC_Organization o ON o.Id = wd.WorkOrgID
                    LEFT JOIN dbo.SEC_Organization RO ON ro.Id = wd.RegulatoryOrgID
          WHERE     wp.FormStatusCode NOT IN ( ‘Draft‘, ‘Approving‘ )
                    AND wd.WorkOrgName IS NOT NULL
                    
                    AND ( WorkOrgID = V.WorkOrgID )
        FOR
          XML PATH(‘‘)
        ) PlanIDs
FROM    ( SELECT    wd.WorkTypeCode ,
                    wp.planid ,
                    wp.PlanBeginTime ,
                    wp.PlanEndTime ,
                    WP.FormStatusCode ,
                    PlanTypeCode ,
                    wd.WorkOrgID ,
                    wd.RegulatoryOrgID ,
                    o.Parent ,
                    wd.WorkOrgName ,
                    o.[Level] AS OLevel ,
                    RO.[Level] AS ROLevel,
                    Ro.NAME AS RoName,
                    CheckInApproveTime ,
                    CheckOutApproveTime
          FROM      dbo.PL_WorkPlan wp
                    LEFT JOIN dbo.PL_WorkDetail wd ON wd.PlanID = wp.PlanID
                    LEFT JOIN dbo.PT_WorkPoint pp ON wp.PlanID = pp.PlanID
                    INNER JOIN dbo.SEC_Organization o ON o.Id = wd.WorkOrgID
                    LEFT JOIN dbo.SEC_Organization RO ON ro.Id = wd.RegulatoryOrgID
          WHERE     wp.FormStatusCode NOT IN ( ‘Draft‘, ‘Approving‘ )
                    AND wd.WorkOrgName IS NOT NULL
                    
        ) V
WHERE   1 = 1  
      and WorkTypeCode in ($WorkTypeCode$)
 and V.PlanBeginTime >= @BeginTime
 and V.PlanBeginTime < @EndTime
{? and V.LineID = @LineID } 
        {? and  V.PlanTypeCode = @PlanTypeCode }
        {? and V.WorkOrgID = @WorkOrgID }
GROUP BY 
        WorkOrgID ,
        WorkOrgName) 
      as  V2
        WHERE V2.WorkOrgID !=‘00000000-0000-0000-0000-000000000000‘
        

使用for xml path 分组查询


联系
我们