具有最大值的增量变量的SQL连接表数据

Question

我想知道如何获得两个辅助表的联接查询，以获得一组完整的生产线测试槽“名称”或“标记”

第一个辅助表包含关于某台机器有多少插槽的信息。看起来就像

Machine Types AUX table
------------------------------------------------------
MachineTypeID   SlotNumber
1               5
2               3
3               10

第二个有每条生产线的机器ID列表。看上去：

Lines AUX table
----------------------------------
LineID   MachineID   MachineTypeID
1        PR00231     1
1        PR00049     2
2        PR00147     1
2        PR00017     2
2        PR00178     3

我不知道如何使用机器I列表和每台机器的插槽数构建"MachineID+SlotNum“标记列表。

在逐行过滤时，预期结果如下：

LINE 2 list of slotsTAGs:
-----------------------------------
PR00147_Slot1
PR00147_Slot2
PR00147_Slot3
PR00147_Slot4
PR00147_Slot5
PR00017_Slot1
PR00017_Slot2
PR00017_Slot3
PR00178_Slot1
PR00178_Slot2
PR00178_Slot3
PR00178_Slot4
PR00178_Slot5
PR00178_Slot6
PR00178_Slot7
PR00178_Slot8
PR00178_Slot9
PR00178_Slot10

谢谢!

Answer 1

要生成这个结果，您需要形成一个数字表，该表由从1到MAX(SlotNumber)的数字组成。在这个查询中，我使用了一个递归的CTE来完成这个任务。然后，这可以是JOINed到Lines和Types表，以形成列表：

WITH cte1 AS (
SELECT  MAX(SlotNumber) AS MaxSlot FROM Types
), cte2 (Slot) AS 
(SELECT 1 AS Slot
 UNION ALL
 SELECT Slot+1 FROM cte2 WHERE Slot < (SELECT MaxSlot FROM cte1))
SELECT CONCAT(l.MachineID, '_Slot', c.Slot) AS Slots
FROM Lines l
JOIN Types t ON t.MachineTypeID = L.MachineTypeID
JOIN cte2 c ON c.Slot <= t.SlotNumber
WHERE l.LineID = 2
ORDER BY l.MachineId, c.Slot

输出：

Slots
PR00017_Slot1
PR00017_Slot2
PR00017_Slot3
PR00147_Slot1
PR00147_Slot2
PR00147_Slot3
PR00147_Slot4
PR00147_Slot5
PR00178_Slot1
PR00178_Slot2
PR00178_Slot3
PR00178_Slot4
PR00178_Slot5
PR00178_Slot6
PR00178_Slot7
PR00178_Slot8
PR00178_Slot9
PR00178_Slot10

dbfiddle演示

Answer 2

检查这段代码-

IF OBJECT_ID('[Machine Types AUX]') IS NOT NULL
    DROP TABLE [Machine Types AUX]

CREATE TABLE [Machine Types AUX]
(
MachineTypeID INT NOT NULL IDENTITY(1,1) CONSTRAINT [PK_MachineTypesAUX] PRIMARY KEY CLUSTERED,
SlotNumber INT NOT NULL
)
GO

INSERT INTO [Machine Types AUX] (SlotNumber)
VALUES (5),(3),(10)

IF OBJECT_ID('[Lines AUX]') IS NOT NULL
    DROP TABLE [Lines AUX]

CREATE TABLE [Lines AUX]
(
LineID INT NOT NULL,
MachineID VARCHAR(10) NOT NULL,
MachineTypeID INT NOT NULL
)
GO

INSERT INTO [Lines AUX] (LineID,MachineID,MachineTypeID)
VALUES (1,'PR00231',1),
(1,'PR00049',2),
(2,'PR00147',1),
(2,'PR00017',2),
(2,'PR00178',3)

--SELECT * FROM [Machine Types AUX]
--SELECT * FROM [Lines AUX]

--EXPECTED_RESULT:
SELECT CONCAT(MachineID,'_Slot',SlotNumber) AS slotsTAGs 
FROM [Machine Types AUX] T INNER JOIN [Lines AUX] T1 ON T.MachineTypeID=T1.MachineTypeID