在一个查询中合并查询

Question

是否可以将这两个查询合并为一个查询？

SELECT COUNT(IMA.FKRiskID) AS RiskCountWithoutPlan 
 FROM Rpt_ImpactAssessment IMA  
WHERE IMA.FKItemID = 38
  AND IMA.ImpactCurPIDLevel LIKE '%High%' 
  AND NOT EXISTS 
    (   
       select 1 
         from RM_LinkActionToPlan LAP                
        where IMA.FKPlanID = LAP.PlanID
     )   
SELECT COUNT(IMA.FKRiskID) AS RiskCountWithPlan
 FROM Rpt_ImpactAssessment IMA 
WHERE IMA.FKItemID = 38 
  AND  IMA.ImpactCurPIDLevel LIKE '%High%'
  AND EXISTS (  
                  select 1 
                    from RM_LinkActionToPlan LAP                 
                   where IMA.FKPlanID = LAP.PlanID
             )

Answer 1

尝尝这个。它适用于我使用虚拟数据(我将发布查询作为评论)。这将给出两列:第一列是RiskCountWithoutPlan，第二列是RiskCountWithPlan

SELECT 
    SUM([rcwop]) [RiskCountWithoutPlan],
    SUM([rcwp]) [RiskCountWithPlan]
FROM
(
    SELECT 
        CASE WHEN LAP.PlanID IS NULL THEN 1 ELSE 0 END [rcwop],
        CASE WHEN LAP.PlanID IS NOT NULL THEN 1 ELSE 0 END [rcwp]
    FROM 
        Rpt_ImpactAssessment IMA  
        LEFT JOIN RM_LinkActionToPlan LAP ON IMA.FKPlanID = LAP.PlanID              
    WHERE IMA.FKItemID = 38
  AND IMA.ImpactCurPIDLevel LIKE '%High%' 
) tbl

希望这就是你要找的。

Stu

DECLARE @Rpt_ImpactAssessment TABLE (
    FKRiskID INT, 
    FKItemID INT,
    FKPlanID INT,
    ImpactCurPIDLevel NVARCHAR(20)
)

DECLARE @RM_LinkActionToPlan TABLE (
    FKRiskID INT, 
    FKItemID INT,
    PlanID  INT,
    ImpactCurPIDLevel NVARCHAR(20)
)


INSERT INTO @Rpt_ImpactAssessment VALUES (1, 38, 2, 'High')
INSERT INTO @RM_LinkActionToPlan VALUES (1, 38, 2, 'High')

INSERT INTO @Rpt_ImpactAssessment VALUES (1, 38, 3, 'High')
INSERT INTO @RM_LinkActionToPlan VALUES (1, 38, 3, 'High')


SELECT 
    SUM([rcwop]) [RiskCountWithoutPlan],
    SUM([rcwp]) [RiskCountWithPlan]
FROM
(
    SELECT 
        CASE WHEN LAP.PlanID IS NULL THEN 1 ELSE 0 END [rcwop],
        CASE WHEN LAP.PlanID IS NOT NULL THEN 1 ELSE 0 END [rcwp]
    FROM 
        @Rpt_ImpactAssessment IMA  
        LEFT JOIN @RM_LinkActionToPlan LAP ON IMA.FKPlanID = LAP.PlanID              
    WHERE IMA.FKItemID = 38
  AND IMA.ImpactCurPIDLevel LIKE '%High%' 
) tbl