SQL 选择此选项可根据数据集中的数字多次返回一行

Question

所以我从这里尝试了许多其他关于这个主题的答案的尝试，到目前为止，一切都完全失败了，或者没有给我我想要的结果：

我有一个 select 语句用于传递传递信息的报表。结果集来自一个主表，每个交货编号只有一行(交货头记录(，在数据集中还有一个名为 palletspaces 的字段，我们用它来指示(您猜对了(交货需要多少个托盘

我现在需要做的是：

1. 查找该托盘空间编号
2. 返回与该托盘空间编号相同的次数的单次交付记录
3. 在结果中包含一个新列，该列最多计入该托盘空间编号

例如，我的SQL将从交付表中返回每条记录，如下所示

id          traderid    toaddressid county         postcode palletspaces
D-124597    2101        2           READING        RG6 1AZ  3
D-124600    20060       12          MAGOR, GWENT   NP26 3DF 1
D-124601    20060       13          RUGBY          CV23 8YH 2

所以现在，我需要查看该托盘空间编号，然后多次返回特定行，然后还有一个新列来计算这些实例：

id          traderid    toaddressid county         postcode palletspaces    LineCount
D-124597    2101        2           READING        RG6 1AZ  3               1
D-124597    2101        2           READING        RG6 1AZ  3               2
D-124597    2101        2           READING        RG6 1AZ  3               3
D-124600    20060       12          MAGOR, GWENT   NP26 3DF 1               1
D-124601    20060       13          RUGBY          CV23 8YH 2               1
D-124601    20060       13          RUGBY          CV23 8YH 2               2

另一件要提的事情是，我自然会有数百条不同的交货记录(每条记录都作为一行返回(，并且所有记录都有不同的托盘空间编号。当然，很明显，我需要该行仅根据其自己的托盘空间编号进行复制和计数

正在使用的 SQL 如下

select 
deliveries.id,
deliveries.traderid,
customers.name,
deliveries.toaddressid,
deliveries.eutransportid,
deliveries.street,
deliveries.city,
deliveries.county,
deliveries.postcode,
delivery_custom.palletspaces,
ectransport.ectranspdesc
from deliveries
INNER JOIN customers ON
deliveries.traderid = customers.id
INNER JOIN delivery_custom ON 
deliveries.id = delivery_custom.id
INNER JOIN ectransport ON
deliveries.eutransportid = ectransport.ectranspcode

Answer 1

尝试如下：

select deliveries.id,
deliveries.traderid,
customers.name,
deliveries.toaddressid,
deliveries.eutransportid,
deliveries.street,
deliveries.city,
deliveries.county,
deliveries.postcode,
delivery_custom.palletspaces,
ectransport.ectranspdesc
INTO #MyTemp
from deliveries
INNER JOIN customers ON
deliveries.traderid = customers.id
INNER JOIN delivery_custom ON 
deliveries.id = delivery_custom.id
INNER JOIN ectransport ON
deliveries.eutransportid = ectransport.ectranspcode

;WITH CTE AS(
SELECT id,traderid,toaddressid,county,postcode,palletspaces,1 AS LineCount 
FROM #MyTemp
UNION ALL
SELECT id,traderid,toaddressid,county,postcode,palletspaces,LineCount+1 
FROM CTE
WHERE LineCount<palletspaces
)
SELECT * 
FROM CTE
ORDER BY id, LineCount;
DROP TABLE #MyTemp

希望这次你明白了。

Answer 2

使用Recursive CTE，我们可以实现这一点：

DECLARE @TAB TABLE ([D Number] VARCHAR(20) ,customer INT, postcode VARCHAR(20), palletspaces INT)
INSERT INTO @TAB VALUES('D-123456' ,19114, 'DA12 1TF' , 4)
INSERT INTO @TAB VALUES('D-111111' ,19114, 'DDDD 1TF' , 3)
;WITH CTE AS(
SELECT [D Number],customer,postcode,palletspaces,1 AS A 
FROM @TAB
UNION ALL
SELECT [D Number],customer,postcode,palletspaces,A+1 
FROM CTE
WHERE A<palletspaces
)
SELECT * 
FROM CTE
ORDER BY [D Number], LineCount;

输出：

D Number    customer    postcode    palletspaces    LineCount
D-123456    19114       DA12 1TF    4               1
D-123456    19114       DA12 1TF    4               2
D-123456    19114       DA12 1TF    4               3
D-123456    19114       DA12 1TF    4               4