SQL Server 2008查询-表联接

debugcn 发表于 Dev

用户名

我无法从SQL Server 2008查询中获得所需的结果。查询1产生我想要的[GeneralErrors]结果，而查询2产生正确的[RehabErrors]结果，但是我确实需要它们都在同一行上。当我尝试在查询3中合并这两个查询时，我的[GeneralErrors]结果不正确，但以我要查询的方式出现。

查询1：

SELECT
        ReviewID, 
        SUM(CASE Score_CorrectID_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_ProperlyIdentified_Accuracy WHEN 'Error' THEN 1 ELSE 0 END) AS GeneralErrors
    FROM  
        Reviews
    WHERE 
        (UserID IS NOT NULL AND UserID <> '')
    GROUP BY 
        Reviews.ReviewID

Results:

ReviewID    GeneralErrors
7           0
8           0
9           0
10          0
11          0
12          9
13          0
14          0
15          4

查询2：

SELECT 
         Reviews.ReviewID
        ,COUNT(RehabMetricsCalls.ReviewID) AS RehabErrors
    FROM    RehabMetrics INNER JOIN
             RehabMetricsCalls ON RehabMetrics.RehabMetricID = RehabMetricsCalls.RehabMetricID RIGHT OUTER JOIN
             Reviews ON RehabMetricsCalls.ReviewID = Reviews.ReviewID   
    WHERE 
        (UserID IS NOT NULL AND UserID <> '')
    GROUP BY 
        Reviews.ReviewID

Results:

ReviewID    RehabErrors
7           3
8           0
9           0
10          0
11          0
12          5
13          5
14          0
15          4

查询3：我尝试将两个查询结合使用，从而产生不正确的结果

SELECT DISTINCT 
        Reviews.ReviewID
        ,SUM(CASE Score_CorrectID_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_ProperlyIdentified_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_MiniMiranda_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_Tone_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_Accuracy_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_Notepad_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +        
        CASE Score_PCAResponsive_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Score_AWGInfo_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
        CASE Complaint_Accuracy WHEN 'Error' THEN 1 ELSE 0 END) AS GeneralErrors
        ,COUNT(RehabMetricsCalls.ReviewID) AS RehabErrors
    FROM    RehabMetrics INNER JOIN
             RehabMetricsCalls ON RehabMetrics.RehabMetricID = RehabMetricsCalls.RehabMetricID INNER JOIN
             Reviews ON RehabMetricsCalls.ReviewID = Reviews.ReviewID   
    WHERE 
        (UserID IS NOT NULL AND UserID <> '')
    GROUP BY 
        Reviews.ReviewID

所需结果：

ReviewID    GeneralErrors   RehabErrors
    7           0               3
    12          45              5
    13          0               5
    15          16              4

Gameiswar

参加。

;with Firstquery  as
(
SELECT
 ReviewID, 
 SUM(CASE Score_CorrectID_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
CASE Score_ProperlyIdentified_Accuracy WHEN 'Error' THEN 1 ELSE 0 END) AS GeneralErrors
 FROM  
 Reviews
 WHERE 
(UserID IS NOT NULL AND UserID <> '')
 GROUP BY 
Reviews.ReviewID
)
,Secondquery as
(
SELECT 
  Reviews.ReviewID
 ,COUNT(RehabMetricsCalls.ReviewID) AS RehabErrors
FROM    RehabMetrics INNER JOIN
   RehabMetricsCalls ON RehabMetrics.RehabMetricID = RehabMetricsCalls.RehabMetricID RIGHT OUTER JOIN
 Reviews ON RehabMetricsCalls.ReviewID = Reviews.ReviewID   
 WHERE 
 (UserID IS NOT NULL AND UserID <> '')
 GROUP BY 
  Reviews.ReviewID
)
select 
fs.reviewid,fs.generalerrors,sq.rehaberrors
from
firstquery fs
join
secondquery sq
on fs.reviewid=sq.reviewid

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。