查找异常数据的sql(sql2000)

Question

查找异常数据的sql(sql2000).一个记录商品进出交易数据的表,每次交易前,取得当时库存数,
写入记录中,正常情况下,每笔交易会取得上次交易完成后的库存数,
但由于人为错误,造成部分记录不符合以上要求,需要查找出来并改正.表主要字段:商品编码,交易前库存数,交易数量,交易方向(出或入),交易时间,
即按商品编码+交易时间排序,后一笔记录的交易前库存数应等于
前一笔记录的交易前库存数+(或-)交易数量
示例数据
create table tb_info(spbm varchar(36), kc int,sl int,fx char(1) ,dt datetime )
--其中fx 1为入,增加库存 0为出,减少库存
insert into tb_info(spbm,kc, sl , fx , dt)
select 'A0001',0, 100 , '1' , '2013-08-01 11:00' --结存100
union 
select 'A0001',100, 20 , '0' , '2013-08-01 14:00' --结存80
union 
select 'A0001',80, 50 , '1' , '2013-08-01 14:01'--结存130
union 
select 'A0001',110, 70 , '0' , '2013-08-01 15:01'--此笔记录有问题,交易前库存数应为130,此处为110,需找出此笔记录及该编码后续记录
union 
select 'A0001',40, 60 , '1' , '2013-08-01 18:11'--
union 
select 'A0002',0, 200 , '1' , '2013-08-02 12:01'--
union 
select 'A0002',200, 130 , '0' , '2013-08-02 12:03'----结存70
union 
select 'A0002',70, 80 , '1' , '2013-08-02 13:03'----结存150
union 
select 'A0002',130, 110 , '0' , '2013-08-02 13:03'----此笔记录有问题,交易前库存数应为150,此处为130,需找出此笔记录及后续记录--需要找出有问题的记录第4笔及第9笔,需要更改的记录,第4笔/第5笔/第9笔数据量很大,需要留意性能. [sql2000版本]

Answer 1

先把这些union  连接的语句建成一个视图，这样容易检查问题

Answer 2

数据量大,还是要写一个存储的,帮你写了一个过程,可以参考.--单笔结存
select*,case when fx=1 then kc+sl else kc-sl end as jc  --into #t2
 from #t  order by spbm,dt
 
 --异常表
 if object_id('tempdb..#t3')>0 drop table #t3
 select *,sl as new_kc into #t3 from #t  where 1=2
 
 -- deallocate csr_kc
 
--如记录表有唯一ID,只需定义@ID,@spbm,@kc,@jc 
declare @spbm varchar(20)
declare @kc numeric(12,2)
declare @sl numeric(12,2)
declare @fx int
declare @dt datetime
declare @jc numeric(12,2)
declare @tkc numeric(12,2)
 
--变量
declare @oldspbm varchar(20)
declare @prvjc numeric(12,2)
set @oldspbm=''
set @prvjc=0declare csr_kc cursor for select spbm,kc,sl,fx,dt,jc from #t2 
open csr_kc
fetch next from csr_kc into @spbm,@kc,@sl,@fx,@dt,@jc
while @@fetch_status=0
begin
if @spbm=@oldspbm
begin
if @kc<>@prvjc
begin
--重算结存
set @tkc=@prvjc --以上笔结存做当前库存
set @prvjc=@prvjc+case when @fx=1 then @sl else -@sl end
--这里可改为直接更新实表,最好有唯一字段
insert #t3 values(@spbm,@tkc,@sl,@fx,@dt,@prvjc)
end
else
begin
set @prvjc=@jc
end
end
else
begin
set @oldspbm=@spbm
set @prvjc=@jc --记录当前结存
end
fetch next from csr_kc into @spbm,@kc,@sl,@fx,@dt,@jc
enddeallocate csr_kc
--检查结果
select * from #t order by spbm,dt 
select * from  #t3  order by spbm,dt

Answer 3

select * from tb_info t where  kc<>isnull((select top 1 case when fx=1 then kc+sl when fx=0 
then kc-sl end 
from tb_info where spbm=t.spbm and dt<t.dt order by dt desc),0)只实现功能了。

Answer 4

