我有一个名为Patientinfo的表A,如下所示:

mysql - 使用ID获取两列的当前日期总和和一列的最后一行-MYSQL-LMLPHP

表b名为tblpayment,如下所示:
mysql - 使用ID获取两列的当前日期总和和一列的最后一行-MYSQL-LMLPHP

现在,我想要的是从表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而不是MAXLEFT 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

10-07 19:25
查看更多