如何实现表记录两两相减？

Question

表结构如下：
create table test
(seq_num int,
 card_no varchar(10),
 quantity decimal(12,2)
)
insert into test select 1,'a',10
insert into test select 2,'a',16
insert into test select 3,'a',5
insert into test select 4,'a',9
insert into test select 5,'a',1
insert into test select 6,'a',4
insert into test select 10,'b',2
insert into test select 11,'b',6要求以card_no分组，按seq_num排序对quantity字段两两相减，
如上例子：针对seq_num字段，2对1相减，4对3相减，6对5相减，11对10相减得出结果应如下：
card_no  quantity1  quantity2  quantity2-quantity1
a        16         10          6(16-10)
a        5          9           4(9-5)
a        1          4           3(4-1)
b        2          6           4(6-2)请教高手如何实现？

Answer 1

card_no  quantity1  quantity2  quantity2-quantity1 
a        16        10          6(16-10) 
a        5          9          4(9-5) 
a        1          4          3(4-1) 
b        2          6          4(6-2) 括号里面的也要吗?6(16-10)

Answer 2

create table test
(seq_num int,
card_no varchar(10),
quantity decimal(12,2)
)
insert into test select 1,'a',10
insert into test select 2,'a',16
insert into test select 3,'a',5
insert into test select 4,'a',9
insert into test select 5,'a',1
insert into test select 6,'a',4
insert into test select 10,'b',2
insert into test select 11,'b',6 select a.card_no,a.quantity,b.quantity,a.quantity-b.quantity from (
select * from(select *,row_number() over(partition by card_no order by seq_num ) as no from test )t where no %2=0 )a,(
select * from(select *,row_number() over(partition by card_no order by seq_num ) as no from test )t where no %2=1 )b
where a.card_no=b.card_no and a.no=b.no+1drop table test

Answer 3

select
aa.card_no,
quantity1 = aa.quantity,
quantity2 = bb.quantity,
[quantity2-quantity1] = aa.quantity - bb.quantity
from(
select
a.card_no,
quantity,
(select count(*) from test where seq_num <= a.seq_num) as row_num
from test a
where (select count(*) from test where seq_num <= a.seq_num) % 2 = 0
)aa
join 
(
select
a.card_no,
quantity,
(select count(*) from test where seq_num <= a.seq_num) as row_num
from test a
where (select count(*) from test where seq_num <= a.seq_num) % 2 = 1
)bb
on bb.row_num = aa.row_num - 1
/**
a 16.00 10.00 6.00
a 9.00 5.00 4.00
a 4.00 1.00 3.00
b 6.00 2.00 4.00
**/

Answer 4

create table test 
(seq_num int, 
card_no varchar(10), 
quantity decimal(12,2) 
) 
insert into test select 1,'a',10 
insert into test select 2,'a',16 
insert into test select 3,'a',5 
insert into test select 4,'a',9 
insert into test select 5,'a',1 
insert into test select 6,'a',4 
insert into test select 10,'b',2 
insert into test select 11,'b',6 
goselect identity(int,1,1) as Id,* into #
from testselect a.card_no,b.quantity ,a.quantity ,b.quantity - a.quantity
from # a
left join # b
on a.card_no = b.card_no and a.id = b.id - 1
where a.id %2 = 1drop table test,#/*
card_no    quantity                                quantity                                
---------- --------------------------------------- --------------------------------------- ---------------------------------------
a          16.00                                   10.00                                   6.00
a          9.00                                    5.00                                    4.00
a          4.00                                    1.00                                    3.00
b          6.00                                    2.00                                    4.00
(4 行受影响)*/

Answer 5