create table tb_info(spbm varchar(36), kc int,sl int,fx char(1) ,dt datetime )
insert into tb_info(spbm,kc, sl , fx , dt)
select 'A0001',0, 100 , '1' , '2013-08-01 11:00' --结存100
union all  
select 'A0001',100, 20 , '0' , '2013-08-01 14:00' --结存80
union all  
select 'A0001',80, 50 , '1' , '2013-08-01 14:01'--结存130
union all  
select 'A0001',110, 70 , '0' , '2013-08-01 15:01'--此笔记录有问题,交易前库存数应为130,此处为110,需找出此笔记录及该编码后续记录
union all  
select 'A0001',40, 60 , '1' , '2013-08-01 18:11'--
union all  
select 'A0002',0, 200 , '1' , '2013-08-02 12:01'--
union all  
select 'A0002',200, 130 , '0' , '2013-08-02 12:03'----结存70
union all  
select 'A0002',70, 80 , '1' , '2013-08-02 13:03'----结存150
union all  
select 'A0002',130, 110 , '0' , '2013-08-02 14:03'----此笔记录有问题,交易前库存数应为150,此处为130,需找出此笔记录及后续记录
select *,上存=isnull((select sum(case when fx=1 then sl else -sl end) from tb_info b where a.spbm=b.spbm and b.dt<a.dt),0)
,结余=(select sum(case when fx=1 then sl else -sl end) from tb_info c where a.spbm=c.spbm and c.dt<=a.dt)
from tb_info adrop table tb_info
/*
A0001 0 100 1 2013-08-01 11:00:00.000 0 100
A0001 100 20 0 2013-08-01 14:00:00.000 100 80
A0001 80 50 1 2013-08-01 14:01:00.000 80 130
A0001 110 70 0 2013-08-01 15:01:00.000 130 60
A0001 40 60 1 2013-08-01 18:11:00.000 60 120
A0002 0 200 1 2013-08-02 12:01:00.000 0 200
A0002 200 130 0 2013-08-02 12:03:00.000 200 70
A0002 70 80 1 2013-08-02 13:03:00.000 70 150
A0002 130 110 0 2013-08-02 14:03:00.000 150 40*/结果是查询出来了,你想要更新就重新写update语句

Answer 5

select a.spbm as 商品编码
,交易前库存数=isnull((select sum(case when fx=1 then sl else -sl end) from tb_info b where a.spbm=b.spbm and b.dt<a.dt),0)
,a.sl as 交易数量,a.fx as 交易方向
,结存=(select sum(case when fx=1 then sl else -sl end) from tb_info c where a.spbm=c.spbm and c.dt<=a.dt)
,a.dt as 交易时间
from tb_info a
/*
商品编码 交易前库存数 交易数量 交易方向 结存 交易时间
A0001 0 100 1 100 2013-08-01 11:00:00.000
A0001 100 20 0 80 2013-08-01 14:00:00.000
A0001 80 50 1 130 2013-08-01 14:01:00.000
A0001 130 70 0 60 2013-08-01 15:01:00.000
A0001 60 60 1 120 2013-08-01 18:11:00.000
A0002 0 200 1 200 2013-08-02 12:01:00.000
A0002 200 130 0 70 2013-08-02 12:03:00.000
A0002 70 80 1 150 2013-08-02 13:03:00.000
A0002 150 110 0 40 2013-08-02 14:03:00.000
*/这样写看清楚一点

Answer 6

create table tb_info(spbm varchar(36), kc int,sl int,fx char(1) ,dt datetime )
--其中fx 1为入,增加库存 0为出,减少库存
insert into tb_info(spbm,kc, sl , fx , dt)
select 'A0001',0, 100 , '1' , '2013-08-01 11:00' --结存100
union 
select 'A0001',100, 20 , '0' , '2013-08-01 14:00' --结存80
union 
select 'A0001',80, 50 , '1' , '2013-08-01 14:01'--结存130
union 
select 'A0001',110, 70 , '0' , '2013-08-01 15:01'--此笔记录有问题,交易前库存数应为130,此处为110,需找出此笔记录及该编码后续记录
union 
select 'A0001',40, 60 , '1' , '2013-08-01 18:11'--
union 
select 'A0002',0, 200 , '1' , '2013-08-02 12:01'--
union 
select 'A0002',200, 130 , '0' , '2013-08-02 12:03'----结存70
union 
select 'A0002',70, 80 , '1' , '2013-08-02 13:03'----结存150
union 
select 'A0002',130, 110 , '0' , '2013-08-02 13:03'----此笔记录有问题,交易前库存数应为150,此处为130,需找出此笔记录及后续记录--SQL:
;WITH cte AS
(
SELECT rowid = ROW_NUMBER() OVER(PARTITION BY spbm ORDER BY dt), * FROM tb_info
),
cte1 AS
(
SELECT *, 正确值=(SELECT SUM(sl*(CASE fx WHEN 1 THEN 1 ELSE -1 END)) FROM cte B WHERE b.spbm=a.spbm AND b.rowid<a.rowid)
FROM cte A
)
--SELECT * FROM cte1 WHERE 正确值 IS NOT NULL AND kc <> 正确值 --查看错误的记录
UPDATE cte1 
SET kc = 正确值
WHERE 正确值 IS NOT NULL AND kc <> 正确值 --更新出错的记录/*(3 行受影响)*/
SELECT * FROM tb_info
/*
spbm kc sl fx dt
A0001 0 100 1 2013-08-01 11:00:00.000
A0001 60 60 1 2013-08-01 18:11:00.000
A0001 80 50 1 2013-08-01 14:01:00.000
A0001 100 20 0 2013-08-01 14:00:00.000
A0001 130 70 0 2013-08-01 15:01:00.000
A0002 0 200 1 2013-08-02 12:01:00.000
A0002 70 80 1 2013-08-02 13:03:00.000
A0002 150 110 0 2013-08-02 13:03:00.000
A0002 200 130 0 2013-08-02 12:03:00.000
*/