请教一个SQL

gpz520 2009-11-25 11:26:38

表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

...全文

108 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

gpz520 2009-11-26

打赏
举报

不过还试谢谢你先睡觉吧晚安

gpz520 2009-11-26

打赏
举报

如果再插入一条记录 A C 7 最终结果是这样的才对
人员人员自己借出收入借出-收入
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

crazylaa 2009-11-26

打赏
举报

看来是搞错了，嘿嘿。

gpz520 2009-11-26

打赏
举报

[Quote=引用 3 楼 crazylaa 的回复:]
你在test表中再加一行记录
A C 7
[/Quote]

运行出来是什么结果啊

crazylaa 2009-11-26

打赏
举报

oracle 不知道有没有怎么判断两个字符串是否相等的函数.自己写了一个。如果没有，那我这个sql不算了。。。

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          0



SQL> select * from test

  2  ;



T1                   T2                           T3

-------------------- -------------------- ----------

A                    B                            10

B                    A                             5

C                    C                             6

gpz520 2009-11-26

打赏
举报

已经差不多了中午12：10结贴我现在试试谢谢大家

liusong_china 2009-11-26

打赏
举报

[Quote=引用 14 楼 liusong_china 的回复:]
SQL codeselect 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) "借出-收入"from test a,(select T2 T1,T1?-
[/Quote]
没看仔细，改正一下。。

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) "借出-收入"

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;

xiyunfang 2009-11-26

打赏
举报

关注中。。。

liusong_china 2009-11-26

打赏
举报

[Quote=引用 6 楼 gpz520 的回复:]
如果再插入一条记录 A C 7 最终结果是这样的才对
人员人员自己借出收入借出-收入
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

[/Quote]

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

liusong_china 2009-11-26

打赏
举报

08:56:51 scott@TUNGKONG> with test as

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

liusong_china 2009-11-26

打赏
举报

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) "借出-收入"

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;

小灰狼W 2009-11-26

打赏
举报

....

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

iqlife 2009-11-26

打赏
举报

你们都是神啊,半夜还在

junzi354 2009-11-26

打赏
举报

关注下

crazylaa 2009-11-26

打赏
举报

SQL> select c.t1 人员,c.t2 人员,c.self 自己,c.borrow 借出,d.income 收入,c.borrow-d.income 借出收入

  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行。

crazylaa 2009-11-26

打赏
举报



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