ITPub博客

首页 > 数据库 > Oracle > Oracle--SQL行列转换实战

Oracle--SQL行列转换实战

原创 Oracle 作者:csbin 时间:2006-06-02 08:35:05 0 删除 编辑
Oracle--SQL行列转换实战[@more@]

  行列转换实例  表ttt有三个字段
  seq --序列
  jcxm --检查项目
  zhi --值
  
  数据分别如下:
  seq   jcxm     zhi
  -------   --------     --------
  11     1    0.50
  11     2    0.21
  11     3    0.25
  12     1    0.24
  12     2    0.30
  12     3    0.22
  
  实现功能  创建视图时移动行值为列值
  
  create view v_view1
  as
  select seq,
  sum(decode(jcxm,1, zhi)) 检测项目1,
  sum(decode(jcxm,2, zhi)) 检测项目2,
  sum(decode(jcxm,3, zhi)) 检测项目3
  from ttt
  group by seq;
  
  序号 检测项目1  检测项目2  检测项目3
  11     0.50    0.21     0.25
  12     0.24    0.30     0.22
  
  技巧:  用THEN中的0和1来进行统计(SUM)
  
  jcxm  zhi
  ----  ----
  a      1
  b      1
  a      3
  d      2
  e      4
  f      5
  a      5
  d      3
  d      6
  b      5
  c      4
  b      3
  求他的zhi既是1,也是3,也是5的jcxm
  方法一
  select jcxm
  from ttt
  group by jcxm
  having sum(decode(zhi,1,-1,3,-1,5,-1,0)) = -3
  方法二
  select jcxm from ttt
  group by jcxm having (sign(sum(decode(zhi,1,-1,0)))+
  sign(sum(decode(zhi,3,-1,0)))+sign(sum(decode(zhi,5,-1,0)))<=-3);
  
  ----------
  a
  b
  说明:
  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  [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 ] (6)
  [αOR b ]=sign(d [α]+d [b ])
  
  例如:
  A   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 )       (1-Sign(LogA))
  LogA and LogB      LogA * LogB
  LogA or LogB       LogA + LogB
  LogA xor LogB      Decode(Sign(LogA),Sign(LogB),0,1)
  Mod(Sign(LogA),Sign(LogB),2
  
  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  
  另外一个关于成绩的分析例子
  
  SELECT
  SUM(CASE WHEN cj <60 THEN 1 ELSE 0 END) as "not passed",
  SUM(CASE WHEN cj BETWEEN 60 AND 79 THEN 1 ELSE 0 END) as "passed",
  SUM(CASE WHEN cj BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "good",
  SUM(CASE WHEN cj >=90 THEN 1 ELSE 0 END) as "Excellent"
  FROM cjtable;
  
  decode用法2
  表、视图结构转化
  现有一个商品销售表sale,表结构为:
  month    char(6)      --月份
  sell    number(10,2)    --月销售金额
  
  现有数据为:
  200001  1000
  200002  1100
  200003  1200
  200004  1300
  200005  1400
  200006  1500
  200007  1600
  200101  1100
  200202  1200
  200301  1300
  
  想要转化为以下结构的数据:
  year   char(4)        --年份
  ------------  ---------------------
  month1  number(10,2)   --1月销售金额
  month2  number(10,2)   --2月销售金额
  month3  number(10,2)   --3月销售金额
  month4  number(10,2)   --4月销售金额
  month5  number(10,2)   --5月销售金额
  month6  number(10,2)   --6月销售金额
  month7  number(10,2)   --7月销售金额
  month8  number(10,2)   --8月销售金额
  month9  number(10,2)   --9月销售金额
  month10  number(10,2)   --10月销售金额
  month11  number(10,2)   --11月销售金额
  month12  number(10,2)   --12月销售金额
  
  结构转化的SQL语句为:
  create or replace view
  v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
  as
  select
  substrb(month,1,4),
  sum(decode(substrb(month,5,2),'01',sell,0)),
  sum(decode(substrb(month,5,2),'02',sell,0)),
  sum(decode(substrb(month,5,2),'03',sell,0)),
  sum(decode(substrb(month,5,2),'04',sell,0)),
  sum(decode(substrb(month,5,2),'05',sell,0)),
  sum(decode(substrb(month,5,2),'06',sell,0)),
  sum(decode(substrb(month,5,2),'07',sell,0)),
  sum(decode(substrb(month,5,2),'08',sell,0)),
  sum(decode(substrb(month,5,2),'09',sell,0)),
  sum(decode(substrb(month,5,2),'10',sell,0)),
  sum(decode(substrb(month,5,2),'11',sell,0)),
  sum(decode(substrb(month,5,2),'12',sell,0))
  from sale
  group by substrb(month,1,4);
  
  体会:要用decode /group by/ order by/sign/sum来实现不同报表的生成
  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  CASE应用
  
  1    1    部门a    800    男
  2    2    部门b    900    女
  3    3    部门a    400    男
  4    4    部门d    1400    女
  5    5    部门e    1200    男
  6    6    部门f    500    男
  7    7    部门a    300    女
  8    8    部门d    1000    男
  9    9    部门d    1230    女
  10    10    部门b    2000    女
  11    11    部门c    2000    男
  12    12    部门b    1200    男
  
  SELECT jcxm as 部门,COUNT(seq) as 人数,
  SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
  SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
  SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 小于800元,
  SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000)          /*用*来实现<和>功能*/
  WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
  WHEN 800 THEN 1 ELSE 0 END)) as 从800至999,     /*注意别名不能以数字开头*/
  SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
  WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
  WHEN 1000 THEN 1 ELSE 0 END)) as 从1000元至1199元,
  SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
  +(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大于1200元
  FroM ttt
  GROUP BY jcxm
  
  部门名 人数  男  女  小于800元 从800至999 从1000元至1199元  大于1200元
  部门a 3    2  1    2    1      0        0
  部门b 3    1  2    0    1      0        2
  部门c 1    1  0    0    0      0        1
  部门d 3    1  2    0    0      1        2
  部门e 1    1  0    0    0      0        1
  部门f 1    1  0    1    0      0        0

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/720091/viewspace-839286/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-12

  • 博文量
    69
  • 访问量
    1460754