我在获取适当的数据时遇到了麻烦。
我有这样的表结构:
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