sql - 对于此任务是否有更有效的查询？

Question

一条消息被定义为 2 个类型为 0 的消息之间的所有消息。

我需要找到表中的第一个 block 确保 block 中存在所有行

我的解决方案:

SELECT MSG FROM (   
    SELECT 
    CASE  
        WHEN type = 83 AND next_type = 84 THEN REPLACE(CONCAT(MSG,next_msg),' ', '')
        WHEN type = 83 AND next_type != 84 THEN MSG
        WHEN type = 83 AND next_type IS NULL THEN MSG       
, ROW_NUMBER() OVER(order by id) AS row_num 

                ) AS tmp5
WHERE MSG IS NOT NULL

Answer 1

你的方法看起来非常好。您首先选择第一个和第二个零类型 ID，然后仅处理它们之间的 ID。不过，我不明白为什么要将 ID 与其相对位置 (row_num = id) 进行比较。这迫使您对表中的所有消息进行交叉联接，而不是仅仅使用内部联接、exists子句或 > Between 子句。

这是查询的缩短版本，用于批量查找邮件:

with limits as
(
  select min(id) as id01, max(id) as id02
  from (select top 2 id, type from messages where type = 0 order by id) first2
)
select case when next_type = 84 then msg + next_msg else msg end
from
(
  select
    type, lead(type) over(order by id) as next_type,
    msg, lead(msg) over(order by id) as next_msg  
  from messages m
  where id > (select id01 from limits)
    and id < (select id02 from limits)
) firstbulk
where type = 83;

您需要在 messages(type, id) 上建立索引来快速获取零类型 ID，当然还需要在 messages(id) 上建立索引快速批量选择行。

演示:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=eecb6383b9daa6963e08dde6a0dd30a1

编辑:您希望内置间隙检测。使用“COUNT(*) OVER () 和零类型 ID 来查看 ID 之间是否有与预期一样多的行。

with limits as
(
  select min(id) as id01, max(id) as id02
  from (select top 2 id, type from messages where type = 0 order by id) first2
)
select case when gaps <> 0 then 'Gap detected'
            when next_type = 84 then msg + next_msg 
            else msg end
from
(
  select
    m.type, lead(m.type) over(order by m.id) as next_type,
    m.msg, lead(m.msg) over(order by m.id) as next_msg,
    l.id02 - l.id01 - count(*) over () - 1 as gaps
  from messages m
  join limits l on l.id01 < m.id and l.id02 > m.id
) firstbulk
where type = 83;

演示:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=909228fd2696b419d14cd4a1c2c220a3