How to combine these two SQL statements?

问题:

I have 2 SQL queries both of which get the counts for different IDs.

select @cntCM_CMQ = count(*)
from dbo.CaseWorkflow cw 
join vew_CasePersonnelSystemIDs vcps on cw.ID_Case = vcps.ID_Case
join dbo.WorkflowStates ws on ws.ID_WorkflowState = cw.ID_WorkflowState
where CMSUID = @nSUID and ws.ID_WorkflowType = 3    -- CMQ

select @cntCM_PRWK = count(*)
from dbo.CaseWorkflow cw 
join vew_CasePersonnelSystemIDs vcps on cw.ID_Case = vcps.ID_Case
join dbo.WorkflowStates ws on ws.ID_WorkflowState = cw.ID_WorkflowState
where CMSUID = @nSUID and ws.ID_WorkflowType = 1    -- PAPERWORK

It seems that I should be able to combine them into a single select (perhaps with a CASE statement), but I can't seem to crack it.

回答1:

Something like this?

select sum(case when ws.ID_WorkflowType = 1 then 1 else 0 end) as cntCM_PRWK
     , sum(case when ws.ID_WorkflowType = 3 then 1 else 0 end) as cntCM_CMQ
from dbo.CaseWorkflow cw 
join vew_CasePersonnelSystemIDs vcps on cw.ID_Case = vcps.ID_Case
join dbo.WorkflowStates ws on ws.ID_WorkflowState = cw.ID_WorkflowState
where CMSUID = @nSUID

回答2:

select 
SUM(Cast(Case when ws.ID_WorkflowType = 3 then 1 else 0 end as int) as CMQ
,SUM(Cast(Case when ws.ID_WorkflowType = 1 then 1 else 0 end as int) as Paperwork

from dbo.CaseWorkflow cw 
join vew_CasePersonnelSystemIDs vcps on cw.ID_Case = vcps.ID_Case
join dbo.WorkflowStates ws on ws.ID_WorkflowState = cw.ID_WorkflowState
where CMSUID = @nSUID 

回答3:

There's a trick to doing this. essentially you use Case to pick out a 1 value for the rows you are interested in and then Sum the ones to get a count. Case defaults to null if no cases match, which get ignored by Sum

Select
  @cntCM_CMQ = Sum(Case ws.ID_WorkflowType When 3 Then 1 End),
  @cntCM_PRWK = Sum(Case ws.ID_WorkflowType When 1 Then 1 End) 
From
  dbo.CaseWorkflow cw 
    inner join 
  vew_CasePersonnelSystemIDs vcps 
    on cw.ID_Case = vcps.ID_Case
    inner join 
  dbo.WorkflowStates ws 
    on ws.ID_WorkflowState = cw.ID_WorkflowState
Where
  CMSUID = @nSUID

回答4:

select 
    @cntCM_CMQ = count(case when ws.ID_WorkflowType = 3 then ws.ID_WorkflowType end)
    , @cntCM_PRWK = count(case when ws.ID_WorkflowType = 1 then ws.ID_WorkflowType end)
from 
    dbo.CaseWorkflow cw 
    join vew_CasePersonnelSystemIDs vcps on cw.ID_Case = vcps.ID_Case
    join dbo.WorkflowStates ws on ws.ID_WorkflowState = cw.ID_WorkflowState
where 
    CMSUID = @nSUID 
    and ws.ID_WorkflowType in (1, 3)

回答5:

An alternative that is similar to the other posts.

Bob Duells post is probably the most readable.

SELECT 
    [cntCM_PRWK]    = COUNT(CASE WHEN ws.ID_WorkflowType = 1 THEN ws.ID_WorkflowState ELSE NULL END),
    [cntCM_CMQ]     = COUNT(CASE WHEN ws.ID_WorkflowType = 3 THEN ws.ID_WorkflowState ELSE NULL END)
FROM 
    dbo.CaseWorkflow cw 
    INNER JOIN vew_CasePersonnelSystemIDs vcps 
            ON cw.ID_Case = vcps.ID_Case
    INNER JOIN dbo.WorkflowStates ws 
            ON ws.ID_WorkflowState = cw.ID_WorkflowState
WHERE CMSUID = @nSUID