代码之家 › 专栏 › 技术社区 › TheShield

组合两个表,包括在其他表中找不到的行,替换列中的值

db2 sql

TheShield · 技术社区 · 7 年前

SELECT t3.PatientID, SUM(t3.Fee) as total FROM     
(SELECT t1.TestID, t2.PatientID, t1.Fee FROM    
(SELECT Test.TestID, Test.Fee FROM MedicalTest AS Test) AS t1,    
(SELECT T.TestID, T.PatientID FROM Take AS T) AS t2    
WHERE t1.TestID = t2.TestID    
ORDER BY t2.PatientID) AS t3        
GROUP BY t3.PatientID    
ORDER BY total DESC;

这给了我一个病人ID表,以及他们在测试上花费了多少,这个表的一部分是这样的:

PATIENTID   TOTAL                            
----------- ---------------------------------
99642131                            550.00
99631255                            440.00
99665378                            430.00
99627956                            310.00
99657423                            280.00
99641125                            260.00
99630025                            230.00
99648682                            230.00

我的另一个问题:

SELECT DISTINCT D.PatientID FROM Diagnose AS D     
WHERE D.PhysicianID IN (    
SELECT P.PhysicianID FROM Physician AS P    
WHERE P.HName = 'Specific Hospital'    
AND P.DName = 'Intensive Care Unit');

向我返回由特定医生看护的患者ID列表。表格的一部分:

PATIENTID  
-----------
99615376
99618797
99620783
99620882
99621221

我试图创建一个结果表,其中包含第二个表中的患者ID以及他们在第一个表中的医疗测试上花费了多少。第二个表格中的一些患者没有进行任何测试,在这种情况下,我希望表格中的total列只给出0,但是,我尝试合并表格时只给出了进行过测试的患者:

SELECT t5.PatientID, t4.total FROM

(SELECT t3.PatientID, SUM(t3.Fee) as total FROM
(SELECT t1.TestID, t2.PatientID, t1.Fee FROM
(SELECT Test.TestID, Test.Fee FROM MedicalTest AS Test) AS t1,
(SELECT T.TestID, T.PatientID FROM Take AS T) AS t2
WHERE t1.TestID = t2.TestID
ORDER BY t2.PatientID) AS t3
GROUP BY t3.PatientID
ORDER BY total DESC) AS t4,

(SELECT DISTINCT D.PatientID FROM Diagnose AS D
WHERE D.PhysicianID IN (
SELECT P.PhysicianID FROM Physician AS P
WHERE P.HName = 'Specific Hospital'
AND P.DName = 'Intensive Care Unit')) AS t5

WHERE t5.PatientID = t4.PatientID;

PATIENTID   TOTAL                            
----------- ---------------------------------
99642131                            550.00
99665378                            430.00
99627956                            310.00

1 回复 | 直到 7 年前

TheShield 7 年前

我成功地编写了以下查询:

SELECT DISTINCT D.PatientID, COALESCE(total, '0') AS finalTotal
FROM Diagnose AS D
LEFT JOIN (
SELECT T.PatientID, SUM(M.Fee) AS total
FROM Take AS T, MedicalTest AS M
WHERE T.TestID = M.TestID
GROUP BY T.PatientID
          ) AS t1
ON t1.PatientID = D.PatientID
WHERE D.PhysicianID IN (
SELECT P.PhysicianID FROM Physician AS P
WHERE P.HName = 'Specific Hospital'
AND P.DName = 'Intensive Care Unit')
ORDER BY finalTotal DESC;

我用 COALESCE(total, '0') 用我的 LEFT JOIN

PATIENTID   FINALTOTAL                                
----------- ------------------------------------------
99642131                                     550.00
99665378                                     430.00
99627956                                     310.00
99615376                                          0
99618797                                          0
99620783                                          0
99620882                                          0
99621221                                          0
99642157                                          0
99655482                                          0
99664061                                          0