34,590
社区成员
发帖
与我相关
我的任务
分享
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
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
--这个很简单啊,你们是不是想复杂了
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
/*
友情提示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
*/
--> 测试数据: @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
*/
--> 测试数据: @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
*/