SQL行列转换实战

行列转换实例
                  表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<B］＝1－sign（1＋sign（A－B）） 
                  不能用-sign（A－B）：因为如果不满足A<b则返回-1，而不是0，这样就不能用在字段选择上了
                  ［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<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 )            
                    (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


                  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

                  [NextPage]
                  另外一个关于成绩的分析例子

                  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
 

 

mh:工时       mhtype:工时类型（6种）

字段：userid             mhtype         mh
            001                     1                 5
            001                     1                 3
            001                     2                 4
            001                     3                 9
            002                     5                 5
            002                     6                 7
            002                     3                 4
            002                     3                 9

要求统计出每个人每一类工时的总合
结果要求如下格式
userid     mhtype1       mhtype2       mhtype3       mhtype4       mhtype5       mhtype6       allmh
001           8                   4                     9                 0                       0                   0                 21
002           0                   0                     13                 0                       5                   7                 25

sql code

create table lk4(
userid mediumint(3) unsigned zerofill not null,
mhtype int not null,
mh int not null);
insert into lk4 values
(001,1,5),
(001,1,3),
(001,2,4),
(001,3,9),
(002,5,5),
(002,6,7),
(002,3,4),
(002,3,9);

select userid,
sum((case when mhtype=1 then mh else 0 end )) as 'mhtype1',
sum((case when mhtype=2 then mh else 0 end )) as 'mhtype2',
sum((case when mhtype=3 then mh else 0 end )) as 'mhtype3',
sum((case when mhtype=4 then mh else 0 end )) as 'mhtype4',
sum((case when mhtype=5 then mh else 0 end )) as 'mhtype5',
sum((case when mhtype=6 then mh else 0 end )) as 'mhtype6',
sum(mh) as 'allmh'
from lk4 group by userid;