select c.classname, sum(case when a.groupid=1 then 1 else 0 end) as 洪兴, .. from students as a inner join StudentGroup as b on a.GroupID=b.ID inner join Class as c on a.ClassID=c.ID group by c.classname
sum(case when a.groupid=2 then 1 else 0 end) as 东兴, sum(case when a.groupid=3 then 1 else 0 end) ...
declare @class table(id int identity,className nvarchar(20)) declare @student table(id int identity,sname nvarchar(20),cid int,gid int) declare @group table(id int identity,gname nvarchar(20)) insert into @class(className) values('班级1'),('班级2'),('班级5') insert into @group(gname) values('洪兴'),('东兴'),('中兴'),('华为'),('三星') insert into @student(sname,cid,gid) values('张三',1,1),('李斯',1,1),('李四',1,1),('王五',1,1),('赵六',1,2),('赵一',1,2),('赵二',1,5),('赵三',1,5),('赵四',2,1),('赵七',3,2) declare @tb table (className nvarchar(20),GroupName nvarchar(20),cnt int) insert into @tb select a.className,b.gname,(select count(*) from @student where cid=a.id and gid=b.id) as cnt from @class a,@group b order by a.id declare @cn nvarchar(20),@gn nvarchar(20),@cnt int,@sql nvarchar(max),@ccn nvarchar(20) declare tb cursor for select className,groupName,cnt from @tb open tb set @ccn = '' set @sql = 'select ' fetch next from tb into @cn,@gn,@cnt while @@FETCH_STATUS=0 begin if @ccn<>@cn begin if @ccn<>'' begin set @sql = @sql + ' union select ' end set @ccn = @cn set @sql = @sql + '''' + @cn + ''' as 班级' end set @sql = @sql + ',' + CONVERT(nvarchar,@cnt) + ' as ' + @gn fetch next from tb into @cn,@gn,@cnt end close tb deallocate tb --print(@sql) exec(@sql)
declare @class table(id int identity,className nvarchar(20)) declare @student table(id int identity,sname nvarchar(20),cid int,gid int) declare @group table(id int identity,gname nvarchar(20)) insert into @class(className) values('班级1'),('班级2'),('班级5') insert into @group(gname) values('洪兴'),('东兴'),('中兴'),('华为'),('三星') insert into @student(sname,cid,gid) values('张三',1,1),('李斯',1,1),('李四',1,1),('王五',1,1),('赵六',1,2),('赵一',1,2),('赵二',1,5),('赵三',1,5),('赵四',2,1),('赵七',3,2) select * ,(select count(*) from @student where cid=a.id and gid=1) as 洪兴 ,(select count(*) from @student where cid=a.id and gid=2) as 东兴 ,(select count(*) from @student where cid=a.id and gid=3) as 中兴 ,(select count(*) from @student where cid=a.id and gid=4) as 华为 ,(select count(*) from @student where cid=a.id and gid=5) as 三星 from @class a一句Sql指令的话,那就只能给固定的值了
c.classname,
sum(case when a.groupid=1 then 1 else 0 end) as 洪兴,
..
from
students as a inner join StudentGroup as b on a.GroupID=b.ID
inner join Class as c on a.ClassID=c.ID
group by
c.classname
sum(case when a.groupid=3 then 1 else 0 end)
...
declare @student table(id int identity,sname nvarchar(20),cid int,gid int)
declare @group table(id int identity,gname nvarchar(20))
insert into @class(className) values('班级1'),('班级2'),('班级5')
insert into @group(gname) values('洪兴'),('东兴'),('中兴'),('华为'),('三星')
insert into @student(sname,cid,gid) values('张三',1,1),('李斯',1,1),('李四',1,1),('王五',1,1),('赵六',1,2),('赵一',1,2),('赵二',1,5),('赵三',1,5),('赵四',2,1),('赵七',3,2)
declare @tb table (className nvarchar(20),GroupName nvarchar(20),cnt int)
insert into @tb select a.className,b.gname,(select count(*) from @student where cid=a.id and gid=b.id) as cnt from @class a,@group b order by a.id
declare @cn nvarchar(20),@gn nvarchar(20),@cnt int,@sql nvarchar(max),@ccn nvarchar(20)
declare tb cursor for select className,groupName,cnt from @tb
open tb
set @ccn = ''
set @sql = 'select '
fetch next from tb into @cn,@gn,@cnt
while @@FETCH_STATUS=0
begin
if @ccn<>@cn
begin
if @ccn<>''
begin
set @sql = @sql + ' union select '
end
set @ccn = @cn
set @sql = @sql + '''' + @cn + ''' as 班级'
end
set @sql = @sql + ',' + CONVERT(nvarchar,@cnt) + ' as ' + @gn
fetch next from tb into @cn,@gn,@cnt
end
close tb
deallocate tb
--print(@sql)
exec(@sql)
declare @student table(id int identity,sname nvarchar(20),cid int,gid int)
declare @group table(id int identity,gname nvarchar(20))
insert into @class(className) values('班级1'),('班级2'),('班级5')
insert into @group(gname) values('洪兴'),('东兴'),('中兴'),('华为'),('三星')
insert into @student(sname,cid,gid) values('张三',1,1),('李斯',1,1),('李四',1,1),('王五',1,1),('赵六',1,2),('赵一',1,2),('赵二',1,5),('赵三',1,5),('赵四',2,1),('赵七',3,2)
select *
,(select count(*) from @student where cid=a.id and gid=1) as 洪兴
,(select count(*) from @student where cid=a.id and gid=2) as 东兴
,(select count(*) from @student where cid=a.id and gid=3) as 中兴
,(select count(*) from @student where cid=a.id and gid=4) as 华为
,(select count(*) from @student where cid=a.id and gid=5) as 三星
from @class a一句Sql指令的话,那就只能给固定的值了