将标题行扩展为多个子行

问题描述

在我的 SQL 数据库中，我有一个表，它表示门票 [Books]，其中一本书中的门票数量可能会有所不同.

Within my SQL database I have a table which represents books of tickets [Books] where the number of tickets within a book can vary.

这由两列 [Books].[StartNo] 和 [Books].[BookSize]

我需要实现的是一个选择语句，该语句为该书中的每张票重复表 [Books] 中的每一行，并附加一个计算列，显示该行的票号.

What I need to achieve is a select statement that repeats each row in the table [Books] for each ticket in that book with an additional calculated column that displays the ticket number for that row.

所以来自

--------+---------+----------
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

我只是不太确定从哪里开始.

I'm just not quite sure where to start.

推荐答案

使用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 

这篇关于将标题行扩展为多个子行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！