我患有严重的肠胃气胀。
简单起见,我有3个表:Orders,Statuss,XrefOrdersStatuses
我已经设置了带有简化架构和一些编辑过的行数据的SQLFiddle(SQLFiddle Here)。
我需要获取的是具有其最新XrefOrdersStatuses(以及Statuss)的Orders。我可以使用以下查询来做到这一点:
SELECT o.shopper_name_first, o.shopper_name_last, os.os_name, x.xos_order_id, x.xos_status_id, x.xos_datetime
FROM Orders AS o
INNER JOIN XrefOrdersStatuses AS x ON x.xos_order_id = o.order_id
LEFT JOIN Statuses AS os ON os.os_id = x.xos_status_id
ORDER BY x.xos_order_id, x.xos_datetime DESC
结果如下:
| shopper_name_first | shopper_name_last | os_name | xos_order_id | xos_status_id | xos_datetime |
|--------------------|-------------------|---------------------|--------------|---------------|------------------------|
| Sally | Sue | Order Pre-Processed | 34049 | 31 | June, 18 2015 12:42:50 |
| Sally | Sue | Order Paid | 34049 | 20 | June, 18 2015 12:36:30 |
| Sally | Sue | Order Created | 34049 | 10 | June, 18 2015 12:34:56 |
| Joe | Schmoe | Order Pre-Processed | 34050 | 31 | June, 18 2015 12:54:50 |
| Joe | Schmoe | Order Paid | 34050 | 20 | June, 18 2015 12:38:30 |
| Joe | Schmoe | Order Created | 34050 | 10 | June, 18 2015 12:34:56 |
| Peter | Piper | Order Paid | 34051 | 20 | June, 18 2015 12:37:30 |
| Peter | Piper | Order Created | 34051 | 10 | June, 18 2015 12:34:56 |
在生产方案中,我要选择更多/所有“订单”表列,为简单起见,此处不再赘述。请注意重复的订单,但状态会排队。
在我看来,目的不是要找到最新状态。该视图列出了订单,我想基于最大的datetime列值将每个订单与其各自的最新状态结合起来/匹配。一对多模式(一个具有许多状态的订单)。
所以我想做的是:
选择顺序
使用MAX(xos_datetime)GROUP BY xos_order_id加入外部参照
加入状态以获取定义
订单必须保留在查询的左侧表中(与其他表一起使用的其他联接)。经过许多其他的SO问题和解答以及许多Google搜索之后,我还没有完全找到我需要的东西。
如果需要按最大日期时间获取XrefOrdersStatuses并按订单ID分组,则可以使用查询...。
SELECT x.*
FROM XrefOrdersStatuses AS x
JOIN (
SELECT xos_order_id, MAX(xos_datetime) AS maxdate
FROM XrefOrdersStatuses
GROUP BY xos_order_id
) AS x1 ON x1.xos_order_id = x.xos_order_id AND x1.maxdate = x.xos_datetime;
导致:
| xos_id | xos_order_id | xos_status_id | xos_datetime |
|--------|--------------|---------------|------------------------|
| 118287 | 34051 | 20 | June, 18 2015 12:37:30 |
| 118289 | 34049 | 31 | June, 18 2015 12:42:50 |
| 118290 | 34050 | 31 | June, 18 2015 12:54:50 |
... 好极了!每个订单的最新状态!
las,我需要将Orders表作为左侧表(其他联接以及在何处进行搜索-即名称,活动/不活动,分配给用户等)。
我的麻烦是将Orders查询与XrefOrdersStatuses查询结合在一起。我只是不能完全让他们一起玩。我的尝试因排序,分组以及各种混乱而失败。
我觉得这让我接近了,但是正确分组和排序是我要进行的调整:
SELECT o.shopper_name_first, o.shopper_name_last, os.os_name, x.xos_order_id, x.xos_status_id, x.xos_datetime
FROM Orders AS o
JOIN (
SELECT xz.*
FROM XrefOrdersStatuses AS xz
JOIN (
SELECT xos_order_id, MAX(xos_datetime) AS maxdate
FROM XrefOrdersStatuses
GROUP BY xos_order_id
) AS x1 ON x1.xos_order_id = xz.xos_order_id AND x1.maxdate = xz.xos_datetime
) AS x
LEFT JOIN Statuses AS os ON os.os_id = x.xos_status_id
ORDER BY x.xos_order_id, x.xos_datetime DESC;
结果如下:
| shopper_name_first | shopper_name_last | os_name | xos_order_id | xos_status_id | xos_datetime |
|--------------------|-------------------|---------------------|--------------|---------------|------------------------|
| Joe | Schmoe | Order Pre-Processed | 34049 | 31 | June, 18 2015 12:42:50 |
| Peter | Piper | Order Pre-Processed | 34049 | 31 | June, 18 2015 12:42:50 |
| Sally | Sue | Order Pre-Processed | 34049 | 31 | June, 18 2015 12:42:50 |
| Joe | Schmoe | Order Pre-Processed | 34050 | 31 | June, 18 2015 12:54:50 |
| Peter | Piper | Order Pre-Processed | 34050 | 31 | June, 18 2015 12:54:50 |
| Sally | Sue | Order Pre-Processed | 34050 | 31 | June, 18 2015 12:54:50 |
| Peter | Piper | Order Paid | 34051 | 20 | June, 18 2015 12:37:30 |
| Sally | Sue | Order Paid | 34051 | 20 | June, 18 2015 12:37:30 |
| Joe | Schmoe | Order Paid | 34051 | 20 | June, 18 2015 12:37:30 |
我尝试了不同的变体,尝试向前,向后,向左,向右,从内,向外,向上,向下,热,冷,湿,干...您都可以理解。
我需要的是它看起来像这样:
| shopper_name_first | shopper_name_last | os_name | xos_order_id | xos_status_id | xos_datetime |
|--------------------|-------------------|---------------------|--------------|---------------|------------------------|
| Sally | Sue | Order Pre-Processed | 34049 | 31 | June, 18 2015 12:42:50 |
| Joe | Schmoe | Order Pre-Processed | 34050 | 31 | June, 18 2015 12:54:50 |
| Peter | Piper | Order Paid | 34051 | 20 | June, 18 2015 12:37:30 |
如果您返回第一个查询...,我需要那些结果,但是需要减少这些结果,以便仅保留每个订单的最新状态。我实际上是在PHP中完成此操作之后...因此,我在重新查询该查询,以从PHP中删除看似不必要的步骤。
也许我的解决方案是XrefOrdersStatuses查询(上面第二个查询),但是使用RIGHT JOIN来获取Order和Status表?
有人在想吗?抱歉让您这么长(我自己几乎是TL; DR),但是我希望我已经适当地注释了这个问题。
顺便说一句-我是一个长期的SO(从这里找到的问题中收集了无数的问题和技巧!)但这是我第一次陷入困境,无法弄清楚如何获得所需的东西。
编辑/答案:按照我需要的方式进行了查询设置,发现我的性能问题与列和索引有关。有点像去医院治疗腹部疼痛,只是被告知您患有克罗恩氏病,然后发现您的肾脏有肿瘤-最终一切都好了。
# Query for answer to user Linoff with mods
SELECT o.shopper_name_first, o.shopper_name_last, s.os_name, x.*
FROM Orders o
RIGHT JOIN XrefOrdersStatuses x ON x.xos_order_id = o.order_id
RIGHT JOIN
(
SELECT xos_order_id, MAX(xos_datetime) AS maxdate
FROM XrefOrdersStatuses
GROUP BY xos_order_id
) xmax ON xmax.xos_order_id = x.xos_order_id AND xmax.maxdate = x.xos_datetime
LEFT JOIN Statuses s ON s.os_id = x.xos_status_id
ORDER BY o.order_datetime DESC;
最佳答案
这是你想要的?
SELECT <choose your columns here>
FROM Orders o LEFT JOIN
XrefOrdersStatuses x
ON x.xos_order_id = o.order_id LEFT JOIN
(SELECT xos_order_id, MAX(xos_datetime) AS maxdate
FROM XrefOrdersStatuses
GROUP BY xos_order_id
) xmax
ON xmax.xos_order_id = x.xos_order_id AND
xmax.maxdate = x.xos_datetime;
仅当订单中没有状态记录时,
LEFT JOIN
才是必需的。