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

Question

我有两个由以下两个查询组成的表：

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以及第一个表中他们在医学测试上花费的金额。第二张表中的一些患者没有进行任何测试，在这种情况下，我希望该表只给总列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

如何将表2中未进行测试的患者包括在内，并在其总计列中输入0？

Answer 1

我设法编写了以下查询：

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在没有进行医学测试的行上输入0。这让我找到了我想要的输出：

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