从单行返回多行 [英] Returning multiple rows from a single row

问题描述

这可能是不可能的，但我想我会把它扔在这里:

This may not be possible, but I thought I'd throw it out here:

给定下表:

ID、开始、结束
123, 1, N

ID, Begin, End
123, 1, N

其中N为整数，写一个查询返回如下结果集:

Where N is an integer, write a query to return the following result set:

ID、开始、结束
123, 1, 1
123, 1, 2
123, 1, 3
.
.
.
123, 1, N

ID, Begin, End
123, 1, 1
123, 1, 2
123, 1, 3
.
.
.
123, 1, N

我们使用的平台是 SQL Server 2005，但如果您可以使用另一种风格的 SQL，我仍然对解决方案感兴趣.

The platform we are using is SQL Server 2005, but if you can do it with another flavor of SQL, I'd still be interested in the solution.

推荐答案

试试这个:

create table #smalltable (id int, [begin] int, [end] int)
insert into #smalltable values (123,1,4)
insert into #smalltable values (124,1,12)
insert into #smalltable values (125,1,7)

;WITH digits (d) AS (
    SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
    SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
    SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
    SELECT 0)
SELECT
    s.id, s.[begin], n.Number AS [End]
    FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
              v.d * 10000 + vi.d * 100000 AS Number
              FROM digits            i
                  CROSS JOIN digits  ii
                  CROSS JOIN digits  iii
                  CROSS JOIN digits  iv
                  CROSS JOIN digits  v
                  CROSS JOIN digits  vi
         ) AS N
        INNER JOIN #smalltable                                    s  ON 1=1
        INNER JOIN (SELECT MAX([end]) AS MaxEnd FROM #smalltable) dt ON 1=1
   WHERE n.Number > 0 AND n.Number<=dt.MaxEnd
    AND n.Number<=s.[end]
   ORDER BY s.id,n.Number

评论

• 不要将您的专栏命名为保留字:开始"和结束"，您总有一天会感谢我的.
• 如果您计划在生产环境中多次运行它，创建一个数字表
  并改用此查询:

必须有一个表格 Numbers 才能使用(见上面的链接)

have to have a table Numbers before this will work (see link above)

SELECT
    s.id,s.[begin],n.Number AS [End]
    FROM Numbers                n
        INNER JOIN #smalltable  s ON 1=1
   WHERE  n.Number > 0 AND n.Number<=s.[end]
   ORDER BY s.id,number

它会运行得更好.

这篇关于从单行返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！