第一步:insert 表C
select distinct * from 表A t where exists(select 1 from 表A where name=t.name and birth=t.birth)
union
select * from 表A where telno1=telno2 or telno1=telno3 or telno1=telno3
select distinct * from 表A t where exists(select 1 from 表A where name=t.name and birth=t.birth)
union
select * from 表A where telno1=telno2 or telno1=telno3 or telno1=telno3
张三 111 222 333 20080927
张三 444 111 555 20080925
这2条即为相同记录
张三 111 222 333 20080927
张三 444 555 666 20080927
这2条也为相同记录
第一步操作数据都来自表A第二步操作数据是表A中的数据跟表B做比较
select 表B.*
from 表A s,表B t
where s.name=t.name and(s.birth=t.birth or s.telno1=t.telno1 or s.telno1=t.telno2 or s.telno1=t.telno3 or s.telno2=t.telno1 or s.telno2=t.telno2 or s.telno2=t.telno3 or s.telno3=t.telno1 or s.telno3=t.telno2 or s.telno3=t.telno3 )这样?
create procedure proc
as
begin
declare @name varchar(30), @TelNo1 varchar(30),@TelNo2 varchar(30),@TelNo3 varchar(30),@Birth varchar(30);
----------第一步
Cursor cur for
select name,TelNo1,TelNo2,TelNo3,Birth
from A
where (TelNo1 = TelNo2 or TelNo1 = TelNo3 or TelNo3 = TelNo2)
union
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
WHERE AA.NAME=A.NAME AND AA.Birth=A.Birth AND AA.CNT>1
OPEN CUR;
FETCH CUR INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO C (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH CUR INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR;
DEALLOCATE CUR;
----------第二步
Cursor cur2 for
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,B
where (A.TelNo1 = B.TelNo1 or A.TelNo1 = B.TelNo2 or A.TelNo1 = B.TelNo3)
or(A.TelNo2 = B.TelNo1 or A.TelNo2 = B.TelNo2 or A.TelNo2 = B.TelNo3)
or(A.TelNo3 = B.TelNo1 or A.TelNo3 = B.TelNo2 or A.TelNo3 = B.TelNo3)
union
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
(select name,count(*) AS CNT,Birth
from b
group by name,Birth
) AS bb
WHERE AA.NAME=A.NAME
AND AA.Birth=A.Birth
AND AA.CNT =bb.cnt
and aa.name=bb.name
and AA.Birth=bb.Birth
OPEN CUR2;
FETCH CUR2 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO C (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH CUR2 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR2;
DEALLOCATE CUR2;
-------------第三步
Cursor cur3 for
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,B
where (A.TelNo1 <> B.TelNo1 and A.TelNo1 <> B.TelNo2 and A.TelNo1 <> B.TelNo3)
or(A.TelNo2 <> B.TelNo1 and A.TelNo2 <> B.TelNo2 and A.TelNo2 <> B.TelNo3)
or(A.TelNo3 <>B.TelNo1 and A.TelNo3 <> B.TelNo2 and A.TelNo3 <> B.TelNo3)
union
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
(select name,count(*) AS CNT,Birth
from b
group by name,Birth
) AS bb
WHERE AA.NAME<>A.NAME
AND AA.Birth<>A.Birth
AND AA.CNT <>bb.cnt
and aa.name<>bb.name
and AA.Birth<>bb.Birth
OPEN CUR3;
FETCH CUR3 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO B (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH CUR3 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR3;
DEALLOCATE CUR3;end呵呵 我担心你的数据表不会发生主键冲突吧,
as
begin
declare @name varchar(30), @TelNo1 varchar(30),@TelNo2 varchar(30),@TelNo3 varchar(30),@Birth varchar(30);
----------第一步
Cursor cur for
select AB.name,AB.TelNo1,AB.TelNo2,AB.TelNo3,AB.Birth
FROM
(select name,TelNo1,TelNo2,TelNo3,Birth
from A
where (TelNo1 = TelNo2 or TelNo1 = TelNo3 or TelNo3 = TelNo2)
) As AB
(select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
WHERE AA.NAME=A.NAME AND AA.Birth=A.Birth AND AA.CNT>1
)AS AC
WHERE (AB.NAME <>AC.NAME AND AB.BIRTH <>AC.BIRTH)
AND (AB.TelNo1 <> AC.TelNo1 and AB.TelNo1 <> AC.TelNo2 and AB.TelNo1 <> AC.TelNo3)
AND(AB.TelNo2 <> AC.TelNo1 and AB.TelNo2 <> AC.TelNo2 and AB.TelNo2 <> AC.TelNo3)
AND(AB.TelNo3 <>AC.TelNo1 and AB.TelNo3 <> AC.TelNo2 and AB.TelNo3 <> AC.TelNo3)
OPEN CUR;
FETCH CUR INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO C (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH CUR INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR;
DEALLOCATE CUR;
----------第二步
Cursor cur2 for
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,B
where (A.TelNo1 = B.TelNo1 or A.TelNo1 = B.TelNo2 or A.TelNo1 = B.TelNo3)
or(A.TelNo2 = B.TelNo1 or A.TelNo2 = B.TelNo2 or A.TelNo2 = B.TelNo3)
or(A.TelNo3 = B.TelNo1 or A.TelNo3 = B.TelNo2 or A.TelNo3 = B.TelNo3)
union
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
(select name,count(*) AS CNT,Birth
from b
group by name,Birth
) AS bb
WHERE AA.NAME=A.NAME
AND AA.Birth=A.Birth
AND AA.CNT =bb.cnt
and aa.name=bb.name
and AA.Birth=bb.Birth
OPEN CUR2;
FETCH CUR2 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO C (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH CUR2 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR2;
DEALLOCATE CUR2; -------------第三步
Cursor cur3 for
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,B
where (A.TelNo1 <> B.TelNo1 and A.TelNo1 <> B.TelNo2 and A.TelNo1 <> B.TelNo3)
or(A.TelNo2 <> B.TelNo1 and A.TelNo2 <> B.TelNo2 and A.TelNo2 <> B.TelNo3)
or(A.TelNo3 <>B.TelNo1 and A.TelNo3 <> B.TelNo2 and A.TelNo3 <> B.TelNo3)
union
select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
(select name,count(*) AS CNT,Birth
from b
group by name,Birth
) AS bb
WHERE AA.NAME <>A.NAME
AND AA.Birth <>A.Birth
AND AA.CNT <>bb.cnt
and aa.name <>bb.name
and AA.Birth <>bb.Birth
OPEN CUR3;
FETCH CUR3 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO B (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH CUR3 INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR3;
DEALLOCATE CUR3; END怎么样
select distinct * from tba t where exists(select 1 from tbA where name=t.name and birth=t.birth and telno1<>t.telno1)
union
select * from tbA where telno1=telno2 or telno1=telno3 or telno1=telno3
insert tbC
select distinct t.*
from tbA s,tbB t
where s.name=t.name and(s.birth=t.birth or s.telno1=t.telno1 or s.telno1=t.telno2 or s.telno1=t.telno3 or s.telno2=t.telno1 or s.telno2=t.telno2 or s.telno2=t.telno3 or s.telno3=t.telno1 or s.telno3=t.telno2 or s.telno3=t.telno3 )
select * from tbc张三 111 222 333 2008-09-27 00:00:00.000
张三 123 234 345 2008-09-27 00:00:00.000
张三 876 543 111 2008-09-24 00:00:00.000
张三 987 654 321 2008-09-27 00:00:00.000
消息 156,级别 15,状态 1,过程 A,第 6 行
关键字 'Cursor' 附近有语法错误。
消息 156,级别 15,状态 1,过程 A,第 13 行
关键字 'select' 附近有语法错误。
消息 102,级别 15,状态 1,过程 A,第 20 行
')' 附近有语法错误。
消息 156,级别 15,状态 1,过程 A,第 39 行
关键字 'Cursor' 附近有语法错误。
消息 156,级别 15,状态 1,过程 A,第 52 行
关键字 'select' 附近有语法错误。
消息 102,级别 15,状态 1,过程 A,第 55 行
')' 附近有语法错误。
消息 156,级别 15,状态 1,过程 A,第 74 行
关键字 'Cursor' 附近有语法错误。
消息 156,级别 15,状态 1,过程 A,第 87 行
关键字 'select' 附近有语法错误。
消息 102,级别 15,状态 1,过程 A,第 90 行
')' 附近有语法错误。太复杂了,能不能帮我改下
as
begin
declare @name varchar(30), @TelNo1 varchar(30),@TelNo2 varchar(30),@TelNo3 varchar(30),@Birth varchar(30);
----------第一步
DECLARE CUR CURSOR for
select AB.name,AB.TelNo1,AB.TelNo2,AB.TelNo3,AB.Birth
FROM
(select name,TelNo1,TelNo2,TelNo3,Birth
from A
where (TelNo1 = TelNo2 or TelNo1 = TelNo3 or TelNo3 = TelNo2)
) As AB,
(select A.name,A.TelNo1,A.TelNo2,A.TelNo3,A.Birth
from A,
(select name,count(*) AS CNT,Birth
from A
group by name,Birth
) AS AA
WHERE AA.NAME=A.NAME AND AA.Birth=A.Birth AND AA.CNT>1
)AS AC
WHERE (AB.NAME <>AC.NAME AND AB.BIRTH <>AC.BIRTH)
AND (AB.TelNo1 <> AC.TelNo1 and AB.TelNo1 <> AC.TelNo2 and AB.TelNo1 <> AC.TelNo3)
AND(AB.TelNo2 <> AC.TelNo1 and AB.TelNo2 <> AC.TelNo2 and AB.TelNo2 <> AC.TelNo3)
AND(AB.TelNo3 <>AC.TelNo1 and AB.TelNo3 <> AC.TelNo2 and AB.TelNo3 <> AC.TelNo3)
OPEN CUR;
FETCH NEXT FROM cur INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO C (name, TelNo1,TelNo2,TelNo3,Birth)
SELECT @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
FETCH NEXT FROM cur INTO @name , @TelNo1,@TelNo2,@TelNo3 ,@Birth;
END
CLOSE CUR;
DEALLOCATE CUR;
END
主要看查询那一块。
create table tb(name nvarchar(10),tel1 varchar(15),tel2 varchar(15),tel3 varchar(15),birth varchar(8))
insert into tb select '张三','111','222','333','20080928'
insert into tb select '张三','444','111','555','20080925'
insert into tb select '张三','110','220','330','20080927'
insert into tb select '张三','440','550','660','20080927'
go
create table tc(name nvarchar(10),tel1 varchar(15),tel2 varchar(15),tel3 varchar(15),birth varchar(8))
go
/*create procedure getsame
as*/
insert into tc select a.* from tb a inner join (
select [name],birth,count(*) as ct from tb group by [name],birth having count(*)>1
)b on a.name=b.name and a.birth=b.birth
insert into tc select a.* from tb a inner join (
select t1.name,t1.tel1 from tb t1 inner join tb t2 on t1.name=t2.name and t1.birth<>t2.birth and (t1.tel1=t2.tel1 or t1.tel1=t2.tel2 or t1.tel1=t2.tel3)
)b on a.name=b.name where a.tel1=b.tel1 or a.tel2=b.tel1 or a.tel3=b.tel1
insert into tc select a.* from tb a inner join (
select t1.name,t1.tel2 from tb t1 inner join tb t2 on t1.name=t2.name and t1.birth<>t2.birth and (t1.tel2=t2.tel2 or t1.tel2=t2.tel3)
)b on a.name=b.name where a.tel1=b.tel2 or a.tel2=b.tel2 or a.tel3=b.tel2
insert into tc select a.* from tb a inner join (
select t1.name,t1.tel3 from tb t1 inner join tb t2 on t1.name=t2.name and t1.birth<>t2.birth and t1.tel3=t2.tel3
)b on a.name=b.name where a.tel1=b.tel3 or a.tel2=b.tel3 or a.tel3=b.tel3
select * from tc
go
drop table tb,tc
/*
name tel1 tel2 tel3 birth
---------- --------------- --------------- --------------- --------
张三 110 220 330 20080927
张三 440 550 660 20080927
张三 111 222 333 20080928
张三 444 111 555 20080925(4 行受影响)
*/
这个需要你在增加一个标示列id,来区分每一行,要保留其中的一行,求其中最大的id或这最小的id对应的行,就可以保留其中的一行了
insert into ta select 1,'张三','111','222','333','19820927'
insert into ta select 2,'张三','123','234','456','19820925'
insert into ta select 3,'张三','444','111','555','19820926'
insert into ta select 4,'张三','777','888','999','19820927'
go
create table tc(id int,name nvarchar(10),tel1 varchar(15),tel2 varchar(15),tel3 varchar(15),birth varchar(8))
go
/*create procedure getsame
as*/
select distinct * into # from (
select a.* from ta a inner join (
select [name],birth from ta group by [name],birth having count(*)>1
)b on a.name=b.name and a.birth=b.birth
union all
select a.* from ta a inner join (
select t1.name,t1.tel1 from ta t1 inner join ta t2 on t1.name=t2.name and t1.birth<>t2.birth and (t1.tel1=t2.tel1 or t1.tel1=t2.tel2 or t1.tel1=t2.tel3)
)b on a.name=b.name where a.tel1=b.tel1 or a.tel2=b.tel1 or a.tel3=b.tel1
union all
select a.* from ta a inner join (
select t1.name,t1.tel2 from ta t1 inner join ta t2 on t1.name=t2.name and t1.birth<>t2.birth and (t1.tel2=t2.tel2 or t1.tel2=t2.tel3)
)b on a.name=b.name where a.tel1=b.tel2 or a.tel2=b.tel2 or a.tel3=b.tel2
union all
select a.* from ta a inner join (
select t1.name,t1.tel3 from ta t1 inner join ta t2 on t1.name=t2.name and t1.birth<>t2.birth and t1.tel3=t2.tel3
)b on a.name=b.name where a.tel1=b.tel3 or a.tel2=b.tel3 or a.tel3=b.tel3
)T
insert into tc select * from # where id not in(
select top 1 id from # order by id
)
delete from ta where id in(select id from # where id not in(
select top 1 id from # order by id
))
select * from ta
select * from tc
go
drop table ta,tc,#
/*
id name tel1 tel2 tel3 birth
----------- ---------- --------------- --------------- --------------- --------
1 张三 111 222 333 19820927
2 张三 123 234 456 19820925(2 行受影响)id name tel1 tel2 tel3 birth
----------- ---------- --------------- --------------- --------------- --------
3 张三 444 111 555 19820926
4 张三 777 888 999 19820927(2 行受影响)
*/
insert into c (name,telno1,telno2,telno3,birth)
select name,telno1,telno2,telno3,birth from a as aa
where (select count(*) from a where name=aa.name
and (telno1 in (aa.telno1,aa.telno2,aa.telno3)
or telno2 in (aa.telno1,aa.telno2,aa.telno3)
or telno3 in (aa.telno1,aa.telno2,aa.telno3)
or birth=aa.birth))>=2--2.
insert into c (name,telno1,telno2,telno3,birth)
select name,telno1,telno2,telno3,birth from a as aa
where exists(select * from b where name=aa.name and (telno1 in (aa.telno1,aa.telno2,aa.telno3)
or telno2 in (aa.telno1,aa.telno2,aa.telno3)
or telno3 in (aa.telno1,aa.telno2,aa.telno3)
or birth=aa.birth))
--3 insert into b (name,telno1,telno2,telno3,birth)
select name,telno1,telno2,telno3,birth from a as aa
where not exists(select * from b where name=aa.name and (telno1 in (aa.telno1,aa.telno2,aa.telno3)
or telno2 in (aa.telno1,aa.telno2,aa.telno3)
or telno3 in (aa.telno1,aa.telno2,aa.telno3)
or birth=aa.birth))
and (select count(*) from a where name=aa.name
and (telno1 in (aa.telno1,aa.telno2,aa.telno3)
or telno2 in (aa.telno1,aa.telno2,aa.telno3)
or telno3 in (aa.telno1,aa.telno2,aa.telno3)
or birth=aa.birth))<2
if not object_id('t1') is null
drop trigger tbc
go
create trigger t1 on tbc
for insert
as
delete tba
where id in (select id from inserted)
insert into tbc
select top 2 * from
(
select * from tba t
where exists(select 1 from tba where id<>t.id and name=t.name and(birth=t.birth or telno1=t.telno1 or telno1=t.telno2 or telno1=t.telno3 or telno2=t.telno1 or telno2=t.telno2 or telno2=telno3 or telno3=t.telno1 or telno3=t.telno2 or telno3=t.telno3 ) )
)K
select * from tbc
1 sss 111 222 333 1982-09-27 00:00:00.000
3 sss 444 111 555 1982-09-26 00:00:00.000
select * from tba 2 sss 123 234 456 1982-09-25 00:00:00.000
4 sss 777 888 999 1982-09-27 00:00:00.000