用sql实现。
根据红色数据得到蓝色数据。先谢谢大家了!
明天中午来结贴ID Name Age Grade
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
4 LLL 80 2
5 CCC 70 2
6 RRR 24 3
ID Name Age Grade
1
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
2
4 LLL 80 2
5 CCC 70 2
3
6 RRR 24 3
根据红色数据得到蓝色数据。先谢谢大家了!
明天中午来结贴ID Name Age Grade
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
4 LLL 80 2
5 CCC 70 2
6 RRR 24 3
ID Name Age Grade
1
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
2
4 LLL 80 2
5 CCC 70 2
3
6 RRR 24 3
insert into tb values('1', 'AAA', '25', '1')
insert into tb values('2', 'BBB', '26', '1')
insert into tb values('3', 'CCC', '35', '1')
insert into tb values('4', 'LLL', '80', '2')
insert into tb values('5', 'CCC', '70', '2')
insert into tb values('6', 'RRR', '24', '3')
goselect id,name,age,grade from
(
select id = grade , name = '' , age = '' , grade = '' , grade1 = grade from
(select distinct grade from tb) t
union all
select *,grade1 = grade from tb
) m
order by grade1,id
drop table tb/*
id name age grade
---------- ---------- ---------- ----------
1
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
2
4 LLL 80 2
5 CCC 70 2
3
6 RRR 24 3(所影响的行数为 9 行)
*/
insert #t select 1, 'AAA', 25, 1
insert #t select 2, 'BBB', 26, 1
insert #t select 3, 'CCC', 35, 1
insert #t select 4, 'LLL', 80, 2
insert #t select 5, 'CCC', 70, 2
insert #t select 6, 'RRR', 24, 3 create table #t2(ID NVARCHAR(10), Name nvarchar(8), Age nvarchar(10), Grade nvarchar(10))--查看结果
godeclare wuxi cursor for
select * from #t
declare @ID int,@Name nvarchar(8),@Age int,@Grade int,@GradeOld int
open wuxi
fetch next from wuxi into @ID ,@Name ,@Age ,@Grade
select @GradeOld=@Grade
while @@fetch_status=0
begin
if @GradeOld=@Grade
insert #t2 select @ID ,@Name ,@Age ,@Grade
else
begin
insert #t2 select '','','',''
insert #t2 select @ID ,@Name ,@Age ,@Grade
end
fetch next from wuxi into @ID ,@Name ,@Age ,@Grade
end
close wuxi
deallocate wuxigo
select * from #t2--drop table #t,#t2
ID Name Age Grade
---------- -------- ---------- ----------
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
4 LLL 80 2
5 CCC 70 2
6 RRR 24 3(所影响的行数为 9 行)
create table #t(ID int, Name nvarchar(8), Age int, Grade int)
insert #t select 1, 'AAA', 25, 1
insert #t select 2, 'BBB', 26, 1
insert #t select 3, 'CCC', 35, 1
insert #t select 4, 'LLL', 80, 2
insert #t select 5, 'CCC', 70, 2
insert #t select 6, 'RRR', 24, 3 create table #t2(ID NVARCHAR(10), Name nvarchar(8), Age nvarchar(10), Grade nvarchar(10))--查看结果
godeclare wuxi cursor for
select * from #t
declare @ID int,@Name nvarchar(8),@Age int,@Grade int,@GradeOld int
open wuxi
fetch next from wuxi into @ID ,@Name ,@Age ,@Grade
while @@fetch_status=0
begin
if @Grade=@GradeOld
insert #t2 select @ID ,@Name ,@Age ,@Grade
else
begin
insert #t2 select '','','',''
insert #t2 select @ID ,@Name ,@Age ,@Grade
end
select @GradeOld=@Grade
fetch next from wuxi into @ID ,@Name ,@Age ,@Grade
end
close wuxi
deallocate wuxigo
select * from #t2--drop table #t,#t2ID Name Age Grade
---------- -------- ---------- ----------
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
4 LLL 80 2
5 CCC 70 2
6 RRR 24 3(所影响的行数为 9 行)
declare @T table(ID int,Name varchar(3),Age int,Grade int)
insert @T
select 1,'AAA',25,1 union all
select 2,'BBB',26,1 union all
select 3,'CCC',35,1 union all
select 4,'LLL',80,2 union all
select 5,'CCC',70,2 union all
select 6,'RRR',24,3select
ID=isnull(a.ID,b.ID),
Name=isnull(a.Name,''),
Age=isnull(cast(a.Age as varchar),''),
Grade=isnull(cast(a.Grade as varchar),'')
from
@T a full join (select ID=Grade,Age=null,Grade from @T group by Grade)
b on a.ID=b.ID and a.Name=b.Age
order by isnull(a.Grade,b.Grade),isnull(a.ID,b.ID)/*
ID Name Age Grade
1
1 AAA 25 1
2 BBB 26 1
3 CCC 35 1
2
4 LLL 80 2
5 CCC 70 2
3
6 RRR 24 3
*/