oracle 行列统计转换

纯粹自己使用方便；

本文系转载：http://www.cnblogs.com/fjfzhkb/archive/2007/11/30/978267.html

感谢网友提供！

 

体会：要用decode /group by/ order by/sign/sum来实现不同的统计和不同报表的生成

--求hkb_test1中Y的值既是1，也是3，也是5的X
select * from hkb_test1;
X        Y
---- -----
a        1
b        1
a        3
d        2
e        4
f        5
a        5
d        3
d        6
b        5
c        4
b        3

结果：
X
----
a
b
--方法一
select x
  from hkb_test1
 group by x
having sum(decode(y, 1, -1, 3, -1, 5, -1, 0)) = -3;
--方法二
select x
  from hkb_test1
 group by x
having(sign(sum(decode(y, 1, -1, 0))) + sign(sum(decode(y, 3, -1, 0))) + sign(sum(decode(y, 5, -1, 0))) <= -3);

PS：　　
    sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
所以可以用sign和decode来完成比较字段大小来区分某个字段。
select decode(sign(字段1-字段2),-1,字段3,字段4) from dual;

抄来的没有弄的很明白的：
　　sign是一个对于写分析SQL有很强大的功能
　　下面对sign进行一些总结：
　　但属性student取0和1以外的值，或者student取两个以上的标法值，问题就不会这么简单了
　　解决办法就是特征函数(abs(),sign())
　　
　　常用的特征算法
　　［A＝B］＝1－abs(sign(A－B))
　　［A！＝B］＝abs(sign(A－B))
　　［A<B］＝1－sign(1＋sign(A－B)) 不能用-sign(A－B)：因为如果不满足A　　
    ［A<＝B］＝sign(1－sign(A－B))
　　［A>B］＝1－sign(1－sign(A－B))
　　［A>＝B］＝sign(1＋sign(A－B)))
　　［NOTα］＝1－d[α]
　　［αANDb ］＝d[α]*d[b]
　　［αOR b ］＝sign(d[α]＋d[b ])
　　
　　例如：
　　A<B　　　　　　　　　　　　 Decode( Sign(A-B), -1, 1, 0 )
　　A<=B　　　　　　　　　　　　Decode( Sign(A-B), 1, 0, 1 )
　　A>B　　　　　　　　　　　　 Decode( Sign(A-B), 1, 1, 0 )
　　A>=B　　　　　　　　　　　　Decode( Sign(A-B), -1, 0, 1 )
　　A=B　　　　　　　　　　　　 Decode( A, B, 1, 0 )
　　A between B and C　　　     Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))
　　A is null　　　　　　　　　 Decode(A,null,1,0)
　　A is not null　　　　　　　 Decode(A,null,0,1)
    A in (B1,B2,...,Bn)　       Decode(A,B1,1,B2,1,...,Bn,1,0)
　　nor LogA　　　　　　　　　　Decode( LogA, 0, 1, 0 )   
　　LogA and LogB　　　　　　   LogA * LogB
　　LogA or LogB　　　　　　　  LogA + LogB
　　LogA xor LogB　　　　　　   Decode(Sign(LogA),Sign(LogB),0,1)
　
　　>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
select * from grade1;
STUDENTID SUBJECTID MARK
--------- --------- ----
      101 A01         59
      101 A02         72
      101 A03         90
      102 A01         75
      102 A02         91
      103 A01         71

--统计学生参加的考试成绩
select STUDENTID,
       sum(decode(SUBJECTID,'A01', mark)) a,
       sum(decode(SUBJECTID,'A02', mark)) b,
       sum(decode(SUBJECTID,'A03', mark)) c
  from grade1
 group by STUDENTID;
STUDENTID          A          B          C
--------- ---------- ---------- ----------
      102         75         91
      101         59         72         90
      103         71           

--统计四种分数段的人数
select sum(case
             when mark < 60 then
              1
             else
              0
           end) as "not passed",
       sum(case
             when mark between 60 and 79 then
              1
             else
              0
           end) as "passed",
       sum(case
             when mark between 80 and 89 then
              1
             else
              0
           end) as "good",
       sum(case
             when mark >= 90 then
              1
             else
              0
           end) as "excellent"
  from grade1;
