select distinct bj,ns = (select count(1) from ta where bj = a.bj and xb ='ns'),nvs=(select count(1) from ta where bj = a.bj and xb ='nvs') from ta a
select 班級,男生=sum(case when 性別='男' then 1 else 0 end), 女生=sum(case when 性別='女' then 1 else 0 end) from tb group by 班級
select 班级,sum(姓名) from table where sex='男' group by 班级 union all select sum(姓名) from table where 班级,sex='女' group by 班级 union all select 班级,sum(姓名) from table group by 班级
select 班級,男生=sum(case when 性別='男' then 1 else 0 end), 女生=sum(case when 性別='女' then 1 else 0 end), 总数=count(1) from tb group by 班級
select 班级, 男生比例=ltrim(cast(sum(case when 性別='男' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%', 女生比例=ltrim(cast(sum(case when 性別='女' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' from tb group by 班级
create table #tb (學生姓名 nvarchar(10),性別 nvarchar(10),班級 int) insert #tb select 'A','男',1 union all select 'B','男',1 union all select 'C','男',2 union all select 'D','女',1 union all select 'E','女',2 union all select 'F','女',2 union all select 'G','男',1 union all select 'H','男',2 union all select 'I','男',2 union all select 'J','男',2 union all select 'K','男',2 union all select 'L','男',3 union all select 'M','女',2 union all select 'N','女',3 select 班級, 男生比例=Ltrim(cast((sum(case 性別 when '男' then 1 else 0 end)*100.0)/sum(1) as dec(10,2)))+'%', 女生比例=Ltrim(cast((sum(case 性別 when '女' then 1 else 0 end)*100.0)/sum(1) as dec(10,2)))+'%' from #tb group by 班級
select 班级, ltrim(cast(sum(case when 性別='男' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 男生比例, ltrim(cast(sum(case when 性別='女' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 女生比例 from tb group by 班级
select 班级, ltrim(cast(sum(case when 性別='男' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 男生比例, ltrim(cast(sum(case when 性別='女' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 女生比例 from tb group by 班级
女生=sum(case when 性別='女' then 1 else 0 end)
from tb group by 班級
select 班级,sum(姓名)
from table
where sex='男'
group by 班级
union all
select sum(姓名)
from table
where 班级,sex='女'
group by 班级
union all
select 班级,sum(姓名)
from table
group by 班级
女生=sum(case when 性別='女' then 1 else 0 end),
总数=count(1)
from tb group by 班級
班级,
男生比例=ltrim(cast(sum(case when 性別='男' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%',
女生比例=ltrim(cast(sum(case when 性別='女' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%'
from tb
group by 班级
create table #tb (學生姓名 nvarchar(10),性別 nvarchar(10),班級 int)
insert #tb
select 'A','男',1 union all
select 'B','男',1 union all
select 'C','男',2 union all
select 'D','女',1 union all
select 'E','女',2 union all
select 'F','女',2 union all
select 'G','男',1 union all
select 'H','男',2 union all
select 'I','男',2 union all
select 'J','男',2 union all
select 'K','男',2 union all
select 'L','男',3 union all
select 'M','女',2 union all
select 'N','女',3 select 班級,
男生比例=Ltrim(cast((sum(case 性別 when '男' then 1 else 0 end)*100.0)/sum(1) as dec(10,2)))+'%',
女生比例=Ltrim(cast((sum(case 性別 when '女' then 1 else 0 end)*100.0)/sum(1) as dec(10,2)))+'%'
from #tb group by 班級
班级,
ltrim(cast(sum(case when 性別='男' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 男生比例,
ltrim(cast(sum(case when 性別='女' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 女生比例
from
tb
group by
班级
select
班级,
ltrim(cast(sum(case when 性別='男' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 男生比例,
ltrim(cast(sum(case when 性別='女' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%' as 女生比例
from
tb
group by
班级