SQLSERVER 游标的使用

解决问题

解决表关联后从表数据字段的串联操作（帮BBS https://bbs.csdn.net/topics/393099374）

原问题

--建表
create table Family
(
	FID int primary key,
	ParentName varchar(20),
	Addr       varchar(50)
)



create table Children
(
	CID int identity(1,1) primary key, 
	FID int,
	ChildName varchar(20),
	Age int, 
)


--测试数据
insert into Family (FID, ParentName,Addr) values (1, '张三', '中华大街');
insert into Family (FID, ParentName,Addr) values (2, '李四', '云海南路');
insert into Family (FID, ParentName,Addr) values (3, '王五', '和平小巷');


insert into Children (FID, ChildName,Age) values (1, '张小', 10);

insert into Children (FID, ChildName,Age) values (2, '李一', 8);
insert into Children (FID, ChildName,Age) values (2, '李二', 12);

insert into Children (FID, ChildName,Age) values (3, '王龙', 13);
insert into Children (FID, ChildName,Age) values (3, '王虎', 10);
insert into Children (FID, ChildName,Age) values (3, '王凤', 7);

希望得到这样的查询结果

FID ParentName Addr ChildName Age ChildName Age ChildName Age
1 张三 中华大街 张小 10
2 李四 云海南路 李一 8 李二 12
3 王五 和平小巷 王龙 13 王虎 10 王凤 7

解决方法

数据库脚本

SELECT * FROM family
SELECT * FROM Children			   
DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(
FID INT NOT NULL,
ParentName VARCHAR(20) NOT NULL,
Addr VARCHAR(50),
ChildNames VARCHAR(8000)
)

DECLARE @FID INT,@ParentName VARCHAR(20),@Addr VARCHAR(50), @ChildName VARCHAR(20),@Age INT
DECLARE @COUNT BIT
DECLARE MY_CURSOR CURSOR FOR SELECT family.FID,family.ParentName,family.Addr,Children.ChildName,Children.Age FROM family LEFT JOIN Children ON family.FID = Children.FID
OPEN MY_CURSOR 
FETCH NEXT FROM MY_CURSOR INTO @FID ,@ParentName ,@Addr , @ChildName ,@Age
WHILE @@FETCH_STATUS =0
BEGIN
	SELECT @COUNT= COUNT(FID) FROM #TEMP WHERE FID = @FID
	IF @COUNT > 0
		BEGIN
			UPDATE #TEMP SET ChildNames = ChildNames +' '+@ChildName +' '+CONVERT(VARCHAR,@Age) WHERE  FID = @FID
		END
	ELSE
		INSERT INTO  #TEMP(FID,ParentName,Addr,ChildNames) VALUES (@FID,@ParentName,@Addr,@ChildName + ' '+CONVERT(VARCHAR,@Age))
	FETCH  NEXT  FROM MY_CURSOR INTO @FID ,@ParentName ,@Addr , @ChildName ,@Age
	PRINT 	 @FID PRINT @ParentName PRINT @Addr PRINT @ChildName PRINT @Age
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR 

SELECT * FROM #TEMP

 

运行结果