create table test
(seq_num int,
card_no varchar(10),
quantity decimal(12,2)
)
insert into test select 1,'a',10
insert into test select 2,'a',16
insert into test select 3,'a',5
insert into test select 4,'a',9
insert into test select 5,'a',1
insert into test select 6,'a',4
insert into test select 10,'b',2
insert into test select 11,'b',6 SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM TESTSELECT  seq_num  ,   card_no  ,  quantity,quantity2 , quantity2-quantity  AS qty
FROM (
SELECT seq_num  ,   card_no  ,  quantity,quantity2=(SELECT quantity FROM # WHERE ID=A.ID+1)
FROM # AS A
WHERE ID%2=1
) ADROP TABLE #
DROP TABLE test
/*
seq_num     card_no    quantity       quantity2      qty             
----------- ---------- -------------- -------------- --------------- 
1           a          10.00          16.00          6.00
3           a          5.00           9.00           4.00
5           a          1.00           4.00           3.00
10          b          2.00           6.00           4.00
*/

Answer 6

感谢大家的帮忙，不过“ID%2=1”这种并不一定是个好的解决办法。
因为记录并不一定是两两对应的，
比如增加一行：insert into test select 7,'a',9
由于之后没有相对的记录对应，因此得出的结果还是一样的。
可是按“ID%2=1”这种方法去得出如下的错误结果create table test 
(seq_num int, 
card_no varchar(10), 
quantity decimal(12,2) 
) 
insert into test select 1,'a',10 
insert into test select 2,'a',16 
insert into test select 3,'a',5 
insert into test select 4,'a',9 
insert into test select 5,'a',1 
insert into test select 6,'a',4 
insert into test select 7,'a',9（增加的一行） 
insert into test select 10,'b',2 
insert into test select 11,'b',6 
goselect identity(int,1,1) as Id,* into #
from testselect a.card_no,b.quantity ,a.quantity ,b.quantity - a.quantity
from # a
left join # b
on a.card_no = b.card_no and a.id = b.id - 1
where a.id %2 = 1drop table test,#
得出的结果就不对了
a 16.00 10.00 6.00
a 9.00 5.00 4.00
a 4.00 1.00 3.00
a NULL 9.00 NULL
b NULL 6.00 NULL

Answer 7

这样?
create table test
(seq_num int,
card_no varchar(10),
quantity decimal(12,2)
)
insert into test select 1,'a',10
insert into test select 2,'a',16
insert into test select 3,'a',5
insert into test select 4,'a',9
insert into test select 5,'a',1
insert into test select 6,'a',4
insert into test select 7,'a',9
insert into test select 10,'b',2
insert into test select 11,'b',6 SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM TEST
SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE card_no<a.card_no)  INTO #T FROM # AS ASELECT  seq_num  ,   card_no  ,  quantity,quantity2 , quantity2-quantity  AS qty
FROM (
SELECT seq_num  ,   card_no  ,  quantity,quantity2=(SELECT quantity FROM #T WHERE CARD_NO=A.CARD_NO AND ID=A.ID+1)
FROM #T AS A
WHERE SEQ%2=1
) ADROP TABLE #
DROP TABLE #T
DROP TABLE test
/*
seq_num     card_no    quantity       quantity2      qty             
----------- ---------- -------------- -------------- --------------- 
1           a          10.00          16.00          6.00
3           a          5.00           9.00           4.00
5           a          1.00           4.00           3.00
7           a          9.00           NULL           NULL
10          b          2.00           6.00           4.00
*/

Answer 8

create table test 
(seq_num int, 
card_no varchar(10), 
quantity decimal(12,2) 
) 
insert into test select 1,'a',10 
insert into test select 2,'a',16 
insert into test select 3,'a',5 
insert into test select 4,'a',9 
insert into test select 5,'a',1 
insert into test select 6,'a',4 
insert into test select 7,'a',9
insert into test select 10,'b',2 
insert into test select 11,'b',6 
--去掉无配对的记录
select identity(int,1,1) as Id,* into # 
from test a
where (select count(*) from test where card_no=a.card_no and seq_num<a.seq_num)%2=0 and  exists(select 1 from test where card_no=a.card_no and seq_num>a.seq_num)
or    (select count(*) from test where card_no=a.card_no and seq_num<a.seq_num)%2=1 and  exists(select 1 from test where card_no=a.card_no and seq_num<a.seq_num)
--相减
select a.card_no,b.quantity ,a.quantity ,[b.quantity - a.quantity]=b.quantity - a.quantity 
from # a 
left join # b 
on a.card_no = b.card_no and a.id = b.id - 1 
where a.id %2 = 1
/*
card_no    quantity                                quantity                                b.quantity - a.quantity
---------- --------------------------------------- --------------------------------------- ---------------------------------------
a          16.00                                   10.00                                   6.00
a          9.00                                    5.00                                    4.00
a          4.00                                    1.00                                    3.00
b          6.00                                    2.00                                    4.00(4 row(s) affected)
*/

Answer 9

create table test 
(seq_num int, 
card_no varchar(10), 
quantity decimal(12,2) 
) 
insert into test select 1,'a',10 
insert into test select 2,'a',16 
insert into test select 3,'a',5 
insert into test select 4,'a',9 
insert into test select 5,'a',1 
insert into test select 6,'a',4 
insert into test select 7,'a',9
insert into test select 10,'b',2 
insert into test select 11,'b',6 
go
/*
select identity(int,1,1) as Id,* into #
from testselect a.card_no,b.quantity ,a.quantity ,b.quantity - a.quantity
from # a
left join # b
on a.card_no = b.card_no and a.id = b.id - 1
where a.id %2 = 1,#
*/
;with t
as(
select *,id = row_number() over (partition by card_no order by card_no,seq_num)
from test
)
select a.card_no,isnull(b.quantity,0) as quantity ,a.quantity ,isnull(b.quantity,0) - a.quantity
from t a
left join t b
on a.card_no = b.card_no and a.id = b.id - 1
where a.id %2 = 1drop table test
/*
---------- --------------------------------------- --------------------------------------- ---------------------------------------
a          16.00                                   10.00                                   6.00
a          9.00                                    5.00                                    4.00
a          4.00                                    1.00                                    3.00
a          0.00                                    9.00                                    -9.00
b          6.00                                    2.00                                    4.00(5 行受影响)
*/

Answer 10

if object_id('test') is not null
   drop table test
create table test
(seq_num int,
card_no varchar(10),
quantity decimal(12,2)
)
insert into test select 1,'a',10
insert into test select 2,'a',16
insert into test select 3,'a',5
insert into test select 4,'a',9
insert into test select 5,'a',1
insert into test select 6,'a',4
insert into test select 10,'b',2
insert into test select 11,'b',6 
--select * from test
select a.card_no,b.quantity as quantity1,
a.quantity as quantity2,b.quantity-a.quantity as 'quantity2-quantity1' 
 from(select * from
(select *,row_number()over(partition by card_no order by seq_num) as 序号 from test) a where 序号%2=1) a join
(select * from (select *,row_number()over(partition by card_no order by seq_num) as 序号 from test)b where 序号%2=0) b 
on a.序号=b.序号-1 and a.card_no=b.card_no

Answer 11

select card_no,quantity1,quantity2,isnull(quantity2-quantity1,0) as [quantity2-quantity1]
from   (select card_no ,quantity1= case (select count(*) from test where card_no<a.card_no) %2 
                              when 1 then case  seq_num %2 when 0 then quantity end 
                              when 0 then case  seq_num %2 when 1 then quantity end 
                           end,
                quantity2= case (select count(*) from test where card_no<a.card_no) %2 
                              when 1 then case  seq_num %2 when 0 then (select quantity  from test where seq_num=a.seq_num+1) end 
                              when 0 then case  seq_num %2 when 1 then (select quantity  from test where seq_num=a.seq_num+1) end 
                           end
    from test a) K
where quantity1 is not null结果：
a 10.00 16.00 6.00
a 5.00 9.00 4.00
a 1.00 4.00 3.00
a 9.00 NULL .00
b 2.00 6.00 4.00
说明： 14楼i 使用临时表
       15楼：把不匹配的（第七行过滤了）
       16、17楼貌似是2005里面的
       偶的2000的，什么都没用，就单纯用case来实现

Answer 12

card_no    quantity1                               quantity2                               quantity2-quantity1
---------- --------------------------------------- --------------------------------------- ---------------------------------------
a          10.00                                   16.00                                   6.00
a          5.00                                    9.00                                    4.00
a          1.00                                    4.00                                    3.00
b          6.00                                    NULL                                    0.00用原数据就不对了。