求一sql语句,谢谢!!!!!!有分!!!!!!!!

[haoxiangni]

编号 姓名 性别 系别
1 aa 男 计
2 bb 女 数
3 cc 男 计
4 dd 男 数


结果

系别 男生总数 女生总数 所有总数
计 2 0 2
数 1 1 2

请问CSDN上的达人们,怎么样写sql语句才能出现结果表中的数据呢??

[吐司vivi]

顶一下

[icedut]

select 系别,sum(case when 性别 ='男' then 1 else 0 end ) as 男,
sum(case when 性别 ='女' then 1 else 0 end )as 女,count(*) as 所有总数
from
(
select 'aa' as 姓名, '男' as 性别, '计' as 系别 from dual
union
select 'bb' , '女' , '数' from dual
union
select 'cc' , '男' , '计' from dual
union
select 'dd' , '男' , '数' from dual
)
group by 系别

[jiazheng]

select t.xb,t.mencount,v.womencount,t.mencount+v.womencount from
(select xb,count(1) as mencount from table where sex='男' group by xb ) t
(select xb,count(1) as womencount from table where sex='女' group by xb ) v
where t.xb=v.xb

[daydayup0323]

select 系别,sum(decode(sex,'男',1,0)),sum(decode(sex,'女',1,0)),count(*)
from talbe
group by 系别

[chensheng913]

SELECT 系别,
SUM(CASE WHEN 性别='男' THEN 1 ELSE 0 END) AS 男生总数,
SUM(CASE WHEN 性别='女' THEN 1 ELSE 0 END) AS 女生总数,
COUNT(*) AS 所有总数
FROM table
GROUP BY 系别