分组，做加减运算

cqhweb 2014-06-20 01:24:57

现有一张表，数据如下：
payacno rcvacno amount
A B 10
A Z 20
C D 30
D C 40
A B 10
B A 50

.....................

得出结果
payacno rcvacno amount
B A 30
A Z 20
D C 40

SQL怎么写？

...全文

385 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

卖水果的net 版主 2014-06-25

打赏
举报

引用 14 楼 wmxcn2000 的回复:


with m as 
(
select 'A' p ,'B' r,10 c union all 
select 'A','Z',20 union all 
select 'C','D',30 union all 
select 'D','C',40 union all 
select 'A','B',10 union all 
select 'B','A',50
),
t as (
select p , r ,c  from m where p> r 
union all
select r , p , -c  from m where p< r 
),
result as 
(select p ,r , SUM(c) cs  from t group by p,r )
select 
case when cs >0 then p else r end as p , 
case when cs <0 then p else r end as p , 
abs(cs) as cs 
from result


p    p    cs
---- ---- -----------
B    A    30
A    Z    20
D    C    10

(3 行受影响)

卖水果的net 版主 2014-06-25

打赏
举报


with m as 
(
select 'A' p ,'B' r,10 c union all 
select 'A','Z',20 union all 
select 'C','D',30 union all 
select 'D','C',40 union all 
select 'A','B',10 union all 
select 'B','A',50
),
t as (
select p , r ,c  from m where p> r 
union all
select r , p , -c  from m where p< r 
),
result as 
(select p ,r , SUM(c) cs  from t group by p,r )
select 
case when cs >0 then p else r end as p , 
case when cs <0 then p else r end as p , 
abs(cs) as cs 
from result

ithhh2800 2014-06-24

打赏
举报

楼主的意思我还是没看懂。详细点可以吗？

极品老土豆 2014-06-20

打赏
举报


 if object_id('tempdb..#a','U') is not null
   drop table #a
   go
   create table #a (payacno char(1),rcvacno char(1),amount tinyint)

   insert into #a(payacno,rcvacno,amount)
           values('A','B',10), 
('A','Z',20), 
('C','D',30),  
('D','C',40), 
('A','B',10), 
('B','A',50);
with a as (
select *,row_number() over(partition by unicode(payacno)+unicode(rcvacno) order by amount desc) as rna
from #a 
),
 b as 
 (
  select distinct payacno,rcvacno,sum(amount)  over(partition by unicode(payacno)+unicode(rcvacno) order by amount desc) as sum_am
  from a where rna <>1
 )
select a.payacno,a.rcvacno,a.amount - isnull(b.sum_am,0)  as amount
from a left outer join b on  (unicode(a.payacno)+unicode(a.rcvacno))-(unicode(b.payacno)+unicode(b.rcvacno))=0
where a.rna = 1

/*
payacno rcvacno amount
------- ------- -----------
B       A       30
D       C       10
A       Z       20

(3 行受影响)

*/

习惯性蹭分 2014-06-20

打赏
举报



declare @tab table(payacno varchar(10),rcvacno varchar(10),amount int)
insert into @tab(payacno,    rcvacno,   amount)
select 'A',                'B',               10  union all
select 'A',                'Z',               20  union all
select 'C',                'D',              30  union all       
select 'D',                'C',              40  union all
select 'A',                'B',               10  union all
select 'B',                'A',               50

;with sel as(
select payacno+rcvacno no1,sum(amount) as [amount], payacno,rcvacno 
from @tab group by payacno,rcvacno 
) 
select a.payacno,a.rcvacno,a.amount-isnull(b.amount,0) as [amount] from sel a
left join sel b on a.no1=reverse(b.no1)
where  a.amount-isnull(b.amount,0)>0

youdan1990 2014-06-20

打赏
举报

--建表 create table test(payacno char(2),rcvacno char(2),amount int) insert into test select 'A','B',10 union all select 'A','Z',20 union all select 'C','D',30 union all select 'D','C',40 union all select 'A','B',10 union all select 'B','A',50; --查询 select te1.payacno 编号P,te1.rcvacno 编号R,isnull(te1.金额,0)-isnull(te2.金额,0) 金额 from (select payacno,rcvacno,LTRIM(RTRIM(payacno+rcvacno)) 编号1,sum(amount) 金额 from test group by payacno,rcvacno) te1 left outer join (select LTRIM(RTRIM(REVERSE(payacno+rcvacno))) 编号2,sum(amount) 金额 from test group by payacno,rcvacno) te2 on te1.编号1=te2.编号2 where isnull(te1.金额,0)-isnull(te2.金额,0)>=0 order by te1.payacno --结果编号P 编号R 金额 ---- ---- ----------- A Z 20 B A 30 D C 10

zhang_yzy 2014-06-20

打赏
举报

select c.payacno, c.rcvacno, c.amout from (select a.payacno, a.rcvacno, nvl((select sum(b.amout) from test111 b where b.payacno = a.payacno and b.rcvacno = a.rcvacno), 0) - nvl((select sum(b.amout) from test111 b where b.payacno = a.rcvacno and b.rcvacno = a.payacno), 0) amout from test111 a group by a.payacno, a.rcvacno) c where c.amout > 0

yoan2014 2014-06-20

打赏
举报

解決sql問題，還得先猜數據的規律

cqhweb 2014-06-20

打赏
举报

数据规律有已经很明显了。

cqhweb 2014-06-20

打赏
举报

第一步：先分组，求和运算。第二步：相反的账号做减法运算。取最大值的一组账号。

lakerskobemvp 2014-06-20

打赏
举报

没看懂表是什么意思

伤痕累累 2014-06-20

打赏
举报


declare @tb table(payacno char(2),rcvacno char(2),amount int)
insert into @tb
select 'A','B',10 union all 
select 'A','Z',20 union all 
select 'C','D',30 union all 
select 'D','C',40 union all 
select 'A','B',10 union all 
select 'B','A',50;
with cte as(
select payacno,rcvacno,sum(amount) as 'amount' from @tb group by payacno,rcvacno
)
select payacno=(case when amount<0 then rcvacno else payacno end),
rcvacno=(case when amount<0 then payacno else rcvacno end),
amount=abs(amount) from (
select payacno,rcvacno,amount=t1.amount-(select isnull(sum(amount),0) from cte t2 where t1.payacno=t2.rcvacno
and t2.payacno=t1.rcvacno) from cte t1 where payacno>rcvacno or not exists (select 1 from cte t2 where t1.payacno=t2.rcvacno
and t2.payacno=t1.rcvacno))t

chen357313771 2014-06-20

打赏
举报

o(╯□╰)o。。木有看懂。。

xdashewan 2014-06-20

打赏
举报

为什么一定是B A而不是A B？

cqhweb 2014-06-20

打赏
举报

重新描述一下。有一张表，数据如下： payacno rcvacno amount A B 10 A Z 20 C D 30 D C 40 A B 10 B A 50 ..................... 得出结果 payacno rcvacno amount B A 30 A Z 20 D C 10 SQL怎么写？