高分求一查询语句(不够再加）

[yanm7788]

现在表里的数据如下格式：

上面为现有格式，下面为想得到的格试。
比如说研究生，在Class1列中只有一列数据，所以得到为1，在Class2列中有3列数据所以为3.
请各位高手帮帮忙，感激不尽。
PS:对CSDN排版无语了。

[yaj52125]

?

[dawugui]

select m.EduLevel,
       Class1 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class1<>'' and Class1 is not null)),0),
       Class2 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class2<>'' and Class2 is not null)),0),
       Class3 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class3<>'' and Class3 is not null)),0),
       Class4 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class4<>'' and Class4 is not null)),0),
       Class5 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class5<>'' and Class5 is not null)),0),
       min(m.CMCode)
from tb m 
group by m.EduLevel

select m.EduLevel,
       Class1 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class1<>'' and Class1 is not null)),0),
       Class2 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class2<>'' and Class2 is not null)),0),
       Class3 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class3<>'' and Class3 is not null)),0),
       Class4 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class4<>'' and Class4 is not null)),0),
       Class5 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class5<>'' and Class5 is not null)),0),
       m.CMCode
from tb m 
group by m.EduLevel,m.CMCode

[yanm7788]

[Quote=引用 1 楼 yaj52125 的回复:]
?
[/Quote]
没什么，在编缉器里数据格式正确，发布出来就乱了
没办法只有上图

高手快来帮帮忙啊！

[yanm7788]

呵呵，速度真快

[华夏小卒]

大乌龟写了，不写了

[dawugui]

create table tb(EduLevel varchar(20), Class1 varchar(20),Class2 varchar(20),Class3 varchar(20),Class4 varchar(20),Class5 varchar(20),CMCode varchar(20))
insert into tb values('研究生' , '1,3', '2,3', '3,4','','', '330702001001')
insert into tb values('大学本科' , '','','','','','330702001001') 
insert into tb values('博士研究生毕业', '','','','','','330702001001') 
insert into tb values('未知' , '1,2,3,4,5', '1,5', '2,6', '1,5','', '330702001001') 
insert into tb values('未知' , '','','4', '2,3,5','', '330702001001') 
insert into tb values('相当初中毕业' , '1,2,3', '2','', '3,7','', '330702001001') 
insert into tb values('研究生' , '','','','','','330702001001') 
insert into tb values('研究生' , '','1,2', '2,6','','', '330702001001') 
insert into tb values('研究生' , '','2,3','','','', '330702001001') 
go

select m.EduLevel,
       Class1 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class1<>'' and Class1 is not null)),0),
       Class2 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class2<>'' and Class2 is not null)),0),
       Class3 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class3<>'' and Class3 is not null)),0),
       Class4 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class4<>'' and Class4 is not null)),0),
       Class5 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class5<>'' and Class5 is not null)),0),
       min(m.CMCode)
from tb m 
group by m.EduLevel

select m.EduLevel,
       Class1 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class1<>'' and Class1 is not null)),0),
       Class2 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class2<>'' and Class2 is not null)),0),
       Class3 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class3<>'' and Class3 is not null)),0),
       Class4 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class4<>'' and Class4 is not null)),0),
       Class5 = isnull((select count(1) from tb where EduLevel = m.EduLevel and (Class5<>'' and Class5 is not null)),0),
       m.CMCode
from tb m 
group by m.EduLevel,m.CMCode

drop table tb

/*
EduLevel             Class1      Class2      Class3      Class4      Class5                           
-------------------- ----------- ----------- ----------- ----------- ----------- -------------------- 
博士研究生毕业              0           0           0           0           0           330702001001
大学本科                 0           0           0           0           0           330702001001
未知                   1           1           2           2           0           330702001001
相当初中毕业               1           1           0           1           0           330702001001
研究生                  1           3           2           0           0           330702001001

（所影响的行数为 5 行）

EduLevel             Class1      Class2      Class3      Class4      Class5      CMCode               
-------------------- ----------- ----------- ----------- ----------- ----------- -------------------- 
博士研究生毕业              0           0           0           0           0           330702001001
大学本科                 0           0           0           0           0           330702001001
未知                   1           1           2           2           0           330702001001
相当初中毕业               1           1           0           1           0           330702001001
研究生                  1           3           2           0           0           330702001001

（所影响的行数为 5 行）

*/