select AA,BB,CC
from(
select AA=case min(id) when (select id=min(id) from 教师表) then '学历' else '' end
,BB=xl,CC=count(*),s1=0
from 教师表
group by XL
union all
select AA=case min(id) when (select id=min(id) from 教师表) then '职务' else '' end
,BB=zw,CC=count(*),s1=1
from 教师表
group by zw
union all
select AA=case min(id) when (select id=min(id) from 教师表) then '职称' else '' end
,BB=zc,CC=count(*),s1=2
from 教师表
group by zc
)a order by s1,AA desc
--那就这样
--查询
select AA=case min(id) when (select id=min(id) from 教师表) then '学历' else '' end
,BB=xl,CC=count(*)
from 教师表
group by XL
union all
select AA=case min(id) when (select id=min(id) from 教师表) then '职务' else '' end
,BB=zw,CC=count(*)
from 教师表
group by zw
union all
select AA=case min(id) when (select id=min(id) from 教师表) then '职称' else '' end
,BB=zc,CC=count(*)
from 教师表
group by zc
--测试数据
create table 教师表(id int,xl varchar(10),zc varchar(10),zw varchar(10),rznj varchar(10))
insert 教师表 select 1,'本科' ,'高级','校长' ,'高中'
union all select 2,'研究生','高级','副校长','初中'
union all select 3,'专科' ,'中级','主任' ,'小学'
go
--查询
select AA=case min(id) when 1 then '学历' else '' end
,BB=xl,CC=count(*)
from 教师表
group by XL
union all
select AA=case min(id) when 1 then '职务' else '' end
,BB=zw,CC=count(*)
from 教师表
group by zw
union all
select AA=case min(id) when 1 then '职称' else '' end
,BB=zc,CC=count(*)
from 教师表
group by zc
go
select AA=case min(id) when 1 then '学历' else '' end
,BB=xl,CC=count(*)
from 教师表
group by XL
union all
select AA=case min(id) when 1 then '职务' else '' end
,BB=zw,CC=count(*)
from 教师表
group by zw
union all
select AA=case min(id) when 1 then '职称' else '' end
,BB=zc,CC=count(*)
from 教师表
group by zc
select AA=case id when 1 then '学历' else '' end
,BB=xl,CC=count(*)
from 教师表
group by XL
union all
select AA=case id when 1 then '职务' else '' end
,BB=zw,CC=count(*)
from 教师表
group by XL
union all
select AA=case id when 1 then '职称' else '' end
,BB=zc,CC=count(*)
from 教师表
group by XL