SQL Server语言 - 返回触发器值的最后一个实例之后的所有记录

Question

在下面的示例中，查询需要返回最后一个'bar'之后的所有记录（按 Id 排序）。 因此，它将返回最后三条记录。

如果'bar'不存在，则查询需要返回所有记录。

Id     Trigger  
1      foo      
2      foo     
3      **bar**      
4      foo      
5      **bar**      
6      foo  
7      foo  

试图找出一个优雅的解决方案，没有（或最小的）子查询、if-thens、变量......

Answer 1

可以使用子查询来计算包含 'bar '.当子查询不返回任何行时，COLAESCE可用于提供默认值 0 。

SELECT t.id
FROM mytable t
WHERE t.id >= COALESCE(
    (SELECT max(id) from mytable where trigger = 'bar'),
    0
)

db<>小提琴在这里

WITH mytable as (
    SELECT 1 id, 'a' trig
    UNION ALL SELECT 2, 'foo'
    UNION ALL SELECT 3, 'bar'
    UNION ALL SELECT 4, 'zoo'
)
SELECT id
FROM mytable t
WHERE id >= COALESCE(
    (SELECT max(id) from mytable where trig = 'bar'),
    0
)
GO

 |身份证 | |-: | | 3 | | 4 | 

Answer 2

您可以使用使用条件聚合的相关子查询来获取值等于"bar"的最大 ID，如果没有"bar"则获取最小 ID。

SELECT *
       FROM [elbat] t1
       WHERE t1.[id] >= (SELECT coalesce(max(CASE t2.[trigger]
                                               WHEN 'bar' THEN
                                                 t2.[id]
                                             END),
                                         min(t2.[id]))
                                FROM [elbat] t2);

数据库<>小提琴

Answer 3

基本上，您希望所有没有具有更大'bar'值的行的行。 我认为最优雅的表达方式是：

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.trigger = 'bar' and t2.id > t.id
                 );

另一种方法使用窗口函数：

select t.*
from (select t.*,
             max(case when trigger = 'bar' then id end) over () as max_bar_id
      from t
     )
where max_bar_id is null or
      id >= max_bar_id;

Answer 4

另一个使用窗口聚合，如果基本数据是复杂查询的结果（因为只有一个访问权限），或者你需要分配多个 foo/bar 组，则主要有用：

WITH cte AS
 (
   SELECT *
     -- cumulative count, assigns 0 to the first group of foo/bar rows
     ,Count(CASE WHEN [trigger] = 'bar' THEN 1 end) -- only count 'bar'
      Over (ORDER BY id DESC -- highest id first
            ROWS BETWEEN Unbounded Preceding AND 1 Preceding) AS flag
   FROM tab
 )
SELECT * 
FROM cte 
WHERE flag = 0 -- first group

参见数据库<>提琴