我有以下两张表:
orders
id | user | amount |
1 | ALEX | 10 |
2 | BARB | 20 |
3 | CARL | 30 |
4 | DAVE | 40 |
5 | EVIE | 50 |
6 | FRAN | 60 |
history
order_id | status | date |
1 | pending | 2017-04-01 10:02:47 |
1 | shipped | 2017-05-01 05:58:35 |
1 | delivered | 2017-06-01 12:56:32 |
2 | pending | 2017-04-01 11:44:03 |
2 | shipped | 2017-05-01 14:51:49 |
2 | delivered | 2017-06-01 23:27:16 |
3 | pending | 2017-07-01 20:44:02 |
3 | shipped | 2017-08-01 17:10:18 |
4 | pending | 2017-07-01 11:19:15 |
4 | shipped | 2017-08-01 15:17:25 |
5 | pending | 2017-10-01 21:59:13 |
6 | pending | 2017-10-01 02:47:33 |
这些是重要的字段,因为每个表都有几个不需要也不有用的列。我需要的是一个查询,将只显示最新的“状态”和“日期”为每个“订单号”,这意味着应该只有一行每个“订单号”。最终结果应该是这样的:
order_id | amount | user | status | date |
1 | 10 | ALEX | delivered | 2017-06-01 12:56:32 |
2 | 20 | BARB | delivered | 2017-06-01 23:27:16 |
3 | 30 | CARL | shipped | 2017-08-01 17:10:18 |
4 | 40 | DAVE | shipped | 2017-08-01 15:17:25 |
5 | 50 | EVIE | pending | 2017-10-01 21:59:13 |
6 | 60 | FRAN | pending | 2017-10-01 02:47:33 |
这是我所能做的,它几乎正确地显示了所有内容,但只针对已交付的内容
SELECT orders.id, orders.user, orders.amount, history.status, history.date
FROM orders
JOIN history
ON history.order_id = orders.id
AND history.status = 'delivered'
ORDER BY history.date DESC
至于我使用的dbms,我不完全确定,我使用的是mode analytics网站上的sql编辑器。对于其他一些问题,我使用的解决方案显然只适用于mysql,但在其他一些问题中,我使用的解决方案只适用于postgresql。
3条答案
按热度按时间hivapdat1#
由于您不确定正在使用的rdbms,我将编写符合ansi的sql查询,该查询将在任何符合ansi的sql rdbms上运行。
这是sql中一个非常常见的问题:找到整行的最大值。实际上,stackoverflow社区创建了一个标签来处理这样的问题是很常见的:greatest-n-per-group。
基本上,有两种方法可以解决这个问题:
与简单组标识符连接,组子查询中的最大值
在这种方法中,首先要找到
group-identifier, max-value-in-group
在子查询中。然后将表连接到子查询,两个查询上的值相等group-identifier
以及max-value-in-group
:用self左连接,调整连接条件和过滤器
在这种方法中,您将表与其自身连接起来。平等的原则
group-identifier
. 然后,两个聪明的动作:第二个连接条件是左侧值小于右侧值
执行步骤1时,实际具有最大值的行将具有
NULL
在右边(这是一个LEFT JOIN
,记得吗?)。然后,我们过滤连接的结果,只显示右侧所在的行NULL
.所以你最终会得到:
结论
两种方法的结果完全相同。
如果你有两排
max-value-in-group
为了group-identifier
,两种方法的结果中都将包含这两行。这两种方法都是sqlansi兼容的,因此,无论其“风格”如何,都可以与您喜爱的rdbms一起工作。
这两种方法对性能也很友好,但是您的里程数可能会有所不同(rdbms、db结构、索引等)。所以当你选择一种方法而不是另一种方法时,基准测试。一定要挑对你最有意义的。
k97glaaz2#
一种方法使用
row_number()
要标识最近的行,请执行以下操作:toe950273#
您可以使用如下窗口功能: