我在获取适当的数据时遇到了麻烦。

我有这样的表结构:

id     INT(11)    AI
order_id     INT(11)
status varchar(45)


该表的日志状态会更改订单。
因此,order_id的状态将很少。

现在,我需要选择行并将它们按order_id分组,在这些行中,订单永远不会有状态(甚至没有给定order_id的状态)!='example'

我们不显示订单,其中一个成员的状态为=示例

样本数据

1   12   ready
1   12   example
2   13   ready
2   13   sent


因此,我根本不希望显示订单12,因为其中一个成员具有“示例”状态

我已经尝试对结果进行分组,但这还不够。

最佳答案

不太确定您是否要获得状态为example的订单记录,或者是从未具有example状态的记录

获取状态为例如的订单列表(状态分组):

SELECT a.order_id, GROUP_CONCAT(a.status)
FROM SomeTable a
INNER JOIN
(
    SELECT order_id, COUNT(*)
    FROM SomeTable
    WHERE status = 'example'
    GROUP BY order_id
) b
ON a.order_id = b.order_id
GROUP BY order_id


要获得从未有过如此地位的人

SELECT a.order_id, GROUP_CONCAT(a.status)
FROM SomeTable a
LEFT OUTER JOIN
(
    SELECT order_id, COUNT(*)
    FROM SomeTable
    WHERE status = 'example'
    GROUP BY order_id
) b
ON a.order_id = b.order_id
WHERE b.order_id IS NULL
GROUP BY order_id


编辑

SELECT a.order_id, GROUP_CONCAT(a.status)
FROM SomeTable a -- Statuses
LEFT OUTER JOIN
(
    SELECT order_id, COUNT(*)
    FROM SomeTable
    WHERE status = 'example'
    GROUP BY order_id
) b -- Get any order id which has had a status of example (as a LEFT JOIN)
ON a.order_id = b.order_id
INNER JOIN
(
    SELECT order_id, MAX(id) AS Latestid
    FROM SomeTable
    GROUP BY order_id
) c -- Get the latest status for each order (ie, max id)
ON a.order_id = c.order_id
LEFT OUTER JOIN
(
    SELECT order_id, id
    FROM SomeTable
    WHERE status = 'example2'
) d -- Get the id of the order status of example2
ON a.order_id = d.order_id AND c.Latestid = d.id -- join on the same order id and that the record id matches the latest record id
WHERE b.order_id IS NULL -- reject those where a match was found on example for any status
AND d.order_id IS NULL -- reject those where a match was found on example2 for the latest status
GROUP BY order_id

10-05 22:51
查看更多