看看这个句子怎么写

[frogshero]

表A

yhdm djlb df
1111 AAAA 10
1111 BBBB 20
1111 dj 54
2222 dj 122
3333 AAAA 21
3333 dj 200

要得到的结果：
yhdm AAAA BBBB dj0 dj1
1111 10 20 54
2222 122
3333 21 200

重要的不只是行转列，还有要判断如用户存在djlb为 AAAA 或 BBBB的记录，那他的djlb为dj的记录就要放在dj0列（如用户1111和3333），如没有（如用户2222）则要放在dj1列
数据有几十万条，不知怎么才能快点？

[ATGC]

对于行转列，事实上oracle是不可能实现的，只能写程序
虽然有些在行数固定，且数量很少的情况下，可以变通实现，但也受到很多限制

[frogshero]

??

[ATGC]

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

已选择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 200

SQL>

[wupangzi]

快点？
把你的语句贴出来看看！一般都比较慢！

[ORARichard]

--try: 没测试

select yhdm,
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
)

[ATGC]

也许这样快点，不过你的djlb只能有'AAAA','BBBB'和‘dj'这3种值
否则俺的语句不适用

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

[frogshero]

to ATGC(想到南极去看看飞翔的海鸥) :
djlb的值的个数是不定的，不过谢谢大家先

[frogshero]

djlb的值的个数是不定的，但是判断只需要判断aaaa, bbbb这两个

[ATGC]

我的语句对个数多少没有关系，我说的是取值必须是AAAA和BBBB和其它
既然这样，我的语句适用

[ATGC]

我觉得楼主的表达有点问题。。
是不是除了AAAA和BBBB其它的都归为dj0和dj1 ？

[ATGC]

如果是我说的这个意思，那就这样，改一改
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

[ORARichard]

--retry: 没测试

select yhdm,
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
)

[ORARichard]

select yhdm,
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
)

[zyh123]

写个分户统计的的包来实现吧