问1个BOM(树形结构的问题)
pidcid
a a1
a a2
a a3
b b1
b b2
b b3
c a1
c a2
c a3
-----------------------
如上,pid (a,c) 这2个是重复的!
假设BOM已经有 n 万行,
问题1.怎样快速找出重复数据.
问题2.下次新增加的时候,如插入(a1,a2,a3)这样的数据,组成新BOM时候,系统能提示已经重复.
注: 因为BOM数据量较大,麻烦给1个速度快1些的方法,另外BOM是树形结构的,以上只列了1层.
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-27 14:26:20
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([pid] varchar(1),[cid] varchar(2))
insert [test]
select 'a','a1' union all
select 'a','a2' union all
select 'a','a3' union all
select 'b','b1' union all
select 'b','b2' union all
select 'b','b3' union all
select 'c','a1' union all
select 'c','a2' union all
select 'c','a3'
go
--问题二:
--写个存储过程:
create proc up_test
(
@pid varchar(2),
@cid varchar(2)
)
as
if exists(select 1 from test where pid<>@pid and cid=@cid)
begin
print '插入数据重复'
end
else
begin
insert test
select @pid,@cid
end
go
exec up_test 'd','a1'
/*
插入数据重复
*/
USE test
GO
-->生成表t1
if object_id('t1') is not null
drop table t1
Go
Create table t1([pid] nvarchar(2),[cid] nvarchar(2))
Insert into t1
Select N'a',N'a1'
Union all Select N'a',N'a2'
Union all Select N'a',N'a3'
Union all Select N'b',N'b1'
Union all Select N'b',N'b2'
Union all Select N'b',N'b3'
--Union all Select N'a',N'c'-- 特殊情況
Union all Select N'c',N'a1'
Union all Select N'c',N'a2'
Union all Select N'c',N'a3'
--Union all Select N'b1',N'b1'-- 特殊情況
--Union all Select N'b2',N'b2'-- 特殊情況
--Union all Select N'b3',N'b3'-- 特殊情況
-- 臨時表效率提升
IF object_id('tempdb..#tmp_Result') IS NOT NULL
DROP TABLE #tmp_Result
SELECT
ROW_NUMBER()OVER(ORDER BY getdate()) AS iden
,*
INTO #tmp_Result
FROM t1
-- 臨時表效率提升
IF object_id('tempdb..#Result') IS NOT NULL
DROP TABLE #Result
;WITH Result AS (
Select
1 AS row
,a.iden
,a.pid
,a.cid
FROM #tmp_Result AS a
UNION ALL
SELECT
b.row+1 AS row
,b.iden
,a.pid
,b.cid
FROM #tmp_Result AS a
INNER JOIN Result AS b ON a.cid=b.pid AND b.iden>a.iden
)
SELECT
pid
,cid
INTO #Result
FROM Result AS a
WHERE NOT EXISTS(SELECT 1 FROM Result
WHERE iden=a.iden
AND row>a.row
)
SELECT
*
FROM #Result AS a
WHERE EXISTS(SELECT 1 FROM #Result
WHERE cid=a.cid
AND pid<>a.pid
