DECLARE @t TABLE (ID int identity,Name varchar(50),Class varchar(50),[Values] int)
INSERT @t SELECT 'AA','A',2
UNION ALL SELECT 'BB','B',1
UNION ALL SELECT 'CC','D',1
UNION ALL SELECT 'AA','A',3
UNION ALL SELECT 'BC','C',5
UNION ALL SELECT 'BB','S',7
UNION ALL SELECT 'BD','V',8
UNION ALL SELECT 'BB','W',9
UNION ALL SELECT 'BE','R',6
UNION ALL SELECT 'BE','R',5
UNION ALL SELECT 'BE','W',9
UNION ALL SELECT 'BB','R',2
UNION ALL SELECT 'BF','W',1
UNION ALL SELECT 'BF','R',7
UNION ALL SELECT 'BG','Q',7/*SELECT * FROM @t ORDER BY NAME
ID Name Class Values
1 AA A 2
4 AA A 3
2 BB B 1
8 BB W 9
12 BB R 2
6 BB S 7
5 BC C 5
7 BD V 8
9 BE R 6
10 BE R 5
11 BE W 9
13 BF W 1
14 BF R 7
15 BG Q 7
3 CC D 1
*/
DECLARE
@Name VARCHAR(20),@Class VARCHAR(50),@Values INT,
@PName VARCHAR(20),@PClass VARCHAR(50),@PValues INT;DECLARE @t1 TABLE(Name varchar(20),Class varchar(50),[Values] int);DECLARE C CURSOR FOR
SELECT Name,Class,[Values] FROM @t ORDER BY NAME;OPEN C;FETCH NEXT FROM C INTO @Name,@Class,@Values;
SELECT @PName=@Name,@PClass ='',@PValues=0;WHILE @@fetch_status = 0
BEGIN
IF @Name <> @PName
BEGIN
INSERT INTO @t1 VALUES(@PName,STUFF(@PClass,1,1,''),@PValues);
SELECT @PName=@Name,@PClass ='',@PValues=0;
END SELECT @PClass=@PClass+'、'+@Class,@PValues=@PValues+@Values; FETCH NEXT FROM C INTO @Name,@Class,@Values;
ENDCLOSE CDEALLOCATE CSELECT * FROM @t1;/*
Name Class Values
-------------------- -------------------------------------------------- -----------
AA A、A 5
BB B、W、R、S 19
BC C 5
BD V 8
BE R、R、W 20
BF W、R 8
BG Q 7(7 行受影响)
*/我正在学用游标,请问这里有一行显示不出来,请问要哪里调一下?
INSERT @t SELECT 'AA','A',2
UNION ALL SELECT 'BB','B',1
UNION ALL SELECT 'CC','D',1
UNION ALL SELECT 'AA','A',3
UNION ALL SELECT 'BC','C',5
UNION ALL SELECT 'BB','S',7
UNION ALL SELECT 'BD','V',8
UNION ALL SELECT 'BB','W',9
UNION ALL SELECT 'BE','R',6
UNION ALL SELECT 'BE','R',5
UNION ALL SELECT 'BE','W',9
UNION ALL SELECT 'BB','R',2
UNION ALL SELECT 'BF','W',1
UNION ALL SELECT 'BF','R',7
UNION ALL SELECT 'BG','Q',7/*SELECT * FROM @t ORDER BY NAME
ID Name Class Values
1 AA A 2
4 AA A 3
2 BB B 1
8 BB W 9
12 BB R 2
6 BB S 7
5 BC C 5
7 BD V 8
9 BE R 6
10 BE R 5
11 BE W 9
13 BF W 1
14 BF R 7
15 BG Q 7
3 CC D 1
*/
DECLARE
@Name VARCHAR(20),@Class VARCHAR(50),@Values INT,
@PName VARCHAR(20),@PClass VARCHAR(50),@PValues INT;DECLARE @t1 TABLE(Name varchar(20),Class varchar(50),[Values] int);DECLARE C CURSOR FOR
SELECT Name,Class,[Values] FROM @t ORDER BY NAME;OPEN C;FETCH NEXT FROM C INTO @Name,@Class,@Values;
SELECT @PName=@Name,@PClass ='',@PValues=0;WHILE @@fetch_status = 0
BEGIN
IF @Name <> @PName
BEGIN
INSERT INTO @t1 VALUES(@PName,STUFF(@PClass,1,1,''),@PValues);
SELECT @PName=@Name,@PClass ='',@PValues=0;
END SELECT @PClass=@PClass+'、'+@Class,@PValues=@PValues+@Values; FETCH NEXT FROM C INTO @Name,@Class,@Values;
ENDCLOSE CDEALLOCATE CSELECT * FROM @t1;/*
Name Class Values
-------------------- -------------------------------------------------- -----------
AA A、A 5
BB B、W、R、S 19
BC C 5
BD V 8
BE R、R、W 20
BF W、R 8
BG Q 7(7 行受影响)
*/我正在学用游标,请问这里有一行显示不出来,请问要哪里调一下?
-------------------
这一行没有插到表里
INSERT @t SELECT 'AA','A',2
UNION ALL SELECT 'BB','B',1
UNION ALL SELECT 'CC','D',1
UNION ALL SELECT 'AA','A',3
UNION ALL SELECT 'BC','C',5
UNION ALL SELECT 'BB','S',7
UNION ALL SELECT 'BD','V',8
UNION ALL SELECT 'BB','W',9
UNION ALL SELECT 'BE','R',6
UNION ALL SELECT 'BE','R',5
UNION ALL SELECT 'BE','W',9
UNION ALL SELECT 'BB','R',2
UNION ALL SELECT 'BF','W',1
UNION ALL SELECT 'BF','R',7
UNION ALL SELECT 'BG','Q',7
--UNION ALL SELECT 'cc','Q',7
/*SELECT * FROM @t ORDER BY NAME
ID Name Class Values
1 AA A 2
4 AA A 3
2 BB B 1
8 BB W 9
12 BB R 2
6 BB S 7
5 BC C 5
7 BD V 8
9 BE R 6
10 BE R 5
11 BE W 9
13 BF W 1
14 BF R 7
15 BG Q 7
3 CC D 1
*/
DECLARE
@Name VARCHAR(20),@Class VARCHAR(50),@Values INT,
@PName VARCHAR(20),@PClass VARCHAR(50),@PValues INT;DECLARE @t1 TABLE(Name varchar(20),Class varchar(50),[Values] int);DECLARE C CURSOR FOR
SELECT Name,Class,[Values] FROM @t ORDER BY NAME;OPEN C;FETCH NEXT FROM C INTO @Name,@Class,@Values;
SELECT @PName=''WHILE @@fetch_status = 0
BEGIN IF @Name <> @PName
BEGIN
INSERT INTO @t1 VALUES(@Name,@Class,@Values);
SELECT @PName=@Name;
END
else
update @t1
set class = class+'、'+@Class,[Values]=[Values]+@Values
where [name] = @name; --SELECT @PClass=@PClass+'、'+@Class,@PValues=@PValues+@Values; FETCH NEXT FROM C INTO @Name,@Class,@Values;
ENDCLOSE CDEALLOCATE CSELECT * FROM @t1;
Name Class Values
-------------------- -------------------------------------------------- -----------
AA A、A 5
BB B、W、R、S 19
BC C 5
BD V 8
BE R、R、W 20
BF W、R 8
BG Q 7
CC D 1
INSERT @t SELECT 'AA','A',2
UNION ALL SELECT 'BB','B',1
UNION ALL SELECT 'CC','D',1
UNION ALL SELECT 'AA','A',3
UNION ALL SELECT 'BC','C',5
UNION ALL SELECT 'BB','S',7
UNION ALL SELECT 'BD','V',8
UNION ALL SELECT 'BB','W',9
UNION ALL SELECT 'BE','R',6
UNION ALL SELECT 'BE','R',5
UNION ALL SELECT 'BE','W',9
UNION ALL SELECT 'BB','R',2
UNION ALL SELECT 'BF','W',1
UNION ALL SELECT 'BF','R',7
UNION ALL SELECT 'BG','Q',7
--UNION ALL SELECT 'cc','Q',7
/*SELECT * FROM @t ORDER BY NAME
ID Name Class Values
1 AA A 2
4 AA A 3
2 BB B 1
8 BB W 9
12 BB R 2
6 BB S 7
5 BC C 5
7 BD V 8
9 BE R 6
10 BE R 5
11 BE W 9
13 BF W 1
14 BF R 7
15 BG Q 7
3 CC D 1
*/
DECLARE
@Name VARCHAR(20),@Class VARCHAR(50),@Values INT,
@PName VARCHAR(20),@PClass VARCHAR(50),@PValues INT;DECLARE @t1 TABLE(Name varchar(20),Class varchar(50),[Values] int);DECLARE C CURSOR FOR
SELECT Name,Class,[Values] FROM @t ORDER BY NAME;OPEN C;FETCH NEXT FROM C INTO @Name,@Class,@Values;
SELECT @PName=@Name,@PClass ='',@PValues=0;WHILE @@fetch_status = 0
BEGIN
IF @Name <> @PName
BEGIN
INSERT INTO @t1 VALUES(@PName,STUFF(@PClass,1,1,''),@PValues);
SELECT @PName=@Name,@PClass ='',@PValues=0;
END SELECT @PClass=@PClass+'、'+@Class,@PValues=@PValues+@Values; FETCH NEXT FROM C INTO @Name,@Class,@Values;
END
----------------------------------------------------------------------
INSERT INTO @t1 VALUES(@PName,STUFF(@PClass,1,1,''),@PValues);
---------------------------------------------------------------------CLOSE CDEALLOCATE CSELECT * FROM @t1;