表合并并统计的问题

昨天帮一个同学解决表合并的 问题 整到半夜 失败了 因为sql 语句的 left join 实在忘了 并且和union 弄混了

一会再复习下：

先说表结构和要求

表

-------------------------------------------------
 table1 
-------------------------------------------------
id  department    response
1       bm1                y
2       bm1                y
3       bm1                y

4       bm2                n

5       bm2                n

6       bm3                y

7       bm3                y

8       bm3                y

9       bm3                n

10     bm3                n

11     bm4                n

12     bm5                y

-------------------------------------------------

最终的结果要求是这样的

  department        al          yi      wei

       bm1                3          3        0

       bm2                2          0        2

       bm3                5          3        2

       bm4                1          0        1

       bm5                1          1        0

-------------------------------------------------

 

实现语句

 

select a.department,a.al, (case  when b.yi is null then  '0' else b.yi end) as yi, (case  when c.wei is null then  '0' else c.wei end) as wei from
 (
   (
    (select department ,count(response) as al from per group by department) a left join
    (select department,count(response) as yi from per where response='y' group by department) b on a.department=b.department
   ) left join
  (select department,count(response) as wei from per where response!='y' group by department) c on a.department=c.department)

 

当我还在洋洋得意的时候 同学整出来一个更牛的，惭愧惭愧 人外有人啊

select department,count(*) as 信息总数,count(case response when 'y' then 1 else null end) as 未处理,count(case response when 'y' then null else 1 end) as 已处理 from per group by department