SQL查询的输出不正确(多行表示状态)

Question

下面是我为获取补丁遵从状态而编写的查询。

 select 
vUI.Title As 'Title',
vui.ArticleID, 
CASE 
    WHEN vsn.StateID = 0 or vsn.StateID = 1 or vsn.StateID =  2 THEN --'No Status'
    count(vRSV.Netbios_Name0) 
END As 'No Status',
CASE
    WHEN vsn.StateID = 3 or vsn.StateID = 4 or vsn.StateID = 5 or vsn.StateID = 7 or vsn.StateID = 8 or vsn.StateID = 12 or vsn.StateID = 14 THEN --'In Progress'
    count(vRSV.Netbios_Name0) 
end as 'In Progress',
Case
    WHEN vsn.StateID = 6 or vsn.StateID = 11 or vsn.StateID = 14 THEN --'Failed'
    count(vRSV.Netbios_Name0) 
End as 'Failed',
Case
    WHEN vsn.StateID = 9 or vsn.StateID = 10 THEN --'Success'
    count(vRSV.Netbios_Name0) 
    --ELSE 'Not Installed'
END As Success
from v_UpdateComplianceStatus vUCS
join v_UpdateInfo vUI
  on vUCS.CI_ID=vUI.CI_ID
  join v_R_System_Valid vRSV  on vUCS.ResourceId=vRSV.resourceid
inner join v_FullCollectionMembership_Valid vFCMV
  on vUCS.ResourceId=vFCMV.ResourceID
  and vFCMV.CollectionID='CB00123' 
join v_GS_OPERATING_SYSTEM vGOS
    on vUCS.ResourceID = vGOS.ResourceID
join v_StateNames vSN
  on vUCS.LastEnforcementMessageID = vSN.StateID and (vSN.TopicType=402 or vSN.TopicType=500 or vSN.TopicType=400)
where vui.DateLastModified between '2019-09-12' and '2019-10-12'
  group by vUI.Title,vui.ArticleID, vsn.StateID

我预期产出如下所示。

ArticleID   No Status   In Progress Failed  Success
4516033     NULL          8           102   2149

但产出如下。

ArticleID   No Status   In Progress Failed  Success
4516033     NULL          NULL        25    NULL
4516033     NULL          NULL        NULL  253
4516033     NULL          NULL       NULL   1896
4516033     NULL          NULL        69    NULL
4516033     NULL           8           8    NULL

我在这里做错什么了？

Answer 1

您应该尝试对每个值进行求和，而不是在每一行上起诉count()。

select 
vUI.Title As 'Title',
vui.ArticleID, 
sum ( CASE 
      WHEN vsn.StateID = 0 
        or vsn.StateID = 1 or vsn.StateID =  2 THEN  1 else 0 
      END  ) As 'No Status',

sum ( case 
      WHEN vsn.StateID = 3 or vsn.StateID = 4 or vsn.StateID = 5 
              or vsn.StateID = 7 or vsn.StateID = 8 
               or vsn.StateID = 12 or vsn.StateID = 14 THEN 1 else 0 

       end ) as 'In Progress',

 sum( CASE  
      WHEN vsn.StateID = 6 or vsn.StateID = 11
          or vsn.StateID = 14 
     THEN 1 else 0 

     End ) as 'Failed',
   sum( CASE WHEN vsn.StateID = 9 or vsn.StateID = 10 
      THEN 1 else 0
     END ) As Success
from v_UpdateComplianceStatus vUCS
join v_UpdateInfo vUI
  on vUCS.CI_ID=vUI.CI_ID
  join v_R_System_Valid vRSV  on vUCS.ResourceId=vRSV.resourceid
inner join v_FullCollectionMembership_Valid vFCMV
  on vUCS.ResourceId=vFCMV.ResourceID
  and vFCMV.CollectionID='CB00123' 
join v_GS_OPERATING_SYSTEM vGOS
    on vUCS.ResourceID = vGOS.ResourceID
join v_StateNames vSN
  on vUCS.LastEnforcementMessageID = vSN.StateID and (vSN.TopicType=402 or vSN.TopicType=500 or vSN.TopicType=400)
where vui.DateLastModified between '2019-09-12' and '2019-10-12'
  group by vUI.Title,vui.ArticleID

Answer 2

您需要有条件的聚合。CASE是聚合函数的参数。所以：

select vUI.Title As Title, vui.ArticleID, 
       sum(case when vsn.StateID in (0, 1, 2)
                then 1 else 0 
           end) as no_status,
       sum(case when vsn.StateID in (3, 4, 5, 7, 8, 12, 14)
                then 1 else 0 
           end) as in_progress,
       . . . 
from . . .
group by vUI.Title As Title, vui.ArticleID;

请注意，您需要同时更改SELECT和GROUP BY。

另外：

• 不对列别名使用单引号。这只会引起混乱。单引号只适用于不需要转义的字符串和日期constants.
• Choose列别名(因此避免spaces).
• IN简化了comparisons.

)。