新手求教一个查询结果去重的问题

[wxh1108]

最近项目上客户需要一个报表，大概情况如下，有表A，表B，客户想要结果类似于，如果直接把表A和表B关联的话，第一列和第二列就会有很多重复的，望大神指教怎么能把这些重复的去掉而得到客户想要的结果？

[ghx287524027]

with a as(
select 'A' as id,5 as money from dual
union all
select 'B' as id,7 as money from dual
union all
select 'C' as id,10 as money from dual
),
B AS (
select 'A' as id,2 as money from dual
union all
select 'A' as id,3 as money from dual
union all
select 'B' as id,1 as money from dual
union all
select 'B' as id,2 as money from dual
union all
select 'B' as id,4 as money from dual
union all
select 'C' as id,2 as money from dual
union all
select 'C' as id,3 as money from dual
union all
select 'C' as id,1 as money from dual
)
select case when c.id2 is null then c.id1 else null end id,
case when c.money2 is null then c.money1 else null end money,
c.money from
(select a.id id1,lag(a.id) over(partition by a.id order by a.id nulls last) id2,
a.money money1,lag(a.money) over(partition by a.money order by a.money nulls last) money2,
b.money money
from a left join b on a.id=b.id) c

能实现结果，但不是最优的……

[chengccy]

with a as(
select 'A' as id,5 as money from dual
union all
select 'B' as id,7 as money from dual
union all
select 'C' as id,10 as money from dual
),
B AS (
select 'A' as id,2 as money from dual
union all
select 'A' as id,3 as money from dual
union all
select 'B' as id,1 as money from dual
union all
select 'B' as id,2 as money from dual
union all
select 'B' as id,4 as money from dual
union all
select 'C' as id,2 as money from dual
union all
select 'C' as id,3 as money from dual
union all
select 'C' as id,1 as money from dual
union all
select 'C' as id,1 as money from dual
)
SELECT DECODE(C.R, 1, A.ID) ID, DECODE(C.R, 1, A.MONEY) "金额", C.MONEY "分配金额"
FROM   A
LEFT   JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWNUM) AS R,
                    ID, MONEY
             FROM   B) C
ON     C.ID = A.ID

[ghx287524027]

用这个吧，改进了一下：

select case when c.id2 is null then c.id1 else null end id,
case when c.id2 is null then c.money1 else null end money,
c.money from
(
select a.id id1,lag(a.id) over(partition by a.id order by a.id) id2,
a.money MONEY1,
b.money money
from a left join b on a.id=b.id
) c

[liuzhe_521]

create table t_a as with a as( select 'A' as id,5 as money from dual union all select 'B' as id,7 as money from dual union all select 'C' as id,10 as money from dual ) select * from a; create table t_b as with B AS ( select 'A' as id,2 as money from dual union all select 'A' as id,3 as money from dual union all select 'B' as id,1 as money from dual union all select 'B' as id,2 as money from dual union all select 'B' as id,4 as money from dual union all select 'C' as id,2 as money from dual union all select 'C' as id,3 as money from dual union all select 'C' as id,1 as money from dual ) select * from b; select nvl2(c.rid, a.id, null), nvl2(c.rid, a.money, null), b.money from t_a a left join t_b b on a.id = b.id left join (select min(rowid)/* keep(dense_rank first order by money)*/ rid from t_b group by id) c on b.rowid = c.rid order by a.id, b.rowid