现在的表中有数据
ID VALUE1 VALUE2
1 a 0.1
1 b 0.2
1 c 0.3
2 a 0.1
2 c 0.3现在我想取得的结果是
ID result1 result2
1 a、b、c 0.1、0.2、0.3
2 a、c 0.1、0.3
其中value1和value2的个数是不定的,只能用一个sql语句实现
ID VALUE1 VALUE2
1 a 0.1
1 b 0.2
1 c 0.3
2 a 0.1
2 c 0.3现在我想取得的结果是
ID result1 result2
1 a、b、c 0.1、0.2、0.3
2 a、c 0.1、0.3
其中value1和value2的个数是不定的,只能用一个sql语句实现
具体单位用量详见表 Bom
Parent Child Usage
A B 2
A C 3
B D 2
B E 3
E F 5用 select P, C, U,
LTRIM(sys_connect_by_path(P, '->'), '->'),
LTRIM(sys_connect_by_path(U, '->'), '->')
from bom
START WITH P = 'A'
CONNECT BY PRIOR C = P
可以得到如下的结果:
P C U Path Qty
A B 2 A 2
B D 2 A->B 2->2
B E 3 A->B 2->3
E F 5 A->B->E 2->3->5
A C 3 A 3
http://community.csdn.net/Expert/topic/5244/5244199.xml?temp=.9764063这里有非常详细的回答
CREATE OR REPLACE TYPE T_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
)
CREATE OR REPLACE TYPE BODY T_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
BEGIN
SCTX := T_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR ||','|| VALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := LTRIM(SELF.STR,',');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
CREATE OR REPLACE FUNCTION f_StrLink(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING T_LINK;先写个聚合函数,然后这样就可以了:
select year
,f_strLink(decode(flag,'1',qua,'')) as qua
,f_strLink(decode(flag,'2',qua,'')) as amo
from(
select year,'1' as flag
,f_strLink(quarter) as qua
--,f_strLink(amount)
from (select * from tbname order by id,result1,result2)
group by year
union all
select year,'2' as flag
--,f_strLink(quarter)
,f_strLink(amount) as qua
from (select * from tbname order by id,result1,result2)
group by year
)
group by year
insert into test values(1,'b',0.2);
insert into test values(1,'c',0.3);
insert into test values(2,'a',0.1);
insert into test values(2,'c',0.3);
commit;
select b.id,
(select ltrim(max(sys_connect_by_path(a.value1, '、')),'、') value1
from (select a.id,
a.value1,
a.n,
lead(a.n) over(partition by a.id order by a.n) n1
from (select a.id,
a.value1,
row_number() over(order by a.id, a.value1 desc) n
from test a) a) a
start with a.id = b.id
and a.n1 is null
connect by n1 = prior a.n) v1,
(select ltrim(max(sys_connect_by_path(a.value2, '、')),'、') value2
from (select a.id,
a.value2,
a.n,
lead(a.n) over(partition by a.id order by a.n) n1
from (select a.id,
a.value2,
row_number() over(order by a.id, a.value2 desc) n
from test a) a) a
start with a.id = b.id
and a.n1 is null
connect by n1 = prior a.n) v2
from (select distinct a.id from test a) b
---------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下:
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
substr(MAX(sys_connect_by_path(a.value2, ';')), 2) q1
From
(Select t.Id,
t.value1,
t.value2,
row_number() over(Partition By Id Order By value2) rn
From test t) a
Start With rn =1
Connect By Prior Id=Id AND a.rn - 1 = PRIOR a.rn
Group By Id
select c.id,c.q1,d.q2 from (
Select Id,substr(MAX(sys_connect_by_path(a.value1, ';')), 2) q1,
From
(Select t.Id,
t.value1,
row_number() over(Partition By Id Order By value1) rn
From test t) a
Start With rn =1
Connect By Prior Id=Id AND a.rn - 1 = PRIOR a.rn
Group By Id) c,
(Select Id,substr(MAX(sys_connect_by_path(a.value2, ';')), 2) q2,
From
(Select t.Id,
t.value2,
row_number() over(Partition By Id Order By value2) rn
From test t) a
Start With rn =1
Connect By Prior Id=Id AND a.rn - 1 = PRIOR a.rn
Group By Id) d
where c.id=d.id