php - 如何左连接选择具有最高 user_id 的值

Question

这个问题与我之前的问题 How to left join 2 tables with SUM() and MAX() grouped by date 相关。

我更改的是我添加了 user_id 列(自动递增)并希望选择每个日期具有最高 user_id 的值

我有表loadhistory(想要“仅选择每个日期具有最高user_id的值”分组依据并按日期DESC排序。)因此，在本例中，我想为 2015-02-27 选择 150，因为它在该日期具有最高的 user_id，为 2015-02-28 选择 50

| user_id | customer_id |     date    | bal |
    1           1         2015-02-27    100
    2           1         2015-02-27    150
    3           1         2015-02-28    150
    4           1         2015-02-28    50

和表 transactionrecord(想要使用 SUM(bal) group by 和 order by date DESC 来汇总每个日期的值)

 | user_id |customer_id |     date     | bal |
     1          1         2015-02-27     50
     2          1         2015-02-27     20
     3          1         2015-02-28     10

我想加入两个表，如下所示:

|    date    |   balance    |   amount paid   |
  2015-02-28       50               10
  2015-02-27       150              70

这是到目前为止的代码(我使用了从上一个问题中获得的代码，并在我的新问题中对其进行了编辑，希望达到预期的结果，但没有)

SELECT a.customer_id, a.date, (b.bal AS bal WHERE b.user_id = MAX(b.user_id) , a.paid
FROM (
SELECT customer_id, date, SUM(bal) AS paid
FROM transactionrecord
GROUP BY customer_id, date
) AS a LEFT JOIN loadhistory AS b
ON a.customer_id = b.customer_id AND a.date = b.date
WHERE a.customer_id = 1
GROUP BY a.customer_id, a.date, a.paid
ORDER BY a.date DESC

请帮忙。提前致谢

Answer 1

MySQL使用第一行group by，所以你必须先对它进行排序，然后才能使用group by，如下所示:

SELECT * FROM (SELECT * FROM `loadhistory` ORDER BY user_id DESC) history GROUP BY date

因此您可以使用以下查询作为解决方案:

SELECT h.date,h.bal as balance, t.amount as 'amount paid' FROM 
  (SELECT * FROM (SELECT * FROM `loadhistory` ORDER BY user_id DESC) history GROUP BY date) as h
JOIN
  (SELECT SUM(bal) as amount, customer_id, date FROM `transactionrecord` GROUP BY date) as t
ON h.date = t.date AND h.customer_id = t.customer_id
ORDER BY date DESC