例如表A
pk c key
1 1 1
2 2
表B
pk c amount
1 1 10
2 2 20
3 4 30想关联A表 查询 B表的金额,条件是a.c = b.c
当a.c不为空时,则查出b.c=a.c ,即B表中 c=1 的
当a.c为空时,则不按c过滤,查出所有,即B表中 c=1, c=2 , c=4 的都查出来sql大概是这样的select sum(b.amount) from B b right join A a on b.c = a.c但是on后面的条件 需要怎么改一下 才符合我的需求呢?高手指点下~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pk c key
1 1 1
2 2
表B
pk c amount
1 1 10
2 2 20
3 4 30想关联A表 查询 B表的金额,条件是a.c = b.c
当a.c不为空时,则查出b.c=a.c ,即B表中 c=1 的
当a.c为空时,则不按c过滤,查出所有,即B表中 c=1, c=2 , c=4 的都查出来sql大概是这样的select sum(b.amount) from B b right join A a on b.c = a.c但是on后面的条件 需要怎么改一下 才符合我的需求呢?高手指点下~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
then
sum(b.amount)over(partition by b.c)
else
sum(b.amount)over(order by a.c)
end as amount from
(
select 1 pk ,1 c,1 key from dual
)a,
(select 1 pk ,1 c,10 amount from dual
union all
select 2 pk ,2 c,20 amount from dual
union all
select 3 pk ,4 c,30 amount from dual)b
where b.c = a.c(+)
--测试数据
create table t1(pk int,c int,key int)
create table t2(pk int,c int,amount int)
begin
insert into t1 values(1,1,1);
insert into t1(pk,c) values(2,2);
insert into t2 values(1,1,10);
insert into t2 values(2,2,20);
insert into t2 values(3,4,30);
end;
--语句
select b.c,nvl2(a.c,b.amount,(select sum(amount) from t2)) from t1 a right join t2 b on a.c=b.c
--结果
1 10
2 20
4 60
with a as(select 1 pk, 1 c, 1 key from dual
union all select 2,2,null from dual)
, b as (select 1 pk, 1 c,10 amount from dual
union all select 2,2,20 from dual
union all select 3,4,30 from dual
union all select 4,5,30 from dual)
select b.c,
(case when a.pk is not null then sum(b.amount)over(partition by b.c order by b.pk)
else (select sum(amount) from b) end) sumamount
from a
right join b
on b.c = a.c试试
UNION ALL SELECT 2,2,NULL FROM DUAL),
B AS (SELECT 1 PK, 1 C,10 AMOUNT FROM DUAL
UNION ALL SELECT 2,2,20 FROM DUAL
UNION ALL SELECT 3,4,30 FROM DUAL)
SELECT B.PK,B.C,SUM(B.AMOUNT) FROM B,A WHERE B.C = A.C(+) GROUP BY B.PK,B.C;