sql—获取表中的第一个可用索引，包括0

pn9klfpd 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(228)

我有一张table叫 player_chest ，其中包含以下字段：

idx integer NOT NULL,
player_id integer NOT NULL,
chest_id integer NOT NULL,

我使用以下查询获取表中的第一个可用索引：

SELECT c.idx + 1 FROM player_chest c WHERE c.player_id = 2 AND NOT EXISTS (
  SELECT 1 FROM player_chest c1 WHERE c1.player_id = 2 AND c1.idx = c.idx + 1
) ORDER BY c.idx;

查询工作的示例：

chest_id | idx | player_id
       0 |   0 |         2
       1 |   1 |         2
       2 |   2 |         2
       1 |   4 |         2

返回3
查询不工作的示例：查询工作的示例：

chest_id | idx | player_id
       1 |   1 |         2
       2 |   2 |         2
       1 |   4 |         2

返回3，但我希望它返回0
但当索引可用时，它无法返回索引0。我该怎么修？

sql postgresql

来源：https://stackoverflow.com/questions/63315637/get-first-available-index-in-the-table-including-0

1条答案

按热度按时间

wixjitnu1#

一种解决方案使用窗口函数：

select (case when min_idx > 0 then 0
             else 1 + min(idx) filter (where next_idx is distinct from idx + 1)
        end)
from (select pc.*,
             lead(idx) over (partition by player_id order by idx) as next_idx,
             min(idx) over (partition by player_id) as min_idx
      from player_chest pc
      where player_id = 2
     ) pc
group by player_id, min_idx;

如果你想要一个返回的版本 0 即使玩家不在桌上，那么：

select (case when max(min_idx) > 0 or max(min_idx) is null then 0
             else 1 + min(idx) filter (where next_idx is distinct from idx + 1)
        end)
from (select pc.*,
             lead(idx) over (partition by player_id order by idx) as next_idx,
             min(idx) over (partition by player_id) as min_idx
      from player_chest pc
      where player_id = 2
     ) pc

赞(0）回复(0）举报 2021-07-24

我来回答

sql—获取表中的第一个可用索引，包括0

1条答案

相关问题

热门标签

最新问答