表自联接问题 - 需要一个期望输出
问题描述:
我有以下结构的表:表自联接问题 - 需要一个期望输出
OLD_ID New_ID Month_Number
------ ------ ------------
A 1
A B 2
B C 3
C D 4
我需要一个输出了该表的一个ID“A”为:
New_ID Oldest_Id Latest_ID
------ --------- ---------
A A D
B A D
C A D
D A D
可以使用month_number找到最老的ID和最新的ID。
请指教,我是SQL新手。
答
这个答案假定Microsoft SQL Server,但对语法进行了调整,只要它具有临时表,行数和用户定义的变量(后者仅用于安全截止以免发生循环)。此答案还假定源表为#ExampleTable
,[New_ID]
列包含所有ID,并且只有在相应的[Month_Number]
值适当时才应考虑较旧/较新的ID。 LTE针对旧ID的“更新”ID为[Month_Number]
。
基本上我会设想是让两个临时表:先列出每个[ID]
与第一[Old_ID]
为[ID]
一起,第二个虽然最后[New_ID]
相同:
-- Create temporary tables and declare cutoff variable
DECLARE @cutoff TINYINT;
SET @cutoff = 0;
CREATE TABLE #ID1 (
[ID] CHAR(1) NOT NULL
,[Oldest_ID] CHAR(1) NOT NULL
,[Month_Number] TINYINT NOT NULL
);
CREATE TABLE #ID2 (
[ID] CHAR(1) NOT NULL
,[Latest_ID] CHAR(1) NOT NULL
,[Month_Number] TINYINT NOT NULL
);
-- Initialise temporary tables
INSERT INTO #ID1 ([ID],[Oldest_ID],[Month_Number])
SELECT [New_ID],[New_ID],[Month_Number]
FROM #ExampleTable;
INSERT INTO #ID2 ([ID],[Latest_ID],[Month_Number])
SELECT [ID],[ID],[Month_Number]
FROM #ID1;
-- Fetch oldest IDs first
WHILE(@@ROWCOUNT != 0 AND @cutoff <= 100)
BEGIN
SET @cutoff = @cutoff + 1;
UPDATE oldies
SET oldies.[Oldest_ID] = d.[OLD_ID]
,oldies.[Month_Number] = d.[Month_Number]
FROM #ID1 AS oldies
JOIN #ExampleTable d
ON oldies.[Oldest_ID] = d.[New_ID]
WHERE d.[Month_Number] <= oldies.[Month_Number]
AND d.[OLD_ID] IS NOT NULL;
END;
-- Now the... "newies"
WHILE(@@ROWCOUNT != 0 AND @cutoff <= 100)
BEGIN
SET @cutoff = @cutoff + 1;
UPDATE newies
SET newies.[Latest_ID] = d.[New_ID]
,newies.[Month_Number] = d.[Month_Number]
FROM #ID2 AS newies
JOIN #ExampleTable d
ON newies.[Latest_ID] = d.[OLD_ID]
WHERE d.[Month_Number] >= newies.[Month_Number];
END;
-- Finally, do as you wish with #ID1 and #ID2
SELECT d.[New_ID] AS [ID]
,f1.[Oldest_ID]
,f2.[Latest_ID]
FROM #ExampleTable AS d
JOIN #ID1 AS f1 ON d.[New_ID] = f1.[ID]
JOIN #ID2 AS f2 ON d.[New_ID] = f2.[ID]
;
的#ID1
和#ID2
中的记录设置为引用自己开始。然后,对于循环中的每个传递,如果可用,则使用下一步更新ID的值。当没有其他事情要做时,迭代停止。
下面是各个循环会发生什么:
- 通#1: *旧:乙 - >; C - > B; D - > C
- 新:A - > B; B→C; Ç - > d
- 通行证#2:
- 旧:C - > A; D - > B
- 新:A - > C;乙 - > d
- 通行证#3:
- 旧:d - >甲
- 新:A - > d
- 通行证#4:
- (不需要更多操作)
@cutoff
变量仅仅是为了防止这种情况永久循环,如果ID'A'引用旧ID'B'和ID'B'引用旧ID'A',举个例子。
这不是一个非常有效的方法,你可能在某些练习中可以做得更好:例如,将Oldie/Newie操作合并为一个,但我将把它留给你。 :)
请用您正在使用的数据库标记您的问题。 –