我想问一下我下面的代码列出的是同一排相邻的空座位，我想达到的效果是如果有3个或4个人相邻在同一排怎么修改

Question

用的mysql 8

select SEAT,L_SEAT,rowid,L_rowid  from (
select *,
LAG(SEAT) OVER(ORDER BY SEAT*1) L_SEAT,
LAG(STATUS) OVER(ORDER BY SEAT*1) L_STATUS,
LAG(rowid) over(order BY seat*1) L_rowid

from ks3) a
WHERE status='未预订' AND L_STATUS='未预订' and L_rowid=rowid 

Answer 1

说一下大概的思路吧，mysql没怎么用过😂
我用PGSQL简单说明一下思路

1. 构造一个每组的路径表达列

path： 未预定0 已预定1 因为数据里没有连续已预定的3个或者4个 所以我找一下连续未预定的数据
beg3 : 连续3个0开始的字符串index
beg4: 连续4个0开始的字符串index
seat_ids：seat分组之后的列表数据

rowid    path    beg3    beg4    seat_ids    aim_seat_ids
 A    01000    3    null    [1,2,3,4,5]      [3,4,5]
 B    10000    2    2       [6,7,8,9,10]     [7,8,9,10]
 C    01100    null null    [11,12,13,14,15] null
 D    00001    1    1       [16,17,18,19,20] [16,17,18,19]

select 
rowid,
array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ) as path,
position('000' in  array_to_string(ARRAY(SELECT distinct unnest(array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ))),'')) as beg3,
position('0000' in  array_to_string(ARRAY(SELECT distinct unnest(array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ))),'')) as beg4,
array_agg(seat) as seat_ids,
-- 使用数组切片 找出对应的数据 如果beg3不被识别 可以用position('000' in  array_to_string(ARRAY(SELECT distinct unnest(array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ))),''))代替
-- 应该可以被识别 😂
array_agg(seat)[beg3,beg3+3] as aim_seat_ids3,
array_agg(seat)[beg4,beg4+3] as aim_seat_ids4
 from taable 
group by rowid

根据上面的临时表 找出所有的目标数据的ID

说一下思路吧 就是把上一步的临时表中的 aim_seat_ids3 和 aim_seat_ids4 列 取distinct 作为一个新的数组 t
然后再原表使用(假设上一步的临时表是T)

伪代码实现

-- 临时表T
with T as (
select 
rowid,
array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ) as path,
position('000' in  array_to_string(ARRAY(SELECT distinct unnest(array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ))),'')) as beg3,
position('0000' in  array_to_string(ARRAY(SELECT distinct unnest(array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ))),'')) as beg4,
array_agg(seat) as seat_ids,
-- 使用数组切片 找出对应的数据 如果beg3不被识别 可以用position('000' in  array_to_string(ARRAY(SELECT distinct unnest(array_agg(case status = ‘未预定’ then ‘0’ else ‘1’ end ))),''))代替
-- 应该可以被识别 😂
array_agg(seat)[beg3,beg3+3] as aim_seat_ids3,
array_agg(seat)[beg4,beg4+3] as aim_seat_ids4
 from taable 
group by rowid
)

select * 
from  table 
where seat in   (  
  select distinct unnest(aim_seat_ids3) from T union select distinct unnest(aim_seat_ids4) from T 
 )

ps没有运行过😂 貌似可以找出题主需要的数据