如何根据项目顺序查找结果

Question

假设您有一个Items表，其中有UniqueNames、id表示订单，以及每个Run的RunNumber

RunNumber33

对于这个示例数据，您可以使用聚合并在HAVING子句中设置条件:

SELECT RunNumber
FROM Items
GROUP BY RunNumber
HAVING MAX(CASE WHEN Name = 'Close' THEN Id END) < MAX(CASE WHEN Name = 'Open' THEN Id END);

或者，对于self join:

SELECT i1.RunNumber
FROM Items i1 INNER JOIN Items i2
ON i1.RunNumber = i2.RunNumber AND i1.id < i2.id
WHERE i1.Name = 'Close' AND i2.Name = 'Open';

由于Close按字母顺序排列在Open之前，那么您可以使用row_number()为按RunNumber分组并按名称排序的每个记录提供行号:

with cte as (
select *, row_number() over (partition by RunNumber order by id, Name) as rn
from mytable
)
select *
from cte
where id = 1 and rn = 1 and Name = 'Close';

或者您可以使用order bycase when:

来指定Close和Open的顺序。

with cte as (
select *,
row_number() over (partition by RunNumber order by case Name when 'Close' then 1 when 'Open' then 2 end, id) as rn
from mytable
)
select *
from cte
where id = 1 and rn = 1 and Name = 'Close';

演示