A表
油品名称 日期 期末库存 罐号
油a 2006.2.2 100 m25
油a 2006.2.2 150 n23
油b 2006.2.6 80 k23
油b 2006.2.6 130 t58
油b 2006.2.6 90 f823
油b 2006.2.7 130 fh88
得到结果
油品名称 日期 期末库存 罐号
油a 2006.2.2 250 m25/n23
油b 2006.2.6 300 k23/t58/f823
油b 2006.2.7 130 fh88
油品名称 日期 期末库存 罐号
油a 2006.2.2 100 m25
油a 2006.2.2 150 n23
油b 2006.2.6 80 k23
油b 2006.2.6 130 t58
油b 2006.2.6 90 f823
油b 2006.2.7 130 fh88
得到结果
油品名称 日期 期末库存 罐号
油a 2006.2.2 250 m25/n23
油b 2006.2.6 300 k23/t58/f823
油b 2006.2.7 130 fh88
from table a,table b
where a.油品名称=b.油品名称 and a.日期=b.日期
table1:
ID fld1
--------------------
1 A
2 B
table2:
ID fld2
-------------------
1 AAA
1 ABB
1 ABC
2 BBB
2 BBC
想查询出这样的结果集:
ID fld1 fld2
-------------------------
1 A AAA;ABB;ABC
2 B BBB;BBC
*/
drop table t1;
drop table t2;
create table t1
(id int,
fld1 varchar2(10)
) /
create table t2
(id int,
fld2 varchar2(10)
) /
insert into t1 values(1,'A');
insert into t1 values(2,'B');
insert into t2 values(1,'AAA');
insert into t2 values(1,'ABB');
insert into t2 values(1,'ABC');
insert into t2 values(2,'BBB');
insert into t2 values(2,'BBC');
select * from t1;
select * from t2;
with my_q as
(select id, substr(max(sys_connect_by_path(fld2, ';')),2,1000) fld2
from (select id, fld2, lag(fld2) over(partition by id order by fld2) l_opt
from t2)
start with l_opt is null
connect by id = prior id and prior fld2 = l_opt
group by id)
select t1.id,t1.fld1,my_q.fld2 from t1,my_q where t1.id=my_q.id
---------- ---------- ---------- ----------
油a 2006.2.2 100 m25
油a 2006.2.2 150 n23
油b 2006.2.6 80 k23
油b 2006.2.6 130 t58
油b 2006.2.6 90 f823
油b 2006.2.7 130 fh88已选择6行。SQL> Select 油品名称, 日期, Sum(期末库存) 期末库存,
2 Substr((Select Max(Sys_Connect_By_Path(罐号, '/'))
3 From (Select Row_Number
() Over(Partition By 油品名称, 日期 Order By 油品名称, 日期) Rid,
4
Row_Number() Over(Partition By 油品名称, 日期 O
rder By 油品名称, 日期) + 1 Pid, Ta.*
5
From (Select * From Ta Order By 油品名称, 日期, 罐号) Ta) b
6 Where b.油品名称 = a.油
品名称 And b.日期 = a.日期
7 Start With Rid = 1
8 Connect By Prior Pid =
Rid), 2) 罐号
9 From Ta a
10 Group By 油品名称, 日期;油品名称 日期 期末库存
---------- ---------- ----------
罐号
--------------------------------------------------------------------------------油a 2006.2.2 250
m25/n23油b 2006.2.7 130
fh88油b 2006.2.6 300
m25/n23/t58