表test 如下
T1 T2 T3
A B 10
B A 5
C C 6
业务是这样的
A借给B 10块
B借给A 5块
至于C就当自己给自己6块最后要查出下面的结果，sql怎么样写，给个思路也行
人员 人员 自己 借出 收入 借出-收入
A B 0 10 5 5
B A 0 5 10 -5
C C 6 0 0 0
T1 T2 T3
A B 10
B A 5
C C 6
业务是这样的
A借给B 10块
B借给A 5块
至于C就当自己给自己6块最后要查出下面的结果，sql怎么样写，给个思路也行
人员 人员 自己 借出 收入 借出-收入
A B 0 10 5 5
B A 0 5 10 -5
C C 6 0 0 0
抛砖引玉阿 。
create or replace function isequal
(
var1 in varchar2,
var2 in varchar2
)
return number as
begin
if(var1 is null and var2 is null or var1 is not null and var2 is not null and var1 = var2) then
return 1;
else
return 0;
end if;
end;/
SQL> select a.t1 人员,a.t2 人员,a.self 自己,a.borrow 借出,b.income 收入,a.borrow-b.income 借出收入
2 from
3 (select t1,t2,sum(decode(isequal(test.t1,test.t2),1,t3,0)) self,
4 sum(decode(isequal(test.t1,test.t2),1,0,t3)) borrow from test group by t1,t2 ) a,
5 (select t2,t1,sum(decode(isequal(test.t1,test.t2),1,0,t3)) income from test group by t1,t2 )b
6 where a.t1=b.t2 and a.t2=b.t1
7 order by a.t1;人员 人员 自己 借出 收入 借出收入
-------------------- -------------------- ---------- ---------- ---------- ----------
A B 0 10 5 5
B A 0 5 10 -5
C C 6 0 0 0SQL> select * from test
2 ;T1 T2 T3
-------------------- -------------------- ----------
A B 10
B A 5
C C 6
人员 人员 自己 借出 收入 借出-收入
A B 0 10 5 5
B A 0 5 10 -5
C C 6 0 0 0
A C 0 7 0 7
C A 0 0 7 -7
SQL> select * from test;T1 T2 T3
-------------------- -------------------- ----------
A B 10
B A 5
C C 6
A C 7
D D 10
E A 100
2 from
3 (
4 --A->B且B->A
5 select t1,t2,0 self,
6 sum(t3) borrow from test where t1<>t2
7 and exists (select 1 from test b where b.t1||b.t2 = test.t2||test.t1)
8 group by t1,t2
9 union
10 --A->B无B->A
11 select t1,t2,0 self,
12 sum(t3) borrow from test where t1<>t2
13 and not exists (select 1 from test b where b.t1||b.t2 = test.t2||test.t1)
14 group by t1,t2
15 union
16 --A->B无B->A的造个B->A
17 select t2 as t1,t1 as t2,0 self,
18 0 borrow from test where t1<>t2
19 and not exists (select 1 from test b where b.t1||b.t2 = test.t2||test.t1)
20 group by t1,t2
21 union
22 --C->C
23 select t2 as t1,t1 as t2,sum(t3) self,
24 0 borrow from test where t1=t2
25 group by t1,t2
26 ) c,
27 (
28 --A->B且B->A
29 select t1,t2,
30 sum(t3) income from test where t1<>t2
31 and exists (select 1 from test b where b.t1||b.t2 = test.t2||test.t1)
32 group by t1,t2
33 union
34 --A->B无B->A
35 select t1,t2,
36 sum(t3) income from test where t1<>t2
37 and not exists (select 1 from test b where b.t1||b.t2 = test.t2||test.t1)
38 group by t1,t2
39 union
40 --A->B无B->A的造个B->A
41 select t2 as t1,t1 as t2,
42 0 income from test where t1<>t2
43 and not exists (select 1 from test b where b.t1||b.t2 = test.t2||test.t1)
44 group by t1,t2
45 union
46 --C->C
47 select t2 as t1,t1 as t2,
48 0 income from test where t1=t2
49 group by t1,t2
50 ) d
51 where c.t1=d.t2 and c.t2=d.t1
52 order by c.t1;人员 人员 自己 借出 收入 借出收入
-------------------- -------------------- ---------- ---------- ---------- ----------
A B 0 10 5 5
A C 0 7 0 7
A E 0 0 100 -100
B A 0 5 10 -5
C C 6 0 0 0
C A 0 0 7 -7
D D 10 0 0 0
E A 0 100 0 100已选择8行。
with test as(select 'A' t1,'B't2,10 T3 from dual
union all select 'B','A',5 from dual
union all select 'C','C',6 from dual
union all select 'A','C',7 from dual)
select nvl(a.t1,b.t2)人员,nvl(a.t2,b.t1)人员,
0 自己,nvl(a.t3,0)借出,nvl(b.t3,0)收入,
nvl(a.t3,0)-nvl(b.t3,0) "借出-收入"
from (select t1,t2,sum(t3)t3 from test where t1<>t2 group by t1,t2)a full join
(select t1,t2,sum(t3)t3 from test where t1<>t2 group by t1,t2) b
on a.t1=b.t2 and a.t2=b.t1
union all
select t1,t2,sum(t3),0,0,0
from test where t1=t2
group by t1,t2人员 人员 自己 借出 收入 借出-收入
A B 0 10 5 5
C A 0 0 7 -7
B A 0 5 10 -5
A C 0 7 0 7
C C 6 0 0 0
from test a,(select T2 T1,T1 T2,T3 from test) b
where a.T1 = b.T1 and a.T2 = b.T2
order by a.T1;
08:56:58 2 (select 'A' T1,'B' T2,10 T3 from dual
08:56:58 3 union all
08:56:58 4 select 'B','A',5 from dual
08:56:58 5 union all
08:56:58 6 select 'C','C',6 from dual)
08:56:58 7 select a.T1 人员,a.T2 人员,decode(a.T1,a.T2,a.T3,0) 自己,decode(a.T1,a.T2,0,a.T3) 借出,decode(b.T1,b.T2,0,b.T3) 收入,decode(a.T1,a.T2,0,a.T3)-decode(b.T1,b.T2,0,b.T3) "借出-收入"
08:56:58 8 from test a,(select T2 T1,T1 T2,T3 from test) b
08:56:58 9 where a.T1 = b.T1 and a.T2 = b.T2
08:56:58 10 order by a.T1;人员 人员 自己 借出 收入 借出-收入
---------- ---------- ---------- ---------- ---------- ----------
A B 0 10 5 5
B A 0 5 10 -5
C C 6 0 0 0已用时间: 00: 00: 00.04
09:12:01 scott@TUNGKONG> with test as
09:12:08 2 (select 'A' T1,'B' T2,10 T3 from dual
09:12:08 3 union all
09:12:08 4 select 'B','A',5 from dual
09:12:08 5 union all
09:12:08 6 select 'C','C',6 from dual
09:12:08 7 union all
09:12:08 8 select 'A','C',7 from dual)
09:12:08 9 select nvl(a.T1,b.T1) 人员,nvl(a.T2,b.T2) 人员,decode(nvl(a.T1,b.T1),nvl(a.T2,b.T2),a.T3,0) 自己,decode(a.T1,a.T2,0,a.T3) 借出,decode(b.T1,b.T2,0,b.T3) 收入,decode(a.T1,a.T2,0,a.T3)-decode(b.T1,b.T2,0,b.T3) "借出-收入"
09:12:08 10 from test a full join (select T2 T1,T1 T2,T3 from test) b
09:12:08 11 on a.T1 = b.T1 and a.T2 = b.T2
09:12:08 12 order by a.T1;人员 人员 自己 借出 收入 借出-收入
---------- ---------- ---------- ---------- ---------- ----------
A B 0 10 5 5
A C 0 7 0 7
B A 0 5 10 -5
C C 6 0 0 0
C A 0 0 7 -7已用时间: 00: 00: 00.01
from test a full join (select T2 T1,T1 T2,T3 from test) b
on a.T1 = b.T1 and a.T2 = b.T2
order by a.T1;