有表:
ID NAME DEPARTMENT SEX
0 Tom HR male
1 Jim SALE male
2 Nancy HR female
3 Amy HR female现在我想得到下表:
DEPARTMENT MALE FEMALE
HR 1 2
SALE 1 0应该怎么做呢?谢谢
ID NAME DEPARTMENT SEX
0 Tom HR male
1 Jim SALE male
2 Nancy HR female
3 Amy HR female现在我想得到下表:
DEPARTMENT MALE FEMALE
HR 1 2
SALE 1 0应该怎么做呢?谢谢
sum(case when sex='male' then 1 else 0 end) male,
sum(case when sex='male' then 0 else 1 end) female
from tb
group by department
sum(case when sex='female' then 1 else 0 end) as female
from tb
group by department
sum(case when sex='male' then 1 else 0 end) male,
sum(case when sex='female' then 1 else 0 end) female
from table
group by department
这句不是很懂,能慢慢解释吗?我的基础很差,包涵包涵~
select department, case when sex='male' then 1 else 0 end as male,
case when sex='female' then 1 else 0 end as female
from tb再理解下面的代码就容易些:select department, sum(case when sex='male' then 1 else 0 end) as male,
sum(case when sex='female' then 1 else 0 end) as female
from tb
group by department
select c.department,sum(c.female) as female,sum(c.male) male from (select department ,female=(case when sex='female' then count(*) else 0 end),male=(case when sex='male' then count(*) else 0 end) from tb group by sex,department) c group by c.department
SET @s='select a.department'
SELECT @s=@s
+','+QUOTENAME(c.sex)
+N'=SUM(CASE sex WHEN '+QUOTENAME(sex,N'''')
+N' THEN num ELSE 0 END)'
FROM (select department,sex,count(*) as num from tb group by department,sex) c group by c.sex
exec( @s+N' from (select department,sex,count(*) as num from tb group by department,sex) a group by a.department')