数据导入过程中的转换问题。

Question

目前在编写一个数据导入的存储过程，遇到效率问题。表结构和示例数据如下。
源表：  
批次  编号 
  1   1000
 1   1001
 2   1000
 3   1001
 4   1000
 5   1000
目标表：
编号  起始批次    结束批次
1000    1          2
1001    1          1
1001    3          3
1000    4          5
解释：
如果同一个编号连续出现在一些批次中，那么只需要修改结束批次，如果从某一批次断开了，那么之后的批次再次出现时就要重新插入一条记录。
目前有大概100个批次，每个批次有5万左右个编号。我在存储过程中用了两层游标，第一层循环批次，第二层循环本批次的编号。如果遇到上一批次有相同编号则修改上一批次同编号的结束批次，否则就插入一条新纪录。
但是执行速度很慢，求更高效率的编写方法。

Answer 1

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-11 18:24:47
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Mar 29 2009 10:27:29 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:源表IF NOT OBJECT_ID('[源表]') IS NULL
DROP TABLE [源表]
GO
CREATE TABLE [源表]([批次] int,[编号] int)
INSERT [源表]
SELECT 1,1000 UNION ALL
SELECT 1,1001 UNION ALL
SELECT 2,1000 UNION ALL
SELECT 3,1001 UNION ALL
SELECT 4,1000 UNION ALL
SELECT 5,1000
GO
--SELECT * FROM [源表]-->SQL查询如下:
SELECT a.编号,a.批次 起始批次,MIN(b.批次) 结束批次
FROM (
SELECT * FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND T.批次=批次+1)
) AS a
INNER JOIN (
SELECT * FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND 批次=T.批次+1)
) AS b 
ON a.批次<=b.批次 AND a.编号=b.编号
GROUP BY a.批次,a.编号
ORDER BY 起始批次,编号
/*
编号          起始批次        结束批次
----------- ----------- -----------
1000        1           2
1001        1           1
1001        3           3
1000        4           5(4 行受影响)
*/

Answer 2

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-11 18:24:47
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Mar 29 2009 10:27:29 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:源表IF NOT OBJECT_ID('[源表]') IS NULL
DROP TABLE [源表]
GO
CREATE TABLE [源表]([批次] int,[编号] int)
INSERT [源表]
SELECT 1,1000 UNION ALL
SELECT 1,1001 UNION ALL
SELECT 2,1000 UNION ALL
SELECT 3,1001 UNION ALL
SELECT 4,1000 UNION ALL
SELECT 5,1000
GO
--SELECT * FROM [源表]--> 生成测试数据表:目标表IF NOT OBJECT_ID('[目标表]') IS NULL
DROP TABLE [目标表]
GO
CREATE TABLE [目标表]([编号] int,[起始批次] int,[结束批次] int)
GO
-->SQL查询如下:
INSERT 目标表
SELECT a.编号,a.批次 起始批次,MIN(b.批次) 结束批次
FROM (
SELECT * FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND T.批次=批次+1)
) AS a
INNER JOIN (
SELECT * FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND 批次=T.批次+1)
) AS b 
ON a.批次<=b.批次 AND a.编号=b.编号
GROUP BY a.批次,a.编号SELECT * FROM 目标表
/*
编号          起始批次        结束批次
----------- ----------- -----------
1000        1           2
1000        4           5
1001        1           1
1001        3           3(4 行受影响)
*/

Answer 3

如果需要存储过程,也帮你做一下,这下就符合题意了:
--> 生成测试数据表:源表IF NOT OBJECT_ID('[源表]') IS NULL
DROP TABLE [源表]
GO
CREATE TABLE [源表]([批次] int,[编号] int)
INSERT [源表]
SELECT 1,1000 UNION ALL
SELECT 1,1001 UNION ALL
SELECT 2,1000 UNION ALL
SELECT 3,1001 UNION ALL
SELECT 4,1000 UNION ALL
SELECT 5,1000
GO
--SELECT * FROM [源表]--> 生成测试数据表:目标表IF NOT OBJECT_ID('[目标表]') IS NULL
DROP TABLE [目标表]
GO
CREATE TABLE [目标表]([编号] int,[起始批次] int,[结束批次] int)
GO
-->SQL查询如下:--创建存储过程:
CREATE PROC sp_源表插入目标表
AS
SELECT a.编号,a.批次 起始批次,MIN(b.批次) 结束批次
FROM (
SELECT * FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND T.批次=批次+1)
) AS a
INNER JOIN (
SELECT * FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND 批次=T.批次+1)
) AS b 
ON a.批次<=b.批次 AND a.编号=b.编号
GROUP BY a.批次,a.编号
GO
--插入数据:
INSERT 目标表 EXEC sp_源表插入目标表
--查询结果:
SELECT * FROM 目标表
/*
编号          起始批次        结束批次
----------- ----------- -----------
1000        1           2
1000        4           5
1001        1           1
1001        3           3(4 行受影响)
*/

Answer 4

测试时发现结果不对，原来是因为我的批次字段是nvarchar的，转换成int就可以了。

Answer 5

--> 生成测试数据表:源表IF NOT OBJECT_ID('[源表]') IS NULL
DROP TABLE [源表]
GO
CREATE TABLE [源表]([批次] int,[编号] int)
INSERT [源表]
SELECT 1,1000 UNION ALL
SELECT 1,1001 UNION ALL
SELECT 2,1000 UNION ALL
SELECT 3,1001 UNION ALL
SELECT 4,1000 UNION ALL
SELECT 5,1000
GO
--SELECT * FROM [源表]--> 生成测试数据表:目标表IF NOT OBJECT_ID('[目标表]') IS NULL
DROP TABLE [目标表]
GO
CREATE TABLE [目标表]([编号] int,[起始批次] int,[结束批次] int)
GO
-->SQL查询如下:--创建存储过程:
IF NOT OBJECT_ID('[sp_源表插入目标表]') IS NULL
DROP PROC [sp_源表插入目标表]
GO
CREATE PROC sp_源表插入目标表
AS
SELECT a.编号,a.批次 起始批次,b.批次 结束批次
FROM (
SELECT rn=ROW_NUMBER()OVER(ORDER BY 编号,批次),* FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND T.批次=批次+1)
) AS a
INNER JOIN (
SELECT rn=ROW_NUMBER()OVER(ORDER BY 编号,批次),* FROM [源表] t 
WHERE NOT EXISTS(
SELECT 1 FROM 源表 
WHERE 编号=T.编号 AND 批次=T.批次+1)
) AS b 
ON a.rn=b.rn
GO
--插入数据:
INSERT 目标表 EXEC sp_源表插入目标表
--查询结果:
SELECT * FROM 目标表
/*
编号          起始批次        结束批次
----------- ----------- -----------
1000        1           2
1000        4           5
1001        1           1
1001        3           3(4 行受影响)
*/2005的用row_number()会更高效.