create table ghpc(
ItemCode varchar(50), --物料
DocDate DateTime, --日期
U_ghpc int --批次
)insert into ghpc (ItemCode,U_ghpc)
select '201099-00-01','2011-07-01', 20110701
Union all
select '201099-00-01','2011-07-02', 20110702
Union all
select '201099-00-01','2011-07-03',20110703
Union all
select '201099-00-01','2011-07-04',20110704
Union all
select '201099-00-01','2011-07-05', 20110705
Union all
select '201099-00-01','2011-07-06', 20110702
Union all
select '201099-00-01','2011-07-07', 20110703
Union all
select '201099-00-02','2011-07-01',20110701
Union all
select '201099-00-02','2011-07-02', 20110702
Union all
select '201099-00-02','2011-07-03', 20110703
Union all
select '201099-00-02','2011-07-04', 20110704
Union all
select '201099-00-02','2011-07-05',20110705
Union all
select '201099-00-02','2011-07-06', 20110702
Union all
select '201099-00-02','2011-07-07', 20110703ItemCode表示物料的编码,U_ghpc表示物料批次,现需要查出没有按照先进先出规则的数据,即同一种物料的日期较大的批次也应该比较大,如下面的两组物料就没有按照先进先出的规则发货,如何用SQL查询出不符合要求的数据项
'201099-00-01','2011-07-06', 20110702
'201099-00-01','2011-07-07', 20110703
哪位高人指点一下,不胜感激!
ItemCode varchar(50), --物料
DocDate DateTime, --日期
U_ghpc int --批次
)insert into ghpc (ItemCode,U_ghpc)
select '201099-00-01','2011-07-01', 20110701
Union all
select '201099-00-01','2011-07-02', 20110702
Union all
select '201099-00-01','2011-07-03',20110703
Union all
select '201099-00-01','2011-07-04',20110704
Union all
select '201099-00-01','2011-07-05', 20110705
Union all
select '201099-00-01','2011-07-06', 20110702
Union all
select '201099-00-01','2011-07-07', 20110703
Union all
select '201099-00-02','2011-07-01',20110701
Union all
select '201099-00-02','2011-07-02', 20110702
Union all
select '201099-00-02','2011-07-03', 20110703
Union all
select '201099-00-02','2011-07-04', 20110704
Union all
select '201099-00-02','2011-07-05',20110705
Union all
select '201099-00-02','2011-07-06', 20110702
Union all
select '201099-00-02','2011-07-07', 20110703ItemCode表示物料的编码,U_ghpc表示物料批次,现需要查出没有按照先进先出规则的数据,即同一种物料的日期较大的批次也应该比较大,如下面的两组物料就没有按照先进先出的规则发货,如何用SQL查询出不符合要求的数据项
'201099-00-01','2011-07-06', 20110702
'201099-00-01','2011-07-07', 20110703
哪位高人指点一下,不胜感激!
select * from ghpc a
where exists(select 1 from ghpc where ItemCode=a.ItemCode and DocDate<a.DocDate and U_ghpc>a.U_ghpc)
ItemCode varchar(50), --物料
DocDate DateTime, --日期
U_ghpc int --批次
)insert into ghpc --(ItemCode,U_ghpc)
select '201099-00-01','2011-07-01', 20110701
Union all
select '201099-00-01','2011-07-02', 20110702
Union all
select '201099-00-01','2011-07-03',20110703
Union all
select '201099-00-01','2011-07-04',20110704
Union all
select '201099-00-01','2011-07-05', 20110705
Union all
select '201099-00-01','2011-07-06', 20110702
Union all
select '201099-00-01','2011-07-07', 20110703
Union all
select '201099-00-02','2011-07-01',20110701
Union all
select '201099-00-02','2011-07-02', 20110702
Union all
select '201099-00-02','2011-07-03', 20110703
Union all
select '201099-00-02','2011-07-04', 20110704
Union all
select '201099-00-02','2011-07-05',20110705
Union all
select '201099-00-02','2011-07-06', 20110702
Union all
select '201099-00-02','2011-07-07', 20110703
go
select * from ghpc a
where exists(select 1 from ghpc where itemcode=a.itemcode and ((docdate<a.docdate and U_ghpc>a.U_ghpc)))
/*
ItemCode DocDate U_ghpc
-------------------------------------------------- ----------------------- -----------
201099-00-01 2011-07-06 00:00:00.000 20110702
201099-00-01 2011-07-07 00:00:00.000 20110703
201099-00-02 2011-07-06 00:00:00.000 20110702
201099-00-02 2011-07-07 00:00:00.000 20110703(4 行受影响)*/
go
drop table ghpc