查找异常数据的sql(sql2000)

jyh070207 2013-08-24 02:55:21
查找异常数据的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版本]
...全文
201 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Shawn 2013-08-26
  • 打赏
  • 举报
 回复 
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
*/
Andy__Huang 2013-08-26
  • 打赏
  • 举报
 回复 
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
*/
这样写看清楚一点
Andy__Huang 2013-08-26
  • 打赏
  • 举报
 回复 
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 a

drop 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语句
习惯性蹭分 2013-08-26
  • 打赏
  • 举报
 回复 

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)
只实现功能了。
ycj80 2013-08-26
  • 打赏
  • 举报
 回复
数据量大,还是要写一个存储的,帮你写了一个过程,可以参考. 

--单笔结存
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=0

declare 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
end

deallocate csr_kc
		

--检查结果
select * from #t order by spbm,dt 
select * from  #t3  order by spbm,dt
Andy__Huang 2013-08-24
  • 打赏
  • 举报
 回复
先把这些union 连接的语句建成一个视图，这样容易检查问题
---涛声依旧--- 2013-08-24
  • 打赏
  • 举报
 回复
建议用存储过程进行校验
SQL数据分析之数据提取、数据查询、数据清洗【MySQL速查】
使用SQL进行数据分析时的步骤，是常用的数据分析岗位必须具备的技能。本文主要介绍一些最基本、使用的sql在数据分析中的各种用法，包括：数据提取、数据查询、数据清洗、数据分组和连接查询。其中最重要的数据查询...
四步搞定异常SQL
导读：本文介绍如何查找需要进行优化的异常SQL并进行处理。影响SQL执行效率的因素主要包括以下几点。1）统计信息，具体如下陈旧的统计信息错误的优化器参数配置数据库迁移前后优化器的改变频繁的...
SqlServer插入数据不报错但查询不到数据
问题背景 近日，某使用SqlServer作为数据库的系统，由于服务器磁盘损坏修复后，部分数据一直无法...根据服务器异常时间点和系统时间点存在重合，假设数据库存在异常，以此反推查找证据，发现SqlServer存在dbcc命令可以
使用sqlserver profiler跟踪sql找出数据库出错原因
有时候我们在调试数据库时候，有几种情况需要跟踪sql语句，第一种是不知道页面的数据是怎么产生的，第二种是系统运行慢，找出慢的SQL语句进行分析，今天，我就分享一款sqlserver自带的分析工具，sqlserver profiler...
MyBatis拦截器应用 — 打印Sql执行时间与检测异常Sql
在Mybatis项目中，代码测试的时候，想要知道每一个Sql在代码中的执行时间以及返回结果来判断Sql编写的是否有问题，一开始只是在自己怀疑有问题的Sql的mapper方法前后获取时间进行打印，但是，当项目里面的Sql很多的...
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章