如何写SQL语句查询表a丢失记录?

Question

表a:
id  cardno  消费金额  存款金额  卡余额 消费时间           记录号
106 0003    2         0          48     2008-01-03 07:30   28
396 0001    5         0          45     2008-01-03 08:00   25
546 0002    3         0          47     2008-01-03 12:20   16
399 0001    10         0         35     2008-01-04 11:30   26
556 0002    4         0          43     2008-01-04 17:10   17
186 0003    8         0          40     2008-01-04 18:00   29
198 0003    3         0          137    2008-01-05 12:40   31
579 0002    3         0          40     2008-01-05 16:45   18
401 0001    5         0          80     2008-01-06 17:00   28
201 0003    12         0         125    2008-01-06 18:10   32
580 0002    5         0          35     2008-01-06 19:00   19
423 0001    1         0          79     2008-01-08 13:00   29
如何写SQL语句查询出丢失记录的流水:结果如下:cardno  丢失记录号  丢失金额
0001      27         50
0003      30         100
另外如何写SQL语句插入这些丢失记录回表a?丢失流水记录ID和消费时间可以取丢失区间任意ID和消费时间.

Answer 1

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2 
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/

Answer 2

0001      27         50 
0003      30         100 ----------丢失的27和30金额怎么知道，cardno哪来~

Answer 3

根据cardno对应的丢失"记录号"区间的"卡余额"得出.

Answer 4

根据cardno对应的丢失"记录号"区间的"卡余额"得出.

Answer 5

--前提，中间每次只能掉一个号，如果连续掉两个号就麻烦了。
create table tb(id int, cardno varchar(10) , 消费金额 int, 存款金额 int, 卡余额 int,消费时间 datetime,记录号 int)
insert into tb values(106, '0003',    2  ,       0 ,         48 ,    '2008-01-03 07:30' ,  28 )
insert into tb values(396, '0001',    5  ,       0 ,         45 ,    '2008-01-03 08:00' ,  25 )
insert into tb values(546, '0002',    3  ,       0 ,         47 ,    '2008-01-03 12:20' ,  16 )
insert into tb values(399, '0001',    10 ,       0 ,         35 ,    '2008-01-04 11:30' ,  26 )
insert into tb values(556, '0002',    4  ,       0 ,         43 ,    '2008-01-04 17:10' ,  17 )
insert into tb values(186, '0003',    8  ,       0 ,         40 ,    '2008-01-04 18:00' ,  29 )
insert into tb values(198, '0003',    3  ,       0 ,         137,    '2008-01-05 12:40' ,  31 )
insert into tb values(579, '0002',    3  ,       0 ,         40 ,    '2008-01-05 16:45' ,  18 )
insert into tb values(401, '0001',    5  ,       0 ,         80 ,    '2008-01-06 17:00' ,  28 )
insert into tb values(201, '0003',    12 ,       0 ,         125,    '2008-01-06 18:10' ,  32 )
insert into tb values(580, '0002',    5  ,       0 ,         35 ,    '2008-01-06 19:00' ,  19 )
insert into tb values(423, '0001',    1  ,       0 ,         79 ,    '2008-01-08 13:00' ,  29 )
goselect m.cardno , 丢失金额 = n.卡余额 - (m.卡余额 - n.消费金额) from
(select * , px = (select count(*) from tb where cardno = t.cardno and 记录号 < t.记录号) + 1 from tb t) m,
(select * , px = (select count(*) from tb where cardno = t.cardno and 记录号 < t.记录号) + 1 from tb t) n
where m.cardno = n.cardno and m.px = n.px - 1 and m.记录号 <> n.记录号 - 1
order by m.cardnodrop table tb/*
cardno     丢失金额        
---------- ----------- 
0001       50
0003       100（所影响的行数为 2 行）
*/

Answer 6

create table tb(id int, cardno varchar(10) , 消费金额 int, 存款金额 int,
 卡余额 int,消费时间 datetime,记录号 int)
