搞不懂思路listagg
来源:互联网 发布:天云大数据 编辑:程序博客网 时间:2024/06/04 22:16
create table t_a (
c1 integer ,
c2 integer,
c3 integer,
sub_id_a integer
)
;
create table t_b (
sub_id_b integer,
part_num varchar(10)
)
;
insert into t_a values(1, 2, 3, 4 ),(1, 2 ,2 ,4 ),(2, 3, 4, 5);
insert into t_b values(4 ,'ef'),(4, 'ed' ),(5, 'eg');
insert into t_b values(5, 'eg');
select * from t_b ;
select a.*,
(select listagg(part_num ,',') from t_b where sub_id_a =sub_id_b)
from t_a a ;
C1 C2 C3 SUB_ID_A 5
-- -- -- -------- -----
1 2 3 4 ef,ed
1 2 2 4 ef,ed
2 3 4 5 eg
;
select a.*,
listagg(part_num ,',')
from t_a a ,t_b
where sub_id_a =sub_id_b
group by a.c1,a.c2,a.c3,a.sub_id_a;
C1 C2 C3 SUB_ID_A 5
-- -- -- -------- -----
1 2 2 4 ef,ed
1 2 3 4 ef,ed
2 3 4 5 eg
with temp as (
select a.*,
part_num
from t_a a ,t_b
where sub_id_a =sub_id_b
)
select c1,c2,c3 ,sub_id_a,
listagg(part_num,',') within group (order by part_num )as part_num
from temp
group by c1,c2,c3 ,sub_id_a
with ur ;
C1 C2 C3 PART_NUM
-- -- -- --------
1 2 2 ef,ed
1 2 3 ef,ed
2 3 4 eg
;
c1 integer ,
c2 integer,
c3 integer,
sub_id_a integer
)
;
create table t_b (
sub_id_b integer,
part_num varchar(10)
)
;
insert into t_a values(1, 2, 3, 4 ),(1, 2 ,2 ,4 ),(2, 3, 4, 5);
insert into t_b values(4 ,'ef'),(4, 'ed' ),(5, 'eg');
insert into t_b values(5, 'eg');
select * from t_b ;
select a.*,
(select listagg(part_num ,',') from t_b where sub_id_a =sub_id_b)
from t_a a ;
C1 C2 C3 SUB_ID_A 5
-- -- -- -------- -----
1 2 3 4 ef,ed
1 2 2 4 ef,ed
2 3 4 5 eg
;
select a.*,
listagg(part_num ,',')
from t_a a ,t_b
where sub_id_a =sub_id_b
group by a.c1,a.c2,a.c3,a.sub_id_a;
C1 C2 C3 SUB_ID_A 5
-- -- -- -------- -----
1 2 2 4 ef,ed
1 2 3 4 ef,ed
2 3 4 5 eg
with temp as (
select a.*,
part_num
from t_a a ,t_b
where sub_id_a =sub_id_b
)
select c1,c2,c3 ,sub_id_a,
listagg(part_num,',') within group (order by part_num )as part_num
from temp
group by c1,c2,c3 ,sub_id_a
with ur ;
C1 C2 C3 PART_NUM
-- -- -- --------
1 2 2 ef,ed
1 2 3 ef,ed
2 3 4 eg
;
0 0
- 搞不懂思路listagg
- 搞不懂.....
- 搞不懂
- 搞不懂
- 搞不懂
- 搞不懂
- 搞不懂的ubuntu
- 搞不懂自己
- SCM???搞不懂
- 搞不懂的问题
- 真的搞不懂了
- 始终搞不懂的问题!!!
- 搞不懂怎么有积分
- 一直搞不懂的 sigpromask ()
- 搞不懂的U-boot
- 翻译---搞不懂的Metrics
- 搞不懂你的套路
- 刚学链表,有点搞不懂,求教
- 开始使用 Docker
- Compile Assimp Open Source Library For Android Compile Assimp Open Source Library For Android
- 算法导论-------快速排序QuickSort
- 微信小程序将引爆手机屏幕的核战争?
- Java移位操作
- 搞不懂思路listagg
- 将tomcat欢迎页面修改为自己项目的主页
- Check the difficulty of problems poj 2151 dp
- view中的 translationx、x、rawx、scrollx、left
- 快速幂取模(转载+自己理解)
- java 实现几种常见排序方法
- 【校园打杂】css中botton按钮变色
- IntelliJ idea 如果打开左边项目展开栏
- 常用命令总结