not passed     passed       good  excellent
---------- ---------- ---------- ----------
         1          3          0          2

--统计每年每个月的消费情况
select * from sale;
SUM_MONTH         SELL
--------- ------------
   200001      1000.00
   200002      1100.00
   200003      1200.00
   200004      1300.00
   200005      1400.00
   200006      1500.00
   200007      1600.00
   200101      1100.00
   200202      1200.00
   200301      1300.00

select substrb(sum_month, 1, 4) 年份,
       　　sum(decode(substrb(sum_month, 5, 2), '01', sell, 0)) 一月,
       　　sum(decode(substrb(sum_month, 5, 2), '02', sell, 0)) 二月,
       　　sum(decode(substrb(sum_month, 5, 2), '03', sell, 0)) 三月,
       　　sum(decode(substrb(sum_month, 5, 2), '04', sell, 0)) 四月,
       　　sum(decode(substrb(sum_month, 5, 2), '05', sell, 0)) 五月,
       　　sum(decode(substrb(sum_month, 5, 2), '06', sell, 0)) 六月,
       　　sum(decode(substrb(sum_month, 5, 2), '07', sell, 0)) 七月,
       　　sum(decode(substrb(sum_month, 5, 2), '08', sell, 0)) 八月,
       　　sum(decode(substrb(sum_month, 5, 2), '09', sell, 0)) 九月,
       　　sum(decode(substrb(sum_month, 5, 2), '10', sell, 0)) 十月,
       　　sum(decode(substrb(sum_month, 5, 2), '11', sell, 0)) 十一月,
       　　sum(decode(substrb(sum_month, 5, 2), '12', sell, 0)) 十二月
  from sale
 group by substrb(sum_month, 1, 4);
年份  一月   二月   三月   四月  五月   六月   七月   八月   九月  十月  十一月  十二月
---- ---- ------ ------ ------ ------ ------ ------ ------ ------ ----- ------ ------
2000  1000   1100  1200   1300   1400   1500  1600     0     0     0     0     0
2001  1100      0     0      0      0      0     0     0     0     0     0     0
2003  1300      0     0      0      0      0     0     0     0     0     0     0
2000     0   1200     0      0      0      0     0     0     0     0     0     0
 
Attention：统计中CASE的运用！！！
例子：
select * from hkb_case_test;
USERID EMPNO     SAL SEX
------ ----- ------- ---
     1 a         800   1
     1 a         800   1
     2 b         900   2
     3 a         400   1
     4 d        1400   2
     5 e        1200   1
     6 f         500   1
     7 a         300   2
     8 d        1000   1
     9 d        1230   2
    10 b        2000   2
    11 c        2000   1
    12 b        1200   1

select a.empno as 部门,
       count(a.userid) as 人数,
       sum(case a.sex
             when 1 then
              1
             else
              0
           end) as 男,
       sum(case a.sex
             when 2 then
              1
             else
              0
           end) as 女,
       sum(case sign(a.sal - 800)
             when -1 then
              1
             else
              0
           end) as 小于800元, --注意别名不能以数字开头
       sum((case sign(a.sal - 800) * sign(a.sal - 1000) --用*来实现<和>功能
             when -1 then
              1
             else
              0
           end) + (case a.sal
             when 800 then
              1
             else
              0
           end)) as 从800至999,
       　　　　 sum((case sign(a.sal - 1000) * sign(a.sal - 1200)
         when -1 then
          1
         else
          0
       end) + (case a.sal
         when 1000 then
          1
         else
          0
       end)) as 从1000元至1199元,
       sum((case sign(a.sal - 1200)
             when 1 then
              1
             else
              0
           end) + (case a.sal
             when 1200 then
              1
             else
              0
           end)) as 大于1200元
  from hkb_case_test a
 group by a.empno;

部门  人数   男   女  小于800元 从800至999 从1000元至1199元 大于1200元
---- ------ ---- ---- --------- ---------- ---------------- ----------
a     4      3    1      2          2             0             0
b     3      1    2      0          1             0             2
c     1      1    0      0          0             0             1
e     1      1    0      0          0             0             1
d     3      1    2      0          0             1             2
f     1      1    0      1          0             0             0