我有一個數字范圍串列，需要將它們轉換為這些范圍內所有數字的串列。我正在使用 MS SQL 資料庫
我有一張包含以下資訊的表格
| 范圍開始 | 范圍結束 |
|---|---|
| 01135489651 | 01135489750 |
| 01142225487 | 01142225686 |
| 01159857992 | 01159858090 |
我想要一種 SQL 方法將范圍擴展到單個數字，如下所示每行一個。
| 結果 |
|---|
| 01135489651 |
| 01135489652 |
| ... |
| ... |
| 01135489749 |
| 01135489750 |
| 01142225487 |
| 01142225488 |
| ... |
| ... |
| 01142225685 |
| 01142225686 |
| 01159857992 |
| 01159857993 |
| ... |
| ... |
| 01159858089 |
| 01159858090 |
理想情況下作為 CTE，不需要臨時表。
有沒有人有一個可行的解決方案？
uj5u.com熱心網友回復：
簡單的遞回 cte 版本。讓 cte 生成從最小 RangeStart 到最大 RangeEnd 的所有數字。加入：
WITH RECURSIVE cte(n) as
(
select min(RangeStart) from tbl
UNION ALL
SELECT n 1
FROM cte
WHERE n <= (select max(RangeEnd) from tbl)
)
select n
from cte
join tbl on n between RangeStart and RangeEnd
如果您的列是字符而不是數字，請添加一些演員表：
WITH RECURSIVE cte(n) as
(
select min(cast(RangeStart as bigint)) from tbl
UNION ALL
SELECT n 1
FROM cte
WHERE n <= (select max(cast(RangeEnd as bigint)) from tbl)
)
select '0' || cast(n as varchar(15))
from cte
join tbl on n between cast(RangeStart as bigint)
and cast(RangeEnd as bigint)
uj5u.com熱心網友回復：
如果沒有 VerticaTIMESERIES子句（PostGreSQL 也可以從無到有生成一系列，但不同），試試這個：
WITH
-- input ..
indata(Range_Start,Range_End) AS (
SELECT '01135489651','01135489750'
UNION ALL SELECT '01142225487','01142225686'
UNION ALL SELECT '01159857992','01159858090'
)
-- REAL query starts here, replace following comma with "WITH"
,
-- max range size is just under 200, so need a list of 200 consecutive integers ..
-- create a table "units" with integers 0 through 9 ...
units(unit) AS (
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
)
,
-- cross join integers 0 through 9 with each other three times and limit the
-- list for the hundreds to 0 and 1, leaving you with 0 through 199 ....
i(i) AS (
SELECT
h.unit * 100 d.unit * 10 u.unit
FROM units h
CROSS JOIN units d
CROSS JOIN units u
WHERE h.unit < 2
)
SELECT
'0'
||CAST (
CAST(range_start AS INTEGER) i
AS VARCHAR(16)
) AS result
FROM indata
CROSS JOIN i
WHERE CAST(range_start AS INTEGER) i <= CAST(range_end AS INTEGER)
ORDER BY result
;
uj5u.com熱心網友回復：
由于您沒有超過 200 個要計算的數字，因此只能將一個簡單的選擇查詢與值和交叉應用一起使用：
select
[Range Start]
,[Range End]
,[n] = n1 n10 n100
,[number] = [Range Start] n1 n10 n100
from (values(01135489651,01135489750),(01142225487,01142225686),(01159857992,01159858090))ranges([Range Start],[Range End])
CROSS APPLY (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(n1)
CROSS APPLY (values(0),(10),(20),(30),(40),(50),(60),(70),(80), (90))n10(n10)
CROSS APPLY (values(0),(100))n100(n100)
where
n1 n10 n100 BETWEEN 0 and [Range End] - [Range Start]
| 范圍開始 | 范圍結束 | n | 數字 |
|---|---|---|---|
| 1135489651 | 1135489750 | 0 | 1135489651 |
| 1135489651 | 1135489750 | 1 | 1135489652 |
| ... | |||
| 1135489651 | 1135489750 | 99 | 1135489750 |
| 1142225487 | 1142225686 | 0 | 1142225487 |
| 1142225487 | 1142225686 | 1 | 1142225488 |
| ... | |||
| 1142225487 | 1142225686 | 198 | 1142225685 |
| 1142225487 | 1142225686 | 199 | 1142225686 |
uj5u.com熱心網友回復：
您可以使用以下語法在資料庫中創建一個表，為您提供非常大的值
SELECT TOP 1000000000000
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY A.name) AS 'Range'
into numbers --- a number table with list of values
FROM syscolumns A, syscolumns B
之后使用以下內容在您的資料庫中創建表
SELECT * INTO your_new_table
from numbers
where range between 01135489651 and 01135489750
union
select range
from numbers
where range between 01142225487 and 01142225686
union
select range
from numbers
where range between 01159857992 and 01159858090
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/347374.html
標籤：sql sql-server 查询语句