选择每个人的最后记录(Select last record of each person)

选择每个人的最后记录(Select last record of each person) mysql

我有以下表格

tbl_investors

id | first_name | last_name | --------------------------------------- 1 | Jon | Cold | 2 | Rob | Ark | 3 | Rickon | Bolt |

tbl_investors_ledger

id | investor_id | amount | ------------------------------------ 1 | 1 | 500 | 2 | 2 | 200 | 3 | 2 | 250 | 4 | 2 | 300 | 5 | 3 | 10 | 6 | 1 | 550 | 7 | 3 | 20 |

我只想以最新的金额回报所有投资者。 例如，Jon Cold与550，Rob Ark 300和Rickon Bolt 20，按姓氏字母顺序排列。

我有一个现有的查询，但它不会返回最新的投资者金额。 有人可以帮助我，我做错了吗？

SELECT t1.*, t2.* FROM ".tbl_investors." t1 LEFT JOIN ".tbl_investors_ledger." t2 ON t1.id = t2.investor_id LEFT JOIN (SELECT t.investor_id, max(t.id) as tid FROM ".tbl_investors_ledger." t ) tt ON tt.investor_id = t2.investor_id AND tt.tid = t2.id GROUP BY t2.investor_id ORDER BY t1.last_name

I have the following tables

tbl_investors

id | first_name | last_name | --------------------------------------- 1 | Jon | Cold | 2 | Rob | Ark | 3 | Rickon | Bolt |

tbl_investors_ledger

id | investor_id | amount | ------------------------------------ 1 | 1 | 500 | 2 | 2 | 200 | 3 | 2 | 250 | 4 | 2 | 300 | 5 | 3 | 10 | 6 | 1 | 550 | 7 | 3 | 20 |

I just want to return all investors with their latest amount. Ex, Jon Cold with 550, Rob Ark 300 and Rickon Bolt 20, alphabetically with their last name.

I have an existing query but it will not return the latest amount of the investor. Can someone help me what i'm doing wrong?

SELECT t1.*, t2.* FROM ".tbl_investors." t1 LEFT JOIN ".tbl_investors_ledger." t2 ON t1.id = t2.investor_id LEFT JOIN (SELECT t.investor_id, max(t.id) as tid FROM ".tbl_investors_ledger." t ) tt ON tt.investor_id = t2.investor_id AND tt.tid = t2.id GROUP BY t2.investor_id ORDER BY t1.last_name

最满意答案

尝试这个;）

SELECT t1.*, t2.* FROM tbl_investors t1 LEFT JOIN tbl_investors_ledger t2 ON t1.id = t2.investor_id INNER JOIN ( SELECT t.investor_id, max(t.id) as tid FROM tbl_investors_ledger t GROUP BY t.investor_id) tt ON tt.investor_id = t2.investor_id AND tt.tid = t2.id ORDER BY t1.last_name

SQLFiddle DEMO

并检查相关的OP 检索每个组和本博客中的最后一条记录 如何在SQL中选择 每组 的第一个/最小/最大行 ，您可以找到更多针对您的问题的解决方案。

Try this;)

SELECT t1.*, t2.* FROM tbl_investors t1 LEFT JOIN tbl_investors_ledger t2 ON t1.id = t2.investor_id INNER JOIN ( SELECT t.investor_id, max(t.id) as tid FROM tbl_investors_ledger t GROUP BY t.investor_id) tt ON tt.investor_id = t2.investor_id AND tt.tid = t2.id ORDER BY t1.last_name

SQLFiddle DEMO

And check related OP Retrieving the last record in each group and this blog How to select the first/least/max row per group in SQL, you can find more solutions for your question.