SQL 查询以查找具有相似 ID 的行，这些行"does not"/"does"在辅助列中具有模式

Question

我正在尝试为共享重复ID(彼此相关(但在辅助列中保留特定模式的行编写查询。

想象一下，这是一个网店，每个订单都有几个阶段。每个orderid在表中都有多个条目。

我想排除一些Orderid，因为它们有特定的模式。

示例：

Orderid     Stage     OtherInfo
----------------------------------
1           step 1    random info    
1           step 3    random info
1           step 4    random info
3           step 1    random info
3           step 2    random info
5           step 3    random info
4           step 1    random info
4           step 2    random info
4           step 3    random info

现在我想查询相应的OrderID满足条件的行，例如：

• 已完成"步骤1"&"步骤3"//但不是"步骤2">

预期结果为：

Orderid      Stage     OtherInfo
-----------------------------------
1            step 1    random info
1            step 3    random info
1            step 4    random info

Answer 1

您可以使用聚合和having:获取订单ID

select orderid
from t
group by orderid
having sum(case when stage = 'step 1' then 1 else 0 end) > 0 and
sum(case when stage = 'step 3' then 1 else 0 end) > 0 and
sum(case when stage = 'step 2' then 1 else 0 end) = 0;

获取原始行：

select t.*
from t
where t.orderid in (select orderid
from t
group by orderid
having sum(case when stage = 'step 1' then 1 else 0 end) > 0 and
sum(case when stage = 'step 3' then 1 else 0 end) > 0 and
sum(case when stage = 'step 2' then 1 else 0 end) = 0
);

Answer 2

使用EXISTS检查条件：

SELECT * 
FROM tablename t
WHERE
EXISTS (SELECT 1 FROM tablename WHERE tablename.Orderid = t.Orderid AND tablename.stage = 'step 1')
AND
EXISTS (SELECT 1 FROM tablename WHERE tablename.Orderid = t.Orderid AND tablename.stage = 'step 3')
AND
NOT EXISTS (SELECT 1 FROM tablename WHERE tablename.Orderid = t.Orderid AND tablename.stage = 'step 2');

Answer 3

您也可以使用

SELECT *
FROM T TT
WHERE CASE WHEN EXISTS(SELECT 1 FROM T WHERE OrderID = TT.OrderID AND Stage = 'step 1')
THEN 
CASE WHEN EXISTS(SELECT 1 FROM T WHERE OrderID = TT.OrderID AND Stage = 'step 3')
THEN 
CASE WHEN NOT EXISTS (SELECT 1 FROM T WHERE OrderID = TT.OrderID AND Stage = 'step 2')
THEN 1
END
END
END = 1;

或

SELECT *
FROM T TT
WHERE CASE WHEN EXISTS(SELECT 1 FROM T WHERE OrderID = TT.OrderID AND Stage = 'step 1')
AND
EXISTS(SELECT 1 FROM T WHERE OrderID = TT.OrderID AND Stage = 'step 3')
AND
NOT EXISTS (SELECT 1 FROM T WHERE OrderID = TT.OrderID AND Stage = 'step 2')
THEN 1
END = 1;