对于行转列,事实上oracle是不可能实现的,只能写程序
虽然有些在行数固定,且数量很少的情况下,可以变通实现,但也受到很多限制
虽然有些在行数固定,且数量很少的情况下,可以变通实现,但也受到很多限制
解决方案 »
- 请问oracle中能否得到所在服务器的CPU型号信息、CPU利用率、OS信息
- 请教个先排序再union all 的sql
- 简单SQL求解!!!
- 谁有oracle 10g的jdbc驱动?
- ctl中,能一次infile多个文件么?
- 数据库启动错误
- 控制某用户不能用select语句查看某些表?如果赋权限?
- : 我想把Table的一个字段的属性定义为另一个Table(表结构不同)的内容,大家给点指导好吗?
- 还是中文问题的啊
- 请教:在8.1.7中能更改数据库的名字吗?
- 备份某Table时,而此Table中有自增字段(通过sequence实现的),在恢复时会不会有问题?
- oracle9i 中的Partitioning功能的请教
-------- -------- --------
1111 AAAA 10
1111 BBBB 20
1111 dj 54
2222 dj 122
3333 AAAA 21
3333 dj 200已选择6行。select * from(
select yhdm,sum(aaaa) AAAA,sum(bbbb) BBBB,sum(dj0) dj0,'' dj1 from
(
select a.YHDM,
decode('AAAA',a.djlb,a.DF,'') AAAA,
decode('BBBB',a.djlb,a.DF,'') BBBB,
decode('dj',a.djlb,a.DF,'') dj0,'' dj1 from aa a,
(select distinct yhdm from aa where djlb='AAAA' OR djlb='BBBB') b
where a.YHDM=b.YHDM
)
group by yhdm
union all
select a.YHDM,to_number('') AAAA,to_number('')BBBB,to_number('') dj0,a.DF dj1 from aa a,
(select yhdm from aa
minus
select yhdm from aa where djlb='AAAA' OR djlb='BBBB') b
where a.YHDM=b.YHDM
)
order by yhdm;YHDM AAAA BBBB DJ0 DJ1
-------- ---------- ---------- ---------- --------
1111 10 20 54
2222 122
3333 21 200SQL>
decode(aaaa,0,null,aaaa) aaaa,
decode(bbbb,0,null,bbbb) bbbb,
decode(a+b,0,null,dj) dj0,
decode(a+b,0,dj,null) dj1
from
(
select yhdm,
sum(decode(djlb,'AAAA',df,0)) aaaa,
sum(decode(djlb,'BBBB',df,0)) bbbb,
sum(decode(djlb,'dj',df,0)) dj
from 表A
)
否则俺的语句不适用select yhdm,sum(aaaa) AAAA,sum(bbbb) BBBB,sum(dj0) dj0,sum(dj1) dj1 from
(
select a.YHDM,
decode('AAAA',a.djlb,a.DF,'') AAAA,
decode('BBBB',a.djlb,a.DF,'') BBBB,
decode('dj',a.djlb,a.DF,'') dj0,'' dj1 from aa a,
(select distinct yhdm from aa where djlb='AAAA' OR djlb='BBBB') b
where a.YHDM=b.YHDM
union all
select x.YHDM,'' AAAA,'' BBBB,'' dj0,x.DF dj1 from aa x,
(select yhdm from aa
minus
select yhdm from aa where djlb='AAAA' OR djlb='BBBB') y
where x.YHDM=y.YHDM
)
group by yhdm;YHDM AAAA BBBB DJ0 DJ1
-------- ---------- ---------- ---------- ----------
1111 10 20 54
2222 122
3333 21 200
djlb的值的个数是不定的,不过谢谢大家先
既然这样,我的语句适用
是不是除了AAAA和BBBB其它的都归为dj0和dj1 ?
SQL> select * from aa;YHDM DJLB DF
-------- -------- -------
1111 AAAA 10
1111 BBBB 20
1111 dj 54
2222 dj 122
3333 AAAA 21
3333 dj 200
4444 gg 500
4444 AAAA 600已选择8行。select yhdm,sum(aaaa) AAAA,sum(bbbb) BBBB,sum(dj0) dj0,sum(dj1) dj1 from
(
select a.YHDM,
decode(a.djlb,'AAAA',a.DF,'') AAAA,
decode(a.djlb,'BBBB',a.DF,'') BBBB,
decode(a.djlb,'AAAA','','BBBB','',a.df) dj0,'' dj1 from aa a,
(select distinct yhdm from aa where djlb='AAAA' OR djlb='BBBB') b
where a.YHDM=b.YHDM
union all
select x.YHDM,'' AAAA,'' BBBB,'' dj0,x.DF dj1 from aa x,
(select yhdm from aa
minus
select yhdm from aa where djlb='AAAA' OR djlb='BBBB') y
where x.YHDM=y.YHDM
)
group by yhdm;YHDM AAAA BBBB DJ0 DJ1
-------- ---------- ---------- ---------- ----------
1111 10 20 54
2222 122
3333 21 200
4444 600 500
decode(aaaa,0,null,aaaa) aaaa,
decode(bbbb,0,null,bbbb) bbbb,
decode(a+b,0,null,dj) dj0,
decode(a+b,0,dj,null) dj1
from
(
select yhdm,
sum(decode(djlb,'AAAA',df,0)) aaaa,
sum(decode(djlb,'BBBB',df,0)) bbbb,
sum(decode(djlb,'AAAA',0,'BBBB',0,df)) dj
from 表A
)
decode(aaaa,0,null,aaaa) aaaa,
decode(bbbb,0,null,bbbb) bbbb,
decode(aaaa+bbbb,0,null,dj) dj0,
decode(aaaa+bbbb,0,dj,null) dj1
from
(
select yhdm,
sum(decode(djlb,'AAAA',df,0)) aaaa,
sum(decode(djlb,'BBBB',df,0)) bbbb,
sum(decode(djlb,'AAAA',0,'BBBB',0,df)) dj
from 表A
group by yhdm
)