为了实现查询所有有联系的数据,可以采用临时表和循环的方式。首先,建立一个临时表来存储查询结果,代码如下:
DECLARE @Temporarily TABLE (Temporarilyid int, TemporarilyPId int)
然后,声明临时变量a和b,用于存储第一个查询条件,如下所示:
DECLARE @a intset @a=(SELECT ID1 as a FROM test WHERE ID1 = 1)
DECLARE @b int
set @b=(SELECT ParentID as b FROM test WHERE ID1 = 1)
接下来,将第一条结果插入临时表中:
INSERT INTO @Temporarily VALUES (@a,@b)
接下来,通过循环来执行操作,循环数由记录的条数决定。代码如下:
DECLARE @i intSET @i = 1
WHILE @i <= 10
BEGIN
DECLARE @e int
set @e=(SELECT distinct Temporarilyid as e FROM @Temporarily WHERE Temporarilyid IN(SELECT ParentID FROM test WHERE ID1 = @i) or TemporarilyPId in(SELECT ID1 FROM test WHERE ID1 = @i))
IF @e>0 BEGIN
set @a=(SELECT ID1 as c FROM test WHERE ID1 = @i)
set @b=(SELECT ParentID as d FROM test WHERE ID1 = @i)
INSERT INTO @Temporarily VALUES (@a,@b)
END
SET @i = @i + 1
END
最后,显示结果集:
SELECT Temporarilyid AS a,TemporarilyPId AS b FROM @Temporarily
这样就可以满足查询所有有联系的数据需求了,无论数据量多少都可以适用。
尽管一个SQL语句可能无法实现这个功能,但这种方法可以较好地解决你的问题。如果你有其他更好的方法,请告诉我。