以下为当时所做的项目一些垃圾数据处理。当时不知道如何调试sql server 2005(2008),就单独写了一段代码处理,也未使用存储过程。
DROP TABLE #ContainerTemp
DECLARE @varRowCountID int
DECLARE @varContainerNo nvarchar(30)
DECLARE @varRow int
DECLARE @varRowLowYear int
DECLARE @varRowTotal int
DECLARE @LastCycle int
DECLARE @ref nvarchar(30)
create table #ContainerTemp(
RowID int identity(1,1) not null,
ContainerNo nvarchar(30))
INSERT INTO #ContainerTemp(ContainerNo) SELECT DISTINCT MContainerNo FROM td_LGMV_Movement ORDER BY MContainerNo
SELECT @varRow = max(RowID) FROM #ContainerTemp
--SET @varRow = 52
SET @varRowCountID = 35001
WHILE(@varRowCountID<=53409)
BEGIN
SELECT @varContainerNo = ContainerNo FROM #ContainerTemp WHERE RowID = @varRowCountID
--PRINT 'ContainerNo'
--PRINT @varContainerNo
SELECT @LastCycle = max(MCycle) FROM (
SELECT DISTINCT MContainerNo,MCycle FROM td_LGMV_Movement INNER JOIN #ContainerTemp ON
td_LGMV_Movement.MContainerNo = #ContainerTemp.ContainerNo WHERE RowID = @varRowCountID AND MEventDT<'2008-01-01 00:00:000') a --ORDER BY MEventDT,MCycle
--PRINT 'RowCountID'
--PRINT @varRowCountID
--PRINT 'LastCycle'
--PRINT @LastCycle
SELECT @varRowLowYear = count(*) FROM (
SELECT DISTINCT MContainerNo,MCycle FROM td_LGMV_Movement INNER JOIN #ContainerTemp ON
td_LGMV_Movement.MContainerNo = #ContainerTemp.ContainerNo WHERE RowID = @varRowCountID AND MEventDT<'2008-01-01 00:00:000') a --ORDER BY MEventDT,MCycle
--PRINT 'RowLowYear'
--PRINT @varRowLowYear
SELECT @varRowTotal = count(*) FROM (
SELECT DISTINCT MContainerNo,MCycle FROM td_LGMV_Movement INNER JOIN #ContainerTemp ON
td_LGMV_Movement.MContainerNo = #ContainerTemp.ContainerNo WHERE RowID = @varRowCountID) a --ORDER BY MEventDT,MCycle
--PRINT 'RowTotal'
--PRINT @varRowTotal
--08年之后无动态
IF(@varRowLowYear = @varRowTotal)
BEGIN
IF(@LastCycle IS NULL)
SET @LastCycle = 0
ELSE
BEGIN
IF(@LastCycle = 0)
BEGIN
--PRINT '处理08年之后无动态,只有一个Cycle的例子' + cast(@LastCycle as nvarchar(6))
UPDATE td_LGMV_Movement SET MCycle = 1 WHERE MContainerNo = @varContainerNo
END
ELSE
BEGIN
--PRINT '处理08年之后无动态,有多个Cycle的例子' + cast(@LastCycle as nvarchar(6))
DELETE FROM td_LGMV_Movement WHERE td_LGMV_Movement.MContainerNo = @varContainerNo AND MCycle < @LastCycle
--PRINT @varContainerNo
UPDATE td_LGMV_Movement SET MCycle = MCycle - @LastCycle + 1 WHERE MContainerNo = @varContainerNo
END
END
END
ELSE--08年之后有动态
BEGIN
IF(@LastCycle IS NOT NULL)
BEGIN
--PRINT '处理08年之后有动态,有多个Cycle的例子' + cast(@LastCycle as nvarchar(6))
DELETE FROM td_LGMV_Movement WHERE td_LGMV_Movement.MContainerNo = @varContainerNo AND MCycle < @LastCycle + 1
UPDATE td_LGMV_Movement SET MCycle = MCycle - @LastCycle WHERE MContainerNo = @varContainerNo
END
END
--PRINT @LastCycle
--PRINT @varRowLowYear
--PRINT @varRowTotal
SELECT TOP 1 @ref = MMovementRef FROM td_LGMV_Movement
WHERE MContainerNo = @varContainerNo ORDER BY MEventDT ASC
UPDATE td_LGMV_Movement SET MPreMovememtRef = NULL WHERE MMovementRef = @ref
--PRINT @ref
SET @varRowCountID = @varRowCountID + 1
END