将多个数量的行转换为单个数量的多个行,同时使用部分数据加入另一个表格
问题描述:
我有两个表格,一个带有ID和其数量,另一个带有属于相应ID的名称。他们需要进行连接,以便最终结果是每行中计数为1的表格以及它们旁边的相应名称。请注意,在某些情况下,表2中的名称数量少于表1中相同ID的数量。将多个数量的行转换为单个数量的多个行,同时使用部分数据加入另一个表格
表1
ID | Count
-----------
100 | 3
101 | 2
102 | 4
表2
ID | Name
----------
100 | abc
100 | def
101 | ghi
101 | jkl
102 | mno
102 | pqr
102 | stu
结果
ID | Count | Name
------------------
100 | 1 | abc
100 | 1 | def
100 | 1 |
101 | 1 | ghi
101 | 1 | jkl
102 | 1 | mno
102 | 1 | pqr
102 | 1 | stu
102 | 1 |
我使用T-SQL,这和我现在的查询转换表1到结果多行表;然后通过循环将表2中的各个名称插入结果表中。我希望有一个更简单或更有效的方法来做到这一点,因为目前的方法需要相当长的时间。如果有,请告诉我。
答
我想到的,我涉及使用一个号码表,您可以创建它(作为一次性的任务)这样的第一件事:
CREATE TABLE numbers (
ID INT
)
DECLARE @CurrentNumber INT, @MaxNumber INT
SET @MaxNumber = 100 -- Choose a value here which you feel will always be greater than MAX(table1.Count)
SET @CurrentNumber = 1
WHILE @CurrentNumber <= @MaxNumber
BEGIN
INSERT INTO numbers VALUES (@CurrentNumber)
SET @CurrentNumber = @CurrentNumber + 1
END
一旦你有一个数字表,可以像这样解决这个问题:
SELECT one.ID,
1 AS [Count],
ISNULL(two.Name,'') AS Name
FROM table1 one
JOIN numbers n ON n.ID <= CASE WHEN one.[Count] >= (SELECT COUNT(1) FROM table2 two WHERE one.ID = two.ID)
THEN one.[Count]
ELSE (SELECT COUNT(1) FROM table2 two WHERE one.ID = two.ID)
END
LEFT JOIN (SELECT ID,
Name,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RecordNo
FROM table2) two ON one.ID = two.ID
AND two.RecordNo = n.ID
对于同一个ID,表2中的名称数是否超过表1中的计数?如果是这样,如何决定省略哪些名称? – onedaywhen
是的,理想情况下,在T2中应该有相同数量的名称作为T1中的计数,但有时名称不是最新的。因此,一旦查询用完名称后续ID需要保持空白 – HarryS
T2中的名称永远不会超过T1中的计数。名字可能会少一些,但从不多。 – HarryS