关于分组统计问题

Question

a表：
employee_id,name,sex
1,甲,男
2,乙,男
3,丙,男
4,丁,女
b表：
training_id,employee_id,training_date
1，1，2005-04-03
2，1，2005-05-03
3，2，2005-04-19
4，3，2005-06-20
5，2，2005-08-01
6，4，2005-08-18
7，4，2005-08-30现在想统计出一下结果
月    份，培训总人数，男，女
2005-04，2，2，0
2005-05，1，1，0
2005-06，1，1，0
2005-08，2，1，1
请问sql语句怎样编写？

Answer 1

select to_char(b.training_date,'yyyy-mm') as "月份",
       count(*) as "培训总人数",
       sum(decode(a.sex,'男',1,0)) as "男",
       sum(decode(a.sex,'女',1,0)) as "女"
from a,b where a.employee_id = b.employee_id
group by to_char(b.training_date,'yyyy-mm')

Answer 2

select t_date,all_cnt,man_cnt,all_cnt-man_cnt woman_cnt
from
(select trunc(training_date,'mm') t_date,count(*) all_cnt
from b
group by trunc(training_date,'mm')) aa,
(select trunc(training_date,'mm') t_date,a.sex,count(*) man_cnt
from b,a
where a.employee_id=b.employee_id and a.sex='男'
group by trunc(training_date,'mm'),a.sex) bb
where aa.t_date=bb.t_date;

Answer 3

漏了点，原来4在 8月份培训了2次，稍微改下
select to_char(b.training_date,'yyyy-mm') as "月份",
       count(distinct training_id) as "培训总人数",
       count(distinct decode(a.sex,'男',training_id)) as "男",
       count(distinct decode(a.sex,'女',training_id)) as "女"
from a,b where a.employee_id = b.employee_id
group by to_char(b.training_date,'yyyy-mm')

Answer 4

select x.yue as 月份,x.p_num as 培训总人数,
 y.nan_num as 男,y.nu_num as 女
