我有一个名为Patientinfo的表A,如下所示:
表b名为tblpayment,如下所示:
现在,我想要的是从表A中获取PatientId和PatientName,并将今天的表B的payingDate与SUM(paymentTotal),SUM(paymentPaid)结合起来,但剩下最后一行付款。
用简单的话来说,我希望今天所有的患者的SUM of PaymentTotal和Sum of PaymentPaid,但最后一行还剩。
在图形上,我想要的应该是:
patientId paymentTotal paymentPaid paymentRemaining
252 123500 118500 5000
253 60000 55000 5000
254 17500 17500 0
258 5800 0 5800
注意:记录应为当前日期。
有帮助吗?
到目前为止,我所做的是:
SELECT a.patientId , a.patientName, b.paymentTotal , b.paymentPaid , b.paymentRemaining FROM
patientinfo a
inner join
(
SELECT patienId , SUM(paymentTotal) as paymentTotal ,SUM(paymentPaid) as paymentPaid,
MAX(paymentRemaining) as paymentRemaining
FROM tblpayment WHERE paymentDate LIKE '%$date%' GROUP BY patienId
)b
on a.patientId = b.patienId
一切都很好,但是此查询中的错误是它为我提供了列paymentRemaining的第一个值。而我想要最后一笔付款,这给了我最大的价值。我应该用什么替换此Max?
编辑:
我的问题有点错误。我不希望最大的PaymentRemaining,但最后一个对应的PatientId的paymentRemaining行。
最佳答案
您只需要MIN
而不是MAX
和LEFT JOIN
:
SELECT DISTINCT
t.patientId,
b.paymentTotal ,
b.paymentPaid , b.paymentRemaining
FROM tblpayment AS t
LEFT JOIN patientinfo a ON t.patientId = a.patientId
LEFT join
(
SELECT patientId , SUM(paymentTotal) as paymentTotal,
SUM(paymentPaid) as paymentPaid,
MIN(paymentRemaining) as paymentRemaining
FROM tblpayment
GROUP BY patientId
) b on t.patientId = b.patientId
demo
这将完全给您您想要的:
| patientId | paymentTotal | paymentPaid | paymentRemaining |
|-----------|--------------|-------------|------------------|
| 252 | 123500 | 118500 | 5000 |
| 253 | 60000 | 55000 | 5000 |
| 254 | 17500 | 17500 | 0 |
| 258 | 5800 | 0 | 5800 |
更新:
如果需要最后一行,则它将是每个患者编号的最新日期。然后试试这个:
SELECT DISTINCT
t.patientId,
b.paymentTotal ,
b.paymentPaid ,
t.paymentRemaining
FROM
(
a.patientId, paymentRemaining, paymentDate
FROM tblpayment AS t
LEFT JOIN patientinfo a ON t.patientId = a.patientId
) AS t
INNER join
(
SELECT patientId , SUM(paymentTotal) as paymentTotal,
SUM(paymentPaid) as paymentPaid
FROM tblpayment
GROUP BY patientId
) b on t.patientId = b.patientId
INNER JOIN
(
SELECT patientId, MAX(paymentDate) AS LatestDate
FROM tblpayment
GROUP BY patientId
) AS s ON s.patientId = t.patientId AND t.paymentDate = s.LatestDate