select code,sum(decode(date,'2004/01/01',letter,0)) '2004/04/01', sum(decode(date,'2004/01/02',letter,0)) '2004/04/02', sum(decode(date,'2004/01/03',letter,0)) '2004/04/03' from tbname group by code;
给你一个例子,我还没看呢,大家共同学习学习 行列转换实例 表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 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>另外一个关于成绩的分析例子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
bzszp(www.bzszp.533.net):
如果是letter字段为是其他的类型呢,例如varchar2,可以实现吗?
freddy2003() 那么存储过程又该如何实现呢?可不可以贴一下代码,我这里先谢谢了
sum(decode(date,'2004/01/02',letter,0)) '2004/04/02',
sum(decode(date,'2004/01/03',letter,0)) '2004/04/03'
from tbname group by code;
表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
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>另外一个关于成绩的分析例子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