Question

我在研究两个问题做家庭作业, 过了好几个小时,我刚解决了这两个问题, 我最后一个问题是,我的两个问题都以数字值翻了一番,而不是单一个数字值。

这就是我有的:

SELECT SUM(P.AMT_PAID) AS TOTAL_PAID, C.CITATION_ID, C.DATE_ISSUED, SUM(V.FINE_CHARGED) AS TOTAL_CHARGED
FROM PAYMENT P, CITATION C, VIOLATION_CITATION V
WHERE V.CITATION_ID = C.CITATION_ID
AND C.CITATION_ID = P.CITATION_ID
GROUP BY C.CITATION_ID;

我的另一面,

SELECT C.CITATION_ID, C.DATE_ISSUED, SUM(V.FINE_CHARGED) AS TOTAL_CHARGED, SUM(P.AMT_PAID) AS TOTAL_PAID, SUM(V.FINE_CHARGED) - SUM(P.AMT_PAID) AS TOTAL_OWED
FROM (CITATION C)
LEFT JOIN VIOLATION_CITATION V
ON V.CITATION_ID = C.CITATION_ID
LEFT JOIN PAYMENT P
ON P.CITATION_ID = C.CITATION_ID
GROUP BY C.CITATION_ID
ORDER BY TOTAL_OWED DESC;

我相信有些事情我忽略了,如果有人能告诉我我在哪里,那将是一个很大的帮助。

Answer 1

Select Sum(P.Amt_Paid) As Total_Paid, C.Citation_Id
    , C.Date_Issued, Sum(V.Fine_Charged) As Total_Charged
From Payment P
    Join Citation C
        On C.Citation_Id = P.Citation_Id
    Join Violation_Citation V
        On V.Citation_Id = C.Citation_Id
Group By C.Citation_Id

首先,您应该使用 JOIN 语法, 而不是使用不受逗号限制的表格列表。这样可以更容易阅读,更标准化,并且通过忽略过滤条款来帮助预防问题。

其次,一个数额过大的最可能的原因是加入 VIOLATION_CITATION 表格。如果将组除去和具有综合功能的列,你可能会看到,对于每个 P.AMT_PAID 的情况,都重复使用 VIOLAION_CITATION 。也许,以下将解决问题:

Select Coalesce(PaidByCitation.TotalAmtPaid,0) As Total_Paid
    , C.Citation_Id, C.Date_Issued
    , Coalesce(ViolationByCitation.TotalCharged,0) As Total_Charged
    , Coalesce(ViolationByCitation.TotalCharged,0)
        - Coalesce(PaidByCitation.TotalAmtPaid,0) As Total_Owed
From Citation As C
    Left Join   (
                Select P.Citation_Id, Sum( P.Amt_Paid ) As TotalAmtPaid
                From Payment As P
                Group By P.Citation_Id
                ) As PaidByCitation
        On PaidByCitation.Citation_Id = C.Citation_Id
    Left Join   (
                Select V.Citation_Id, Sum( V.Find_Charged ) As TotalCharged
                From Violation_Citation As V
                Group By V.Citation_Id
                ) As ViolationByCitation
        On ViolationByCitation.Citation_Id = C.Citation_Id

使用 Coalesce 是为了确保如果左键返回给定的 Citation_ID 值,则没有行,我们用零替换 Null 。

友情链接