gpt4 book ai didi

sql - Oracle - 将值从行转换为范围

转载 作者:行者123 更新时间:2023-12-04 20:43:09 24 4
gpt4 key购买 nike

是否有任何技术可以允许这样设置行

WITH 
base AS
(
SELECT 1 N FROM DUAL UNION ALL
SELECT 2 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL

SELECT 6 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL

SELECT 17 N FROM DUAL UNION ALL
SELECT 18 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL

SELECT 21 N FROM DUAL
)
SELECT a.N
FROM base a

产生结果
 1     3
6 7
17 19
21 21

它实际上是行到范围操作。
我在 Oracle Land 玩游戏,如果有任何建议,我将不胜感激。

最佳答案

我觉得这可能可以改进,但它有效:

WITH base AS  (
SELECT 1 N FROM DUAL UNION ALL
SELECT 2 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 6 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 17 N FROM DUAL UNION ALL
SELECT 18 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL
SELECT 21 N FROM DUAL
)
, lagged AS
(
SELECT n, LAG(n) OVER (ORDER BY n) lag_n FROM base
)
, groups AS
(
SELECT n, row_number() OVER (ORDER BY n) groupnum
FROM lagged
WHERE lag_n IS NULL OR lag_n < n-1
)
, grouped AS
(
SELECT n, (SELECT MAX(groupnum) FROM groups
WHERE groups.n <= base.n
) groupnum
FROM base
)
SELECT groupnum, MIN(n), MAX(n)
FROM grouped
GROUP BY groupnum
ORDER BY groupnum

关于sql - Oracle - 将值从行转换为范围,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3629296/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com