在我的SQL数据库中，我有一个表，代表票证的书[Books]，其中一本书中票证的数量可以变化。
这由两列表示[Books].[StartNo]，[Books].[BookSize]
[Books].[StartNo]
[Books].[BookSize]
我需要实现的是一条select语句，该语句针对该书中的每张票重复表[Books]的每一行，并带有一个额外的计算列来显示该行的票号。
所以从
--------+---------+---------- Book | StartNo | BookSize --------+---------+---------- Book 1 | 1 | 3 Book 2 | 4 | 4 Book 3 | 19 | 4
像这样
--------+---------+----------+---------- Book | StartNo | BookSize | TicketNo --------+---------+----------+---------- Book 1 | 1 | 3 | 1 Book 1 | 1 | 3 | 2 Book 1 | 1 | 3 | 3 Book 2 | 4 | 4 | 4 Book 2 | 4 | 4 | 5 Book 2 | 4 | 4 | 6 Book 2 | 4 | 4 | 7 Book 3 | 19 | 4 | 19 Book 3 | 19 | 4 | 20 Book 3 | 19 | 4 | 21 Book 3 | 19 | 4 | 22
我只是不太确定从哪里开始。
使用 tally table
tally table
WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) --10 * 10 = 100 ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv0 b) --100 * 10 = 1000 ,Tally (num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv2) SELECT (num+StartNo-1) as TicketNo, * FROM Tally CROSS JOIN Yourtable WHERE num <= booksize ORDER BY book
