使用SQL查询生成多个数据

I have 2 tables as below

我有以下两张桌子

Product_Asset:

Product_Asset:

PAId      Tracks
1          2
2          3

Product_Asset_Resource:

Product_Asset_Resource:

Id        PAId        TrackNumber
1          1              1 
2          1              2 
3          2              1
4          2              2 
5          2              3

I would like to know if I can generate the data in product_asset_resource table based on product_asset table using TSQL query (without complex cursor etc.)

我想知道是否可以使用TSQL查询(没有复杂游标等)基于product_asset表生成product_asset_resource表中的数据。

For example, if the number of tracks in product_asset is 3 then I need to populate 3 rows in product_asset_resource with track numbers as 1,2,3

例如，如果product_asset中的跟踪数是3，那么我需要在product_asset_resource中填充3行，跟踪号为1,2,3

2 个解决方案

#1

You can do this with the help of a Tally Table.

你可以在一个计数表的帮助下做到这一点。

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(SELECT MAX(Tracks) FROM Product_Asset) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT
    Id  = ROW_NUMBER() OVER(ORDER BY pa.PAId, t.N),
    pa.PAId,
    TrackNumber = t.N
FROM Product_Asset pa
INNER JOIN CteTally t
    ON t.N <= pa.Tracks

ONLINE DEMO

在线演示

#2

Try this,I am not using any Tally Table

试试这个，我没有使用任何计数表

declare @Product_Asset table(PAId int,Tracks int)
insert into @Product_Asset values (1 ,2),(2, 3)

;with CTE as
(
select PAId,1 TrackNumber from @Product_Asset
union all

select  pa.PAId,TrackNumber+1 from @Product_Asset pa
inner join cte c on pa.PAId=c.PAId
where c.TrackNumber<pa.Tracks
)

select ROW_NUMBER()over(order by paid)id, * from cte

IMHO,Recursive CTE or sub query or using temp table performance depend upon example to example.

IMHO、递归CTE或子查询或使用临时表性能取决于示例。

I find Recursive CTE more readable and won't use them unless they exhibit performance problem.

我发现递归CTE更容易读，除非表现出性能问题，否则不会使用它们。

I am not convince that Recursive CTE is hidden RBAR. CTE is just syntax so in theory it is just a subquery

我不相信递归CTE是隐藏的RBAR。CTE只是语法，所以理论上它只是一个子查询

We can take any example to prove that using #Temp table will improve the performance ,that doesn't mean we always use temp table.

我们可以通过任何示例来证明使用#Temp表将提高性能，但这并不意味着我们总是使用临时表。

Similarly in this example using Tally Table may not improve the performance this do not imply that we should take help of Tally Table at all.

同样，在这个例子中使用计数表可能不会提高性能，这并不意味着我们应该完全依赖计数表。

#1