sql-server - DYNAMICALLY 重复 SELECT 查询结果

Question

这是我的查询:

SELECT Barcode 
FROM Table_Barcode
WHERE IdArticle = 'Ar-1029344'

结果是这样的:

BarCode
-------
5142589
0123454
1111145

我想复制每个寄存器，比如复制4次这样:

BarCode
-------
5142589
5142589
5142589
5142589
0123454
0123454
0123454
0123454
1111145
1111145
1111145
1111145

编辑

我要动态的，因为以后不知道要不要复制寄存器 4 次或 10 次或 25 次

Answer 1

使用交叉连接:

SqlFiddleDemo

SELECT t.Barcode
FROM Table_Barcode t
CROSS JOIN (VALUES (1), (2), (3), (4)) AS tab(col)
WHERE t.IdArticle = 'Ar-1029344'

具有可变重复的第二个版本:

DECLARE @rep INT = 5;  /* How many times should be repated */

WITH cte AS
(
   SELECT TOP (@rep)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   FROM sys.All_Columns ac1         /* You can use any table to populate */
   CROSS JOIN sys.ALL_Columns ac2   /* You can use any table to populate */
)
SELECT t.BarCode
FROM TABLE_BARCODE t
CROSS JOIN cte
ORDER BY t.BarCode;

或者，如果您知道最大重复次数，您可以对值进行硬编码，例如:

DECLARE @rep INT = 5;  /* How many times should be repated */

WITH cte AS
(
   SELECT TOP(@rep) col
   FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
                (11), (12), (13), (14), (15), (16), (17), (18), (19), (20)
                (21), (22), (23), (24), (25)) AS tab(col)
)
SELECT t.BarCode
FROM TABLE_BARCODE t
CROSS JOIN cte;