--> 测试数据: @T declare @T table (字段1 varchar(1),字段2 varchar(1)) insert into @T select 'a','b' union all select 'c','d' union all select 'b','a' union all select 'm','n' union all select 'o','p' union all select 'p','o' ;with maco as ( select row_number() over(partition by ascii(字段1)+ascii(字段2), abs(ascii(字段1)-ascii(字段2)) order by (select 1)) as rid,* from @T a ) select 字段1,字段2 from maco where rid=1 /* 字段1 字段2 ---- ---- b a c d m n o p*/ 如果是字母的话,可以这样。
是有啊 但是主键不能约束互为相同的字段吧 比如不能约束 a,b和把b,a
--> 测试数据: @T declare @t table (id int,字段1 varchar(1),字段2 varchar(1)) insert into @t select 1,'a','b' union all select 2,'c','d' union all select 3,'b','a' union all select 4,'m','n' union all select 5,'o','p' union all select 6,'p','o';with maco as ( select id,字段1 as col from @t union all select id,字段2 from @t ) select id, col=stuff((select ','+col from maco where id=t.id order by col for xml path('')), 1, 1, '') from maco t group by id /* id col ----------- ------------- 1 a,b 2 c,d 3 a,b 4 m,n 5 o,p 6 o,p */
/* 友情提示1:数据先备份后处理。 友情提示2: 一定要有索引 */--> 测试数据: @T declare @t table (id int,字段1 varchar(1),字段2 varchar(1)) insert into @t select 1,'a','b' union all select 2,'c','d' union all select 3,'b','a' union all select 4,'m','n' union all select 5,'o','p' union all select 6,'p','o';with maco as ( select id,字段1 as col from @t union all select id,字段2 from @t ),m1 as ( select id, col=stuff((select ','+col from maco where id=t.id order by col for xml path('')), 1, 1, '') from maco t group by id ) ,m2 as ( select row_number() over (partition by col order by id) as rid,id from m1 ) delete @t from @t a left join m2 b on a.id=b.id where b.rid<>1 select * from @t /* id 字段1 字段2 ----------- ---- ---- 1 a b 2 c d 4 m n 5 o p */
C 表有三个字段 id,a,b a和b就是你的两个字段 id是唯一标识 not in 效率慢了些 你可以想办法优化 select ID, a,b from C where ID not in ( select c1.ID id1 from C c1 left join C c2 on c1.b=c2.a where c1.a=c2.b group by c1.ID)
--这个很简单啊,你们是不是想复杂了 SELECT a.* FROM 表 a LEFT JOIN 表 b ON a.客户端ip = b.服务器ip AND a.客户端端口 = b.服务端端口 AND a.id > b.id WHERE b.id IS NULL
SELECT a.* FROM 表 a LEFT JOIN 表 b ON a.客户端ip = b.服务器ip AND a.客户端端口 = b.服务端端口 AND a.服务器ip = b.客户端ip AND a.服务端端口 = b.客户端端口 --刚刚写漏了 AND a.id > b.id WHERE b.id IS NULL
--> 测试数据: @T
declare @T table (字段1 varchar(1),字段2 varchar(1))
insert into @T
select 'a','b' union all
select 'c','d' union all
select 'b','a' union all
select 'm','n' union all
select 'o','p' union all
select 'p','o'
;with maco as
(
select row_number() over(partition by ascii(字段1)+ascii(字段2),
abs(ascii(字段1)-ascii(字段2)) order by (select 1)) as rid,* from @T a
)
select 字段1,字段2 from maco where rid=1
/*
字段1 字段2
---- ----
b a
c d
m n
o p*/
如果是字母的话,可以这样。
比如不能约束 a,b和把b,a
--> 测试数据: @T
declare @t table (id int,字段1 varchar(1),字段2 varchar(1))
insert into @t
select 1,'a','b' union all
select 2,'c','d' union all
select 3,'b','a' union all
select 4,'m','n' union all
select 5,'o','p' union all
select 6,'p','o';with maco as
(
select id,字段1 as col from @t
union all
select id,字段2 from @t
)
select
id, col=stuff((select ','+col from maco
where id=t.id order by col for xml path('')), 1, 1, '')
from maco t group by id /*
id col
----------- -------------
1 a,b
2 c,d
3 a,b
4 m,n
5 o,p
6 o,p
*/
/*
友情提示1:数据先备份后处理。
友情提示2: 一定要有索引
*/--> 测试数据: @T
declare @t table (id int,字段1 varchar(1),字段2 varchar(1))
insert into @t
select 1,'a','b' union all
select 2,'c','d' union all
select 3,'b','a' union all
select 4,'m','n' union all
select 5,'o','p' union all
select 6,'p','o';with maco as
(
select id,字段1 as col from @t
union all
select id,字段2 from @t
),m1 as
(
select
id, col=stuff((select ','+col from maco
where id=t.id order by col for xml path('')), 1, 1, '')
from maco t group by id
)
,m2 as
(
select row_number() over (partition by col order by id) as rid,id from m1
)
delete @t from @t a left join m2 b on a.id=b.id where b.rid<>1 select * from @t
/*
id 字段1 字段2
----------- ---- ----
1 a b
2 c d
4 m n
5 o p
*/
C 表有三个字段 id,a,b a和b就是你的两个字段 id是唯一标识 not in 效率慢了些 你可以想办法优化 select ID, a,b from C
where ID not in (
select c1.ID id1
from C c1
left join C c2
on c1.b=c2.a
where c1.a=c2.b
group by c1.ID)
内容如下:id 客户端ip 客户端端口 服务器ip 服务端端口
1 192.168.1.1 81 192.168.1.2 82
2 192.168.1.3 83 192.168.1.4 84
3 192.168.1.9 67 192.168.1.6 54
4 192.168.1.4 84 192.168.1.3 83像3和4这种情况我认为是相同的,即:客户端和服务器内容相反
怎么才能去除这样的重复?
--这个很简单啊,你们是不是想复杂了
SELECT a.*
FROM 表 a LEFT JOIN 表 b
ON a.客户端ip = b.服务器ip AND a.客户端端口 = b.服务端端口 AND a.id > b.id
WHERE b.id IS NULL
SELECT a.*
FROM 表 a
LEFT JOIN 表 b ON a.客户端ip = b.服务器ip AND a.客户端端口 = b.服务端端口
AND a.服务器ip = b.客户端ip AND a.服务端端口 = b.客户端端口 --刚刚写漏了
AND a.id > b.id
WHERE b.id IS NULL