提个高深的问题：从数据库内挖掘出交叉表，分都全给了

Question

sum(decode(sign(...)))...  group by ...,
这是典型用法
要不给你个例子吧，又得造表了，faintSQL> select  * from test;         A          B
---------- ----------
        12         32
       324          1
         0        111
1.1111E+11          1
     12345      10000
      1111      10000
         1      10000
    123456
        12         32
       324          1
         0        111         A          B
---------- ----------
1.1111E+11          1
     12345      10000
      1111      10000
         1      10000
    123456
        12         32
       324          1
         0        111
1.1111E+11          1
     12345      10000
      1111      10000         A          B
---------- ----------
         1      10000
    123456
        12         32
       324          1
         0        111
1.1111E+11          1
     12345      10000
      1111      10000
         1      10000
    123456
      1010        413已选择33行。SQL> select a,sum(decode(sign(b - 0)+sign(100 - b),2,1,decode(sign(100 - b),0,1,0))) n1,
  2         sum(decode(sign(b - 100)+sign(1000 - b),2,1,decode(sign(1000 - b),0,1,0))) n2,
  3         sum(decode(sign(b - 1000),1,1,0)) n3
  4  from   test
  5  group by a;         A         N1         N2         N3
---------- ---------- ---------- ----------
         0          0          4          0
         1          0          0          4
        12          4          0          0
       324          4          0          0
      1010          0          1          0
      1111          0          0          4
     12345          0          0          4
    123456          0          0          0
1.1111E+11          4          0          0已选择9行。SQL> 
不知道兄弟看出门道来了没有
你需要注意的就是利用时间来取代数字
根据年分组
根据月来确定季度
方法已经给你了
剩下的我想该你自己来完成了

Answer 1

非常容易
SELECT TO_CHAR(VISIT_DATE,'YYYY'),
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),1,1,0)),
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),2,1,0)),
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),3,1,0)),
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),4,1,0))
  FROM MYTABLE
 GROUP BY TO_CHAR(VISIT_DATE,'YYYY');

Answer 2

倒没有注意to_char的季度功能
呵呵
多谢弱水兄及时提醒

Answer 3

可惜来晚了.
To_Char(sysdate,'Q')转换为季度,我也是前几天刚从一个软件上学来的.
不过,做报表,应该用一些大型工具,如Business Object等.交叉表之类是最基本的了.

Answer 4

可惜来晚了.
To_Char(sysdate,'Q')转换为季度,我也是前几天刚从一个软件上学来的.
不过,做报表,应该用一些大型工具,如Business Object等.交叉表之类是最基本的了.

Answer 5

SELECT TO_CHAR(VISIT_DATE,'YYYY') "年份",
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),1,1,0)) "第一季度",
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),2,1,0)) "第二季度",
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),3,1,0)) "第三季度",
       SUM(DECODE(TO_CHAR(VISIT_DATE,'Q'),4,1,0)) "第四季度"
  FROM MYTABLE
 GROUP BY TO_CHAR(VISIT_DATE,'YYYY');如果是列数不定的情况的话，只能用动态pl/sql来完成。

Answer 6

KingSunSha(弱水三千)可是个大师级人物哦，佩服佩服！

Answer 7

我总结的统计SQL语句：欢迎提出意见
1、年度按月份统计组团次数
select to_char(groups.start_date,'yyyy-mm') ,count(groups.start_date) from groups where 
groups.start_date between to_date('2002-01-01','yyyy-mm-dd') and 
to_date('2002-12-31','yyyy-mm-dd')
group by to_char(groups.start_date,'yyyy-mm')
2、年度统计组团次数
   select to_char(TUANZ.APPLY_DATE,'yyyy') ,count(TUANZ.APPLY_DATE) from TUANZ 
group by to_char(TUANZ.APPLY_DATE,'yyyy')
3、年度第一季度统计组团次数
   select to_char(TUANZ.APPLY_DATE,'yyyy') ,count(TUANZ.APPLY_DATE) from TUANZ where TUANZ.APPLY_DATE 
between to_date('2000-01-01','yyyy-mm-dd') 
and to_date('2000-03-30','yyyy-mm-dd')
group by to_char(TUANZ.APPLY_DATE,'yyyy')
4、年度季度统计组团次数
   SELECT TO_CHAR(tuanz.apply_date,'YYYY'),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'Q'),1,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'Q'),2,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'Q'),3,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'Q'),4,1,0))
 FROM tuanz
 GROUP BY TO_CHAR(tuanz.apply_date,'YYYY')
 having(TO_CHAR(tuanz.apply_date,'YYYY') in ('1999','2000','2001'))
 
5、日期转换到星期
   TO_CHAR(tuanz.apply_date,'D')//week
   TO_CHAR(tuanz.apply_date,'J')// not understand
   TO_CHAR(tuanz.apply_date,'I')//end of year
6、年度按月份统计组团次数
   SELECT TO_CHAR(tuanz.apply_date,'YYYY'),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),01,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),02,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),03,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),04,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),05,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),06,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),07,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),08,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),09,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),10,1,0)),                                   
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),11,1,0)),
       SUM(DECODE(TO_CHAR(tuanz.apply_date,'mm'),12,1,0)) 
 FROM tuanz
 GROUP BY TO_CHAR(tuanz.apply_date,'YYYY')
 having(TO_CHAR(tuanz.apply_date,'YYYY') in ('1999','2000','2001'))

Answer 8

竖表的横置的典型方法就是
sum(decode())  group by ...