如果存在特定行类型，则从特定行选择行到最新行。

Question

我希望使用单查询实现这两个需求。目前，我在程序中使用两个查询，并使用C#完成流程部分，如下所示。

伪码

select top 1 id from table where type=b
if result.row.count > 0 {var typeBid = row["id"]}
select * from table where id >= {typeBid}
else
select * from table

Req1:如果type=b中存在记录，则结果应该是带有type=b的最新行，以及之后添加的所有其他行。

表格

--------------------
id  type    date
--------------------
1   b   2021-10-15
2   a   2021-11-16
3   b   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

结果

--------------------
id  type    date
--------------------
3   b   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

Req2: type=b中没有记录。查询应该选择表中的所有记录。

表格

---------------------
id  type    date
---------------------
1   a   2021-10-15
2   a   2021-11-16
3   a   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

结果

--------------------
id  type    date
--------------------
1   a   2021-10-15
2   a   2021-11-16
3   a   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

Answer 1

with max_b_date as (select max(date) as date
                    from table1 where type = 'b')
select t1.*
from table1 t1
cross join max_b_date 
where t1.date >= max_b_date.date
   or max_b_date.date is null

(table是一个SQL保留字，https://en.wikipedia.org/wiki/SQL_reserved_words，所以我用table1作为表名。)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bd05543a9712e27f01528708f10b209f

Answer 2

请试试这个(有点深，但你能确切地找一下吗？)

select ab.* from 
    ((select top 1 id, type, date from test where type = 'b' order by id desc)
    union 
    select * from test where type != 'b') as ab 
where ab.id >= (select COALESCE((select top 1 id from test where type = 'b' order by id desc), 0))
order by ab.id;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=739eb6bfee787e5079e616bbf4e933b1

Answer 3

看起来您可以在这里使用OR条件

SELECT  
    *
    FROM
    (
        SELECT  
            *,
            BCount = COUNT(CASE type WHEN 'B' THEN 1 ELSE NULL END)-- to get the Count of Records with Type b.
            FROM Table
    )Q
    WHERE 
    (
        BCount > 0 AND id >= (select top 1 id from table where type=b)-- if there are Row's with Type b then select Req#1
    )
    OR
    (
        BCount = 0 -- if THere are no rows with Type B select All
    )