日期:2014-05-18 浏览次数:20900 次
create table b(ke varchar(5),dan varchar(5)) insert b select 'a', 'b' union all select 'b', 'a' union all select 'c', 'd' union all select 'd', 'e' union all select 'e', 'c' select b1.ke, b2.ke, b3.ke from b b1, b b2, b b3 where b1.dan =b2.ke and b2.dan<>b1.ke and b2.dan=b3.ke and b3.dan<>b2.ke and b1.ke>b2.ke ----结果 e c d
------解决方案--------------------
刚才写了个递归函数感觉反而把问题弄复杂了,因为存在a,b--b,a这样的数据,清理后效率是很低的,所以就想到了对比字段上,可能不太好,见量!
create table b(ke varchar(5),dan varchar(5))
insert b
select 'a','b' union all
select 'b','a' union all
select 'c','d' union all
select 'd','e' union all
select 'e','c' union all
select '1','2' union all
select '2','1' union all
select '3','4' union all
select '4','5' union all
select '5','3'
union all
select '11','22' union all
select '22','33' union all
select '33','44' union all
select '44','11'
GO
--来得到合并的字符串的ASCII码的数字
CREATE FUNCTION F_UNIONTOTAL(@s VARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE @re INT
SELECT @s = RTRIM(LTRIM(@s)),@re = 0
WHILE @s<>''
BEGIN
SET @re = @re + ASCII(LEFT(@s,1))
SET @s = STUFF(@s,1,1,'')
END
RETURN @re
END
GO
--两级匹配
SELECT One,Two FROM
(
select One=Oa.ke,Two=Ob.ke,Three=dbo.F_UNIONTOTAL(Oa.ke+Ob.ke)
from b Oa ,b Ob WHERE Oa.dan = Ob.ke
) A WHERE One + Two IN
(
SELECT Top 1 One+Two FROM
(
select One=Oa.ke,Two=Ob.ke,Three=dbo.F_UNIONTOTAL(Oa.ke+Ob.ke)
from b Oa ,b Ob WHERE Oa.dan = Ob.ke
) B WHERE A.Three = B.Three
)
--三级匹配
SELECT One,Two,Three FROM
(
select One=Oa.ke,Two=Oa.dan,Three=Ob.dan,Four=dbo.F_UNIONTOTAL(Oa.ke+Oa.dan+Ob.dan)
from b Oa ,b Ob WHERE Oa.dan = Ob.ke AND dbo.F_UNIONTOTAL(Oa.ke+Oa.dan) <> dbo.F_UNIONTOTAL(Ob.ke+Ob.dan)
) A WHERE One + Two + Three IN
(
SELECT TOP 1 One + Two + Three FROM
(
select One=Oa.ke,Two=Oa.dan,Three=Ob.dan,Four=dbo.F_UNIONTOTAL(Oa.ke+Oa.dan+Ob.dan)
from b Oa ,b Ob WHERE Oa.dan = Ob.ke AND dbo.F_UNIONTOTAL(Oa.ke+Oa.dan) <> dbo.F_UNIONTOTAL(Ob.ke+Ob.dan)
) B WHERE A.Four = B.Four
)
DROP FUNCTION F_UNIONTOTAL
DROP TABLE b
One Two
----- -----
11 22
2 1
3 4
33 44
4 5
44 11
5 3
b a
c d
d e
e c
(所影响的行数为 11 行)
One Two Three
----- ----- -----
e c d
5 3 4
22 33 44
33 44 11
44 11 22
(所影响的行数为 5 行)