from 
  (select 
   substr(training_date,1,7) as yue, count(*) as p_num
  from b表
  group by substr(training_date,1,7)) x,  
  (select substr(training_date,1,7) as yue,
      sum(decode(y.employee_id,'男',sex_num,0) as nan_num,
      sum(decode(y.employee_id,'女',sex_num,0) as nu_num
   from
    (select  b.training_date,a.sex as employee_id 
     from b表 b,a表 a
     where b.employee_id=a.employee_id)
   group by substr(training_date,1,7)) y
where x.yue=y.yue

Answer 5

上面弄错了count里面的应该是training_id：SQL> select * from a;EMPLOYEE_ID NAME                 SEX
----------- -------------------- ---
          1 甲                   男
          2 乙                   男
          3 丙                   男
          4 丁                   女SQL> select * from b;TRAINING_ID EMPLOYEE_ID TRAINING_DATE
----------- ----------- -------------
          1           1 2005-4-3
          2           1 2005-5-3
          3           2 2005-4-19
          4           3 2005-6-20
          5           2 2005-8-1
          6           4 2005-8-18
          7           4 2005-8-307 rows selectedselect to_char(b.training_date,'yyyy-mm') as "月份",
       count(distinct b.employee_id) as "培训总人数",
       count(distinct decode(a.sex,'男',b.employee_id)) as "男",
       count(distinct decode(a.sex,'女',b.employee_id)) as "女"
from a,b where a.employee_id = b.employee_id
group by to_char(b.training_date,'yyyy-mm')SQL> 
  7  /月份    培训总人数         男         女
------- ---------- ---------- ----------
2005-04          2          2          0
2005-05          1          1          0
2005-06          1          1          0
2005-08          2          1          1

Answer 6

union all 一下不就行了

Answer 7

用union all可以，不过用rollup效率更高些select to_char(b.training_date,'yyyy-mm') as "月份",
       count(distinct b.employee_id) as "培训总人数",
       count(distinct decode(a.sex,'男',b.employee_id)) as "男",
       count(distinct decode(a.sex,'女',b.employee_id)) as "女"
from a,b where a.employee_id = b.employee_id
group by rollup(to_char(b.training_date,'yyyy-mm'))SQL> 
  7  /月份    培训总人数         男         女
------- ---------- ---------- ----------
2005-04          2          2          0
2005-05          1          1          0
2005-06          1          1          0
2005-08          2          1          1
                 4          3          1
这里有个问题，为什么你上面统计的总人数是6个，不是总共才4个人吗？如果非要达到你上面的效果只能用union all 了

Answer 8

简单的union all还不行的。楼主的数据逻辑是：同一个人在不同的月份培训要算不同的次数，在同一个月份培训只算一次。

Answer 9

这样还可以：
select to_char(b.training_date,'yyyy-mm') as "月份",
       count(distinct b.employee_id) as "培训总人数",
       count(distinct decode(a.sex,'男',b.employee_id)) as "男",
       count(distinct decode(a.sex,'女',b.employee_id)) as "女"
from a,b where a.employee_id = b.employee_id
group by to_char(b.training_date,'yyyy-mm')
union
select '',sum(培训总人数),sum(男),sum(女) from
(
select to_char(b.training_date,'yyyy-mm') as "月份",
       count(distinct b.employee_id) as "培训总人数",
       count(distinct decode(a.sex,'男',b.employee_id)) as "男",
       count(distinct decode(a.sex,'女',b.employee_id)) as "女"
from a,b where a.employee_id = b.employee_id
group by to_char(b.training_date,'yyyy-mm')
)

Answer 10

同一个人在不同的月份培训要算不同的次数，在同一个月份培训只算一次。
-------------------------------------------------------------------
用rollup还是有办法的：
select to_char(b.training_date,'yyyy-mm') as "月份",
       count(distinct b.employee_id || to_char(b.training_date,'yyyy-mm')) as "培训总人数",
       count(distinct decode(a.sex,'男',b.employee_id || to_char(b.training_date,'yyyy-mm'))) as "男",
       count(distinct decode(a.sex,'女',b.employee_id || to_char(b.training_date,'yyyy-mm'))) as "女"
from a,b where a.employee_id = b.employee_id
group by rollup(to_char(b.training_date,'yyyy-mm'))SQL> 
  7  /月份    培训总人数         男         女
------- ---------- ---------- ----------
2005-04          2          2          0
2005-05          1          1          0
2005-06          1          1          0
2005-08          2          1          1
                 6          5          1

Answer 11

首先谢谢以上各位。
至于培训的次数，因为培训是按次数的，无论是同一个人培训的还是不同人培训都要算上。
不过想请问一下duanzilin(寻)，用rollup是不是比union 效率高？

Answer 12

当然rollup要比union 的效率高多了，你可以试一下，用union要进行2次select 表操作，而且2次都要用到count统计，速度自然就慢多了，而rollup只要一次就行了
下面是用union all的执行计划
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     UNION-ALL
   3    2       SORT (GROUP BY)
   4    3         MERGE JOIN
   5    4           SORT (JOIN)
   6    5             TABLE ACCESS (FULL) OF 'B'
   7    4           SORT (JOIN)
   8    7             TABLE ACCESS (FULL) OF 'A'
   9    2       SORT (AGGREGATE)
  10    9         VIEW
  11   10           SORT (GROUP BY)
  12   11             MERGE JOIN
  13   12               SORT (JOIN)
  14   13                 TABLE ACCESS (FULL) OF 'B'
  15   12               SORT (JOIN)
  16   15                 TABLE ACCESS (FULL) OF 'A'Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        633  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          5  rows processed这个是用rollup的执行计划，可以比较一下
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY ROLLUP)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'B'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'A'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        633  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          5  rows processed