求：Oracle统计分组语句(急人)

Question

表结构：
   日期             部门      是否在a刊发表(0：是，1：否)   是否在b刊发表(0：是，1：否)   
   2008-1-2         a                  0                          1
   2008-2-2        b                 1                          0 
   2008-3-2        c                0                          1
   2008-4-2        a                1                          1
统计条件为：
   日期区间 如： 2008-2-2 至 2008-3-2
统计结果为：
   按照部门字段分组
显示字段：   部门    是否在a刊物发表文章数量             是否在a刊物发表文章数量         是否在b刊发表文章数量             是否在b刊发表文章数量
                   （统计的是在指定时间段内            （统计的是2008-3月发表数量）    （统计的是在指定时间段内          （统计的是2008-3月发表数量）
                     发表数量2008-2-2 至 2008-3-2）   （为大时间为准，取月份）         发表数量2008-2-2 至 2008-3-2） （为大时间为准，取月份）
            ？部门    ？篇                                 ？篇                               ？篇                               ？篇

Answer 1

select aaa.部门，sum(aaa.是否在a刊发表),sum(aaa.是否在b刊发表)
from
(select 部门，是否在a刊发表,是否在b刊发表
from 表
where 日期<=to_date('2008-2-2','YYYY-MM-DD') and 日期>=to_date('2008-3-2','YYYY-MM-DD') aaa
gruop by aaa.部门

Answer 2

重新发一下！多谢各位关注：
表结构
日期            部门      是否在a刊发表(0：是，1：否)  是否在b刊发表(0：是，1：否)  
  2008-1-2        a                0                          1 
  2008-2-2        b                1                          0 
  2008-3-2        c                0                          1 
  2008-4-2        a                1                          1 
统计条件为： 
  日期区间 如： 2008-2-2 至 2008-3-2 
统计结果
  1、部门  2、是否在a刊物发表文章数量   3、是否在a刊物发表文章数量    4、是否在b刊发表文章数量    5、是否在b刊发表文章数量 需要解释的是： 统计出的字段有五个 
1、部门 2、是否在a刊物发表文章数量 3、是否在a刊物发表文章数量 4、是否在b刊发表文章数量 5、是否在b刊发表文章数量 
如日期：2008-2-2 至 2008-3-2 
2、4字段取的是这个时间段的数量 
3、5字段取的是 2008-3月的数据（去时间大的那个月）

Answer 3

WITH a AS
     (SELECT TO_DATE ('2008-1-2', 'yyyy-mm-dd') days, 'a' dept, 0 a, 1 b
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-2-2', 'yyyy-mm-dd'), 'b', 1, 0
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-3-2', 'yyyy-mm-dd'), 'c', 0, 1
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-4-2', 'yyyy-mm-dd'), 'a', 1, 1
        FROM DUAL)
SELECT aaa.dept, aaa.tot_a, aaa.mon_a_num, aaa.tot_b, aaa.mon_b_num
  FROM (SELECT aa.*, SUM (mon_a_num) OVER (PARTITION BY aa.dept) tot_a,
               SUM (mon_b_num) OVER (PARTITION BY aa.dept) tot_b,
               ROW_NUMBER () OVER (PARTITION BY aa.dept ORDER BY aa.months)
                                                                           rn
          FROM (SELECT   a.dept, TO_CHAR (days, 'YYYYMM') months,
                         SUM (a.a) mon_a_num, SUM (a.b) mon_b_num
                    FROM a
                   WHERE a.days BETWEEN TRUNC (SYSDATE, 'yyyy')
                                    AND TRUNC (SYSDATE) - 60
                GROUP BY a.dept, TO_CHAR (days, 'YYYYMM')) aa) aaa
 WHERE aaa.rn = 1结果
Row# DEPT TOT_A MON_A_NUM TOT_B MON_B_NUM1 a 1 0 2 1
2 b 1 1 0 0
3 c 0 0 1 1

Answer 4

测试数据
WITH oa_inforelease AS
     (SELECT TO_DATE ('2008-1-2', 'yyyy-mm-dd') fdate, 'a' fdepartment,
             0 femittorf, 1 fhomepagetorf
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-2-2', 'yyyy-mm-dd'), 'b', 1, 0
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-2-2', 'yyyy-mm-dd'), 'b', 0, 1
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-1-2', 'yyyy-mm-dd'), 'b', 1, 0
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-3-2', 'yyyy-mm-dd'), 'c', 0, 1
        FROM DUAL
      UNION ALL
      SELECT TO_DATE ('2008-4-2', 'yyyy-mm-dd'), 'a', 1, 1
        FROM DUAL)语句1
SELECT aaa.fdepartment 部门, aaa.tot_a "是否在a刊物发表文章数量",
       aaa.mon_a_num "是否在a刊物发表文章数量",
       aaa.tot_b "是否在b刊物发表文章数量",
       aaa.mon_b_num "是否在b刊物发表文章数量"
  FROM (SELECT aa.*, SUM (mon_a_num) OVER (PARTITION BY aa.fdepartment) tot_a,
               SUM (mon_b_num) OVER (PARTITION BY aa.fdepartment) tot_b,
               ROW_NUMBER () OVER (PARTITION BY aa.fdepartment ORDER BY aa.months)
                                                                           rn
          FROM (SELECT   a.fdepartment, TO_CHAR (fdate, 'YYYYMM') months,
                         SUM (a.femittorf) mon_a_num,
                         SUM (a.fhomepagetorf) mon_b_num
                    FROM oa_inforelease a
                   WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                                     AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')
                GROUP BY a.fdepartment, TO_CHAR (fdate, 'YYYYMM')) aa) aaa
 WHERE aaa.rn = 1结果
Row# 部门 是否在a刊物发表文章数量 是否在a刊物发表文章数量_1 是否在b刊物发表文章数量 是否在b刊物发表文章数量_11 b 1 1 1 1
2 c 0 0 1 1语句2
SELECT aa.fdepartment 部门, aa.tot_a "是否在a刊物发表文章数量",
       aa.last_a "是否在a刊物发表文章数量",
       aa.tot_b "是否在b刊物发表文章数量",
       aa.last_b "是否在b刊物发表文章数量"
  FROM (SELECT a.*, SUM (a.femittorf) OVER (PARTITION BY a.fdepartment) tot_a,
               SUM (a.femittorf) OVER (PARTITION BY a.fdepartment, TO_CHAR
                                                                     (a.fdate,
                                                                      'yyyymm'
                                                                     ))
                                                                       last_a,
               SUM (a.fhomepagetorf) OVER (PARTITION BY a.fdepartment) tot_b,
               SUM (a.fhomepagetorf) OVER (PARTITION BY a.fdepartment, TO_CHAR
                                                                         (a.fdate,
                                                                          'yyyymm'
                                                                         ))
                                                                       last_b,
               ROW_NUMBER () OVER (PARTITION BY a.fdepartment ORDER BY a.fdate DESC)
                                                                           rn
          FROM oa_inforelease a
         WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                           AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')) aa
 WHERE aa.rn = 1结果Row# 部门 是否在a刊物发表文章数量 是否在a刊物发表文章数量_1 是否在b刊物发表文章数量 是否在b刊物发表文章数量_11 b 1 1 1 1
2 c 0 0 1 1

Answer 5

方法1有错误，order by 里日期应该是DESC的
SELECT aaa.fdepartment 部门, aaa.tot_a "是否在a刊物发表文章数量",
       aaa.mon_a_num "是否在a刊物发表文章数量",
       aaa.tot_b "是否在b刊物发表文章数量",
       aaa.mon_b_num "是否在b刊物发表文章数量"
  FROM (SELECT aa.*, SUM (mon_a_num) OVER (PARTITION BY aa.fdepartment) tot_a,
               SUM (mon_b_num) OVER (PARTITION BY aa.fdepartment) tot_b,
               ROW_NUMBER () OVER (PARTITION BY aa.fdepartment ORDER BY aa.months desc)
                                                                           rn
          FROM (SELECT   a.fdepartment, TO_CHAR (fdate, 'YYYYMM') months,
                         SUM (a.femittorf) mon_a_num,
                         SUM (a.fhomepagetorf) mon_b_num
                    FROM oa_inforelease a
                   WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                                     AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')
                GROUP BY a.fdepartment, TO_CHAR (fdate, 'YYYYMM')) aa) aaa
 WHERE aaa.rn = 1

Answer 6

SELECT   aa.fdepartment 部门, SUM (aa.tot_a) "是否在a刊物发表文章数量",
         SUM (aa.a_num) "是否在a刊物发表文章数量",
         SUM (aa.tot_b) "是否在b刊物发表文章数量",
         SUM (aa.b_num) "是否在b刊物发表文章数量"
    FROM (SELECT   a.fdepartment, 0 tot_a, SUM (a.femittorf) a_num, 0 tot_b,
                   SUM (a.fhomepagetorf) b_num
              FROM oa_inforelease a,
                   (SELECT   a.fdepartment, MAX (TRUNC (a.fdate, 'mm'))
                                                                       last_m
                        FROM oa_inforelease a
                       WHERE a.fdate BETWEEN TO_DATE ('2008-2-2',
                                                      'yyyy-mm-dd')
                                         AND TO_DATE ('2008-3-2',
                                                      'yyyy-mm-dd')
                    GROUP BY a.fdepartment) b
             WHERE a.fdepartment = b.fdepartment
               AND TRUNC (a.fdate, 'mm') = b.last_m
          GROUP BY a.fdepartment
          UNION ALL
          SELECT   a.fdepartment, SUM (a.femittorf) tot_a, 0,
                   SUM (a.fhomepagetorf) tot_b, 0
              FROM oa_inforelease a
             WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                               AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')
          GROUP BY a.fdepartment) aa
GROUP BY aa.fdepartment

Answer 7

SELECT   aa.fdepartment 部门, SUM (aa.tot_a) "是否在a刊物发表文章数量",
         SUM (aa.a_num) "是否在a刊物发表文章数量",
         SUM (aa.tot_b) "是否在b刊物发表文章数量",
         SUM (aa.b_num) "是否在b刊物发表文章数量",bb.FRELEASECOUNT
    FROM (SELECT   a.fdepartment, 0 tot_a, SUM (a.femittorf) a_num, 0 tot_b,
                   SUM (a.fhomepagetorf) b_num
              FROM oa_inforelease a,
                   (SELECT   a.fdepartment, MAX (TRUNC (a.fdate, 'mm'))
                                                                       last_m
                        FROM oa_inforelease a
                       WHERE a.fdate BETWEEN TO_DATE ('2008-2-2',
                                                      'yyyy-mm-dd')
                                         AND TO_DATE ('2008-3-2',
                                                      'yyyy-mm-dd')
                    GROUP BY a.fdepartment) b
             WHERE a.fdepartment = b.fdepartment
               AND TRUNC (a.fdate, 'mm') = b.last_m
          GROUP BY a.fdepartment
          UNION ALL
          SELECT   a.fdepartment, SUM (a.femittorf) tot_a, 0,
                   SUM (a.fhomepagetorf) tot_b, 0
              FROM oa_inforelease a
             WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                               AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')
          GROUP BY a.fdepartment) aa，
            (
            select a.FDEPARTMENT,a.FRELEASECOUNT 
                 from OA_DEPTRELEASE 
                     
            ) bb
where aa.FDEPARTMENT=bb.FDEPARTMENT
            
GROUP BY aa.fdepartment

Answer 8

SELECT   aa.fdepartment 部门, SUM (aa.tot_a) "是否在a刊物发表文章数量",
         SUM (aa.a_num) "是否在a刊物发表文章数量",
         SUM (aa.tot_b) "是否在b刊物发表文章数量",
         SUM (aa.b_num) "是否在b刊物发表文章数量",nvl(bb.FRELEASECOUNT,0)
    FROM (SELECT   a.fdepartment, 0 tot_a, SUM (a.femittorf) a_num, 0 tot_b,
                   SUM (a.fhomepagetorf) b_num
              FROM oa_inforelease a,
                   (SELECT   a.fdepartment, MAX (TRUNC (a.fdate, 'mm'))
                                                                       last_m
                        FROM oa_inforelease a
                       WHERE a.fdate BETWEEN TO_DATE ('2008-2-2',
                                                      'yyyy-mm-dd')
                                         AND TO_DATE ('2008-3-2',
                                                      'yyyy-mm-dd')
                    GROUP BY a.fdepartment) b
             WHERE a.fdepartment = b.fdepartment
               AND TRUNC (a.fdate, 'mm') = b.last_m
          GROUP BY a.fdepartment
          UNION ALL
          SELECT   a.fdepartment, SUM (a.femittorf) tot_a, 0,
                   SUM (a.fhomepagetorf) tot_b, 0
              FROM oa_inforelease a
             WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                               AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')
          GROUP BY a.fdepartment) aa，
            (
            select a.FDEPARTMENT,a.FRELEASECOUNT 
                 from OA_DEPTRELEASE a
                     
            ) bb
where aa.FDEPARTMENT=bb.FDEPARTMENT(+)
            
GROUP BY aa.fdepartment,nvl(bb.FRELEASECOUNT,0)

Answer 9

SELECT   aa.fdepartment 部门, SUM (aa.tot_a) "是否在a刊物发表文章数量",
         SUM (aa.a_num) "是否在a刊物发表文章数量",
         SUM (aa.tot_b) "是否在b刊物发表文章数量",
         SUM (aa.b_num) "是否在b刊物发表文章数量",nvl(bb.FRELEASECOUNT,0),
         cc.tot
    FROM (SELECT   a.fdepartment, 0 tot_a, SUM (a.femittorf) a_num, 0 tot_b,
                   SUM (a.fhomepagetorf) b_num
              FROM oa_inforelease a,
                   (SELECT   a.fdepartment, MAX (TRUNC (a.fdate, 'mm'))
                                                                       last_m
                        FROM oa_inforelease a
                       WHERE a.fdate BETWEEN TO_DATE ('2008-2-2',
                                                      'yyyy-mm-dd')
                                         AND TO_DATE ('2008-3-2',
                                                      'yyyy-mm-dd')
                    GROUP BY a.fdepartment) b
             WHERE a.fdepartment = b.fdepartment
               AND TRUNC (a.fdate, 'mm') = b.last_m
          GROUP BY a.fdepartment
          UNION ALL
          SELECT   a.fdepartment, SUM (a.femittorf) tot_a, 0,
                   SUM (a.fhomepagetorf) tot_b, 0
              FROM oa_inforelease a
             WHERE a.fdate BETWEEN TO_DATE ('2008-2-2', 'yyyy-mm-dd')
                               AND TO_DATE ('2008-3-2', 'yyyy-mm-dd')
          GROUP BY a.fdepartment) aa，
            (
            select a.FDEPARTMENT,a.FRELEASECOUNT 
                 from OA_DEPTRELEASE a
                     
            ) bb,
           (
            select nvl(sum(a.FRELEASECOUNT),0)  tot
                 from OA_DEPTRELEASE a
                     
            ) cc
where aa.FDEPARTMENT=bb.FDEPARTMENT(+)
            
GROUP BY aa.fdepartment,nvl(bb.FRELEASECOUNT,0),cc.tot