select a.dept_id, (select sum(person_id) from person b where b.dept_id=a.dept_id and sex=1), (select sum(person_id) from person b where b.dept_id=a.dept_id and sex=0) from person a
--改一下 select a.dept_id, (select count(person_id) from person b where b.dept_id=a.dept_id and sex=1), (select count(person_id) from person b where b.dept_id=a.dept_id and sex=0) from person a
select a.dept_id, sum(case when b.sex=1 then 1 else 0), sum(case when b.sex=0 then 1 else 0) from dept a left join person b on a.dept_id=b.dept_id group by a.dept_id order by a.dept_id
简单的方法: select dept_id,sex,count(*) from Person group by dept_id,sex order by dept_id这个结果是这样的结构: dept_id sex count(id) 1 男 4 1 女 3 2 女 4 3 男 6 3 女 5 ...............
select a.dept_id, (select count(person_id) from person b where b.dept_id=a.dept_id and sex=1), (select count(person_id) from person b where b.dept_id=a.dept_id and sex=0) from person a
sum(case when b.sex=1 then 1 else 0),
sum(case when b.sex=0 then 1 else 0)
from dept a
left join person b
on a.dept_id=b.dept_id
group by a.dept_id
order by a.dept_id
select dept_id,sex,count(*)
from Person
group by dept_id,sex
order by dept_id这个结果是这样的结构:
dept_id sex count(id)
1 男 4
1 女 3
2 女 4
3 男 6
3 女 5
...............