目前在编写一个数据导入的存储过程,遇到效率问题。表结构和示例数据如下。
源表:
批次 编号
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万左右个编号。我在存储过程中用了两层游标,第一层循环批次,第二层循环本批次的编号。如果遇到上一批次有相同编号则修改上一批次同编号的结束批次,否则就插入一条新纪录。
但是执行速度很慢,求更高效率的编写方法。
源表:
批次 编号
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万左右个编号。我在存储过程中用了两层游标,第一层循环批次,第二层循环本批次的编号。如果遇到上一批次有相同编号则修改上一批次同编号的结束批次,否则就插入一条新纪录。
但是执行速度很慢,求更高效率的编写方法。
/*---------------------------------
-- 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 行受影响)
*/
/*---------------------------------
-- 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 行受影响)
*/
--> 生成测试数据表:源表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 行受影响)
*/
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()会更高效.