不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

如何合并结果集

楼主sdd330（小飞羊）2005-08-23 20:23:00 在 MS-SQL Server / 基础类提问

我从下面的存储过程获得了多个结果集,如何将他们合并成一个表再返回啊,请高手指教
CREATE PROCEDURE spciwfTasLoadByOrg
@UserID int
AS
DECLARE @id int

SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%U'+CAST(@UserID AS varchar(10))+'%'

DECLARE cur1 CURSOR FOR
SELECT DeptID FROM [orgDU] WHERE UserID = @UserID
OPEN cur1
FETCH NEXT FROM cur1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%D'+CAST(@id AS varchar(10))+'%'
FETCH NEXT FROM cur1 INTO @id
END
CLOSE cur1
DEALLOCATE cur1

DECLARE cur1 CURSOR FOR
SELECT GroupID FROM [orgGU] WHERE UserID = @UserID
OPEN cur1
FETCH NEXT FROM cur1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%G'+CAST(@id AS varchar(10))+'%'
FETCH NEXT FROM cur1 INTO @id
END
CLOSE cur1
DEALLOCATE cur1

DECLARE cur1 CURSOR FOR
SELECT MajorID FROM [orgMU] WHERE UserID = @UserID
OPEN cur1
FETCH NEXT FROM cur1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%M'+CAST(@id AS varchar(10))+'%'
FETCH NEXT FROM cur1 INTO @id
END
CLOSE cur1
DEALLOCATE cur1

DECLARE cur1 CURSOR FOR
SELECT PostID FROM [orgPU] WHERE UserID = @UserID
OPEN cur1
FETCH NEXT FROM cur1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%P'+CAST(@id AS varchar(10))+'%'
FETCH NEXT FROM cur1 INTO @id
END
CLOSE cur1
DEALLOCATE cur1

DECLARE cur1 CURSOR FOR
SELECT RoleID FROM [orgRU] WHERE UserID = @UserID
OPEN cur1
FETCH NEXT FROM cur1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%R'+CAST(@id AS varchar(10))+'%'
FETCH NEXT FROM cur1 INTO @id
END
CLOSE cur1
DEALLOCATE cur1

DECLARE cur1 CURSOR FOR
SELECT TitleID FROM [orgTU] WHERE UserID = @UserID
OPEN cur1
FETCH NEXT FROM cur1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM [viwIwfTask] WHERE TaskState = '1' AND Performer LIKE '%T'+CAST(@id AS varchar(10))+'%'
FETCH NEXT FROM cur1 INTO @id
END
CLOSE cur1
DEALLOCATE cur1
GO
问题点数：20、回复次数：9Top

1 楼wgsasd311（自强不息）回复于 2005-08-23 23:15:04 得分 0

如果几个返回结果集的字段类型，及数量相同，可以用union all联成一个表。Top

2 楼Fibona（Fibona）回复于 2005-08-23 23:53:54 得分 0

用union all就行Top

3 楼MorningTea（一勺抹茶）回复于 2005-08-24 08:18:16 得分 0

如果是写在sp,不用union all连接来，但是必须插入一个表才可以直接引用

insert into 表(字段和你的返回结果一样)
exec spciwfTasLoadByOrgTop

4 楼sdd330（小飞羊）回复于 2005-08-24 09:36:09 得分 0

我用临时表,但是提示出错
CREATE PROCEDURE spciwfTasLoadByAllOrg
@UserID int
AS

DECLARE @temp table(
PkID int,
FlowID int,
PhaseID int,
Name varchar(64),
Performer varchar(32),
TaskState varchar(64),
TaskStateName varchar(64),
Deadline datetime,
Remind varchar(512),
Sender int,
SendTime datetime,
Receiver int,
ReceiveTime datetime,
FinishTime datetime,
TaskUrl varchar(512),
FormID int
)

INSERT INTO @temp EXEC spciwfTasLoadByOrg
SELECT * FROM @temp
GOTop

5 楼vivianfdlpw（）回复于 2005-08-24 09:40:11 得分 0

CREATE PROCEDURE spciwfTasLoadByAllOrg
@UserID int
AS

create table #(
PkID int,
FlowID int,
PhaseID int,
Name varchar(64),
Performer varchar(32),
TaskState varchar(64),
TaskStateName varchar(64),
Deadline datetime,
Remind varchar(512),
Sender int,
SendTime datetime,
Receiver int,
ReceiveTime datetime,
FinishTime datetime,
TaskUrl varchar(512),
FormID int
)

INSERT INTO # EXEC spciwfTasLoadByOrg
SELECT * FROM #
drop table #
GO
Top

6 楼vivianfdlpw（）回复于 2005-08-24 09:41:01 得分 20

table 变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。
在其作用域内，table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。但是，table 不能用在下列语句中：

INSERT INTO table_variable EXEC 存储过程。

SELECT select_list INTO table_variable 语句。

在定义 table 变量的函数、存储过程或批处理结束时，自动清除 table 变量。

Top

7 楼sdd330（小飞羊）回复于 2005-08-24 09:41:11 得分 0

用union all不行,循环的时候怎么处理啊Top

8 楼sdd330（小飞羊）回复于 2005-08-24 09:42:17 得分 0

谢谢vivianfdlpw()Top

9 楼JonathanLee（唐宋）回复于 2005-08-24 09:46:49 得分 0

可以在sp中创建个临时表，然后先将各个CURSOR插入到临时表中，最后再select * from 临时表Top