如何使用SQL选择父/子关系的最后一个子行 [英] How do I select the last child row of a parent/child relationship using SQL
本文介绍了如何使用SQL选择父/子关系的最后一个子行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
仅使用SQL(MySQL),我想选择一个父子关系的最后一个子行,其中子行按时间戳排序.
Using SQL (MySQL) only I would like to select each of the last child rows of a parent child relationship where the child rows are ordered by a timestamp.
例如,使用表invoices
和invoice_items
,我想要每个invoice
的最新(即带有时间戳的)invoice_items
记录.
For example using the tables invoices
and invoice_items
, I want the newest (ie: most recently timestamped) invoice_items
records for each invoice
respectively.
--------------------------
|Invoices |
--------------------------
|invoice_id| other fields|
--------------------------
| 1 | ... |
--------------------------
| 2 | ... |
--------------------------
| 3 | ... |
--------------------------
--------------------------------------------
|Invoice_Items |
--------------------------------------------
| id | invoice_id | invoice_item_timestamp |
--------------------------------------------
| 1 | 1 | 2009-12-01 10:00:00 |
--------------------------------------------
| 2 | 1 | 2009-12-01 10:01:00 |
--------------------------------------------
| 3 | 1 | 2009-12-01 10:02:00 |
--------------------------------------------
| 4 | 2 | 2009-12-01 9:00:00 |
--------------------------------------------
| 5 | 3 | 2009-12-02 08:30:00 |
--------------------------------------------
| 6 | 3 | 2009-12-03 08:31:00 |
--------------------------------------------
生成如下表所示的结果集的最佳SQL语法是什么?
What is the best SQL syntax to produce a resultset that would look something like the following table?
-----------------------------------------------------
|invoice_id| invoice_item_id |invoice_item_timestamp|
---------------------------------------------------
| 1 | 3 | 2009-12-01 10:02:00 |
| 2 | 4 | 2009-12-01 09:00:00 |
| 3 | 6 | 2009-12-03 08:31:00 |
-----------------------------------------------------
推荐答案
SELECT i.*,it.*
FROM invoices i
INNER JOIN (
SELECT invoice_id, MAX(invoice_item_timestamp)
FROM invoice_items
GROUP BY invoice_id
) it ON (i.invoice_id=it.invoice_id)
这篇关于如何使用SQL选择父/子关系的最后一个子行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文