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.EduLevelselect 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
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') goselect 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.EduLevelselect 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.CMCodedrop 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 行)*/
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.EduLevelselect 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
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')
goselect 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.EduLevelselect 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.CMCodedrop 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 行)*/