957
技術社區[雲棲]
SQL合並多表查詢記錄的存儲過程
思路：將查詢到的記錄保存進一個臨時表，再在臨時表中進行查詢，用完後刪除臨時表；
select * from dbo.PE_Nodes
CREATE PROCEDURE [dbo].[PR_GetNodeInfoByNodeID]
@Nodeid INT
declare
@str varchar(300),
@id varchar(300),
@m int,
@n int
select * into temp from dbo.PE_Nodes where 1=0 --select * into 表A from 表B：隻複製表解構，不複製數據；
set @str=SUBSTRING((select arrChildID from dbo.PE_Nodes where NodeIdentifier = (select NodeIdentifier from dbo.PE_Nodes where NodeID =Nodeid )),0,300)
set @m=CHARINDEX(',',@str)
set @n=1
WHILE @m>0
BEGIN
set @id=substring(@str,@n,@m-@n)
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
Insert into temp Select * from dbo.PE_Nodes where NodeID = @id --將查到的表數據插入到臨時表；
END
select * from temp
drop table temp --刪除臨時表
最後更新：2017-04-02 06:51:23