如下表:
ID 名称 类型 数量
1 0001 A 3
2 0001 A 1
3 0001 B 2
4 0002 A 4
5 0002 B 6
6 0002 B 3
查询的结果,我想要的是这种形式
名称 类型A 数量 类型B 数量
0001 A 4 B 2
0002 A 4 B 9我自己试了几次都没解出来,请高手帮忙解出来。谢谢。
ID 名称 类型 数量
1 0001 A 3
2 0001 A 1
3 0001 B 2
4 0002 A 4
5 0002 B 6
6 0002 B 3
查询的结果,我想要的是这种形式
名称 类型A 数量 类型B 数量
0001 A 4 B 2
0002 A 4 B 9我自己试了几次都没解出来,请高手帮忙解出来。谢谢。
SELECT NAME, MAX(TYPEA) "类型A", SUM(NUM_1)
, MAX(TYPEB) "类型B", SUM(NUM_2)
FROM (
SELECT DISTINCT NAME
,DECODE(TYPE,'A','A',NULL) AS TYPEA
,SUM(DECODE(TYPE,'A',NUM,0)) OVER (PARTITION BY NAME, TYPE) NUM_1
,DECODE(TYPE,'B','B',NULL) AS TYPEB
,SUM(DECODE(TYPE,'B',NUM,0)) OVER (PARTITION BY NAME, TYPE) NUM_2
FROM ABC
)
GROUP BY NAME
名称,
'A',
sum(decode(ttt.类型,'A',ttt.数量,0),
'B',
sum(decode(ttt.类型,'B',ttt.数量,0),
from ttt
group by 名称
select
名称,
'A',
sum(decode(ttt.类型,'A',ttt.数量,0)),
'B',
sum(decode(ttt.类型,'B',ttt.数量,0)),
from ttt
group by 名称
2 UNION
3 SELECT 2 ID,'0001' NAME,'A' TYPE,1 QTY FROM DUAL
4 UNION
5 SELECT 3 ID,'0001' NAME,'B' TYPE,2 QTY FROM DUAL
6 UNION
7 SELECT 4 ID,'0002' NAME,'A' TYPE,4 QTY FROM DUAL
8 UNION
9 SELECT 5 ID,'0002' NAME,'B' TYPE,6 QTY FROM DUAL
10 UNION
11 SELECT 6 ID,'0002' NAME,'B' TYPE,3 QTY FROM DUAL
12 )
13 SELECT NAME,MAX(DECODE(RN,1,TYPE,NULL)) TYPEA,
14 MAX(DECODE(RN,1,QTY,NULL)) QTYA,
15 MAX(DECODE(RN,2,TYPE,NULL)) TYPEB,
16 MAX(DECODE(RN,2,QTY,NULL)) QTYB
17 FROM (SELECT NAME,TYPE,QTY,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NAME) RN FROM (select NAME,TYPE,SUM(QTY) QTY from A
18 GROUP BY NAME,TYPE
19 ORDER BY NAME))
20 GROUP BY NAME
21 /
NAME TYPEA QTYA TYPEB QTYB
---- ----- ---------- ----- ----------
0001 A 4 B 2
0002 A 4 B 9
非常感谢楼上三位的帮助。^_^
到这儿看看吧
http://hi.baidu.com/songyl525/blog/item/67829cb3f1c125a3d8335ada.html
要仔细研究
2 'A' as A,
3 sum(decode(tt.catalog,'A',tt.amount,0)) as A_count,
4 'B' as B,
5 sum(decode(tt.catalog,'B',tt.amount,0)) as B_count
6 from tablename tt
7 group by tt.name;NAME A A_COUNT B B_COUNT
---- - ---------- - ----------
0001 A 4 B 2
0002 A 4 B 9
create table a as
select 1 id_a,'0001' name_a,'A' type_a,3 number_a from dual
union all
select 2,'0001','A',1 from dual
union all
select 3,'0001','B',2 from dual
union all
select 4,'0002','A',4 from dual
union all
select 5,'0002','B',6 from dual
union all
select 6,'0002','B',3 from dualselect
c.name_a,
c.type_a,
c.acc,
d.type_a,
d.acc
from
(
select
a.name_a,
a.type_a,
sum(a.number_a) acc
from a
where a.type_a='A'
group by
a.name_a,
a.type_a
) c,
(
select
a.name_a,
a.type_a,
sum(a.number_a) acc
from a
where a.type_a='B'
group by
a.name_a,
a.type_a
) d
where c.name_a=d.name_a用不上什么行列转换,更谈不上分析函数了
名称,
'A' 类型A,
SUM(DECODE(类型, 'A',数量, 0)) 类型A数量,
'B' 类型B,
SUM(DECODE(类型, 'B',数量, 0)) 类型B数量,
FROM 表名
GROUP BY 名称
跟“bjt_ ”没什么差别。
---------------------------
“bjt”的方法也能查出来,只是会有多余的数据,比如:0001会产生两行的数据。
---------------------------
估计是表中数据存在“0001”和“ 0001”的情况,空格问题,使用trim函数解决即可。