insert into tb values(106, '0003',    2  ,       0 ,         48 ,    '2008-01-03 07:30' ,  28 )
insert into tb values(396, '0001',    5  ,       0 ,         45 ,    '2008-01-03 08:00' ,  25 )
insert into tb values(546, '0002',    3  ,       0 ,         47 ,    '2008-01-03 12:20' ,  16 )
insert into tb values(399, '0001',    10 ,       0 ,         35 ,    '2008-01-04 11:30' ,  26 )
insert into tb values(556, '0002',    4  ,       0 ,         43 ,    '2008-01-04 17:10' ,  17 )
insert into tb values(186, '0003',    8  ,       0 ,         40 ,    '2008-01-04 18:00' ,  29 )
insert into tb values(198, '0003',    3  ,       0 ,         137,    '2008-01-05 12:40' ,  31 )
insert into tb values(579, '0002',    3  ,       0 ,         40 ,    '2008-01-05 16:45' ,  18 )
insert into tb values(401, '0001',    5  ,       0 ,         80 ,    '2008-01-06 17:00' ,  28 )
insert into tb values(201, '0003',    12 ,       0 ,         125,    '2008-01-06 18:10' ,  32 )
insert into tb values(580, '0002',    5  ,       0 ,         35 ,    '2008-01-06 19:00' ,  19 )
insert into tb values(423, '0001',    1  ,       0 ,         79 ,    '2008-01-08 13:00' ,  29 )
goselect px=(select count(1) from tb where cardno = a.cardno and id < a.id),* 
into #
from tb a
order by cardno,记录号select a.cardno,a.记录号 +  1 as 记录号 ,c.卡余额 - a.卡余额 + c.消费金额 as 丢失
from # a left join # c on a.px = c.px - 1 and a.cardno = c.cardno
where exists(select 1 from # b where a.记录号 <> b.记录号 - 1 
                 and a.px = b.px - 1 and a.cardno = b.cardno)drop table tb,#
/*
cardno     记录号         丢失          
---------- ----------- ----------- 
0001       27          50
0003       30          100（所影响的行数为 2 行）
*/

Answer 7

/*
如何写SQL语句查询出丢失记录的流水:结果如下:cardno  丢失记录号  丢失金额
0001      27         50
0003      30         100
另外如何写SQL语句插入这些丢失记录回表a?丢失流水记录ID和消费时间可以取丢失区间任意ID和消费时间.
*/CREATE TABLE #tb(id int,  cardno varchar(10),  消费金额 int, 存款金额 int, 卡余额 int, 消费时间 datetime, 记录号 int)
INSERT #tb SELECT 106, '0003',    2,        0,          48,     '2008-01-03 07:30',   28
UNION ALL SELECT 396, '0001',    5,        0,          45,     '2008-01-03 08:00',   25
UNION ALL SELECT 546, '0002',    3,        0,          47,     '2008-01-03 12:20',   16
UNION ALL SELECT 399, '0001',    10,         0,         35,     '2008-01-04 11:30',   26
UNION ALL SELECT 556, '0002',    4,        0,          43,     '2008-01-04 17:10',   17
UNION ALL SELECT 186, '0003',    8,        0,          40,     '2008-01-04 18:00',   29
UNION ALL SELECT 198, '0003',    3,        0,          137,    '2008-01-05 12:40',   31
UNION ALL SELECT 579, '0002',    3,        0,          40,     '2008-01-05 16:45',   18
UNION ALL SELECT 401, '0001',    5,        0,          80,     '2008-01-06 17:00',   28
UNION ALL SELECT 201, '0003',    12,         0,         125,    '2008-01-06 18:10',   32
UNION ALL SELECT 580, '0002',    5,        0,          35,     '2008-01-06 19:00',   19
UNION ALL SELECT 423, '0001',   1,        0,          79,     '2008-01-08 13:00',   29
GO
select a.cardno,(a.记录号-1)as 丢失记录号,(a.卡余额+a.消费金额-b.卡余额)as 丢失金额 
from #tb as a,#tb as b 
where a.cardno=b.cardno 
and (a.记录号-b.记录号)>1 
and a.消费时间>b.消费时间
and (b.记录号+1) not in (select 记录号 from #tb) 
and (a.记录号-1) not in (select 记录号 from #tb)drop table #tb/*
0001 27 50
0003 30 100
*/

Answer 8

这个1条sql能搞定，不用子查询了，呵呵

Answer 9

这样的前提是 表还是正常的时候就要添加tid