create table ab
(
a int not null,
b int not null
)
go
insert into ab values(1,2);
insert into ab values(2,1);
go
select * from ab
如果存在任何两条记录的a、b两个字段值交叉相等时只取一条记录,这个在SQLServer里怎么实现?
(
a int not null,
b int not null
)
go
insert into ab values(1,2);
insert into ab values(2,1);
go
select * from ab
如果存在任何两条记录的a、b两个字段值交叉相等时只取一条记录,这个在SQLServer里怎么实现?
create table ab
(
a varchar(10) not null,
b varchar(10) not null
)
go
insert into ab values('a','d');
insert into ab values('d','a');
insert into ab values('ggaa','rttr');
insert into ab values('rttr','ggaa');
go
select * from ab where a>b
/*
a b
---------- ----------
d a
rttr ggaa(2 行受影响)
*/
写了好多次的说,
TONY哥这样也会有不正确的情况
如果只有一条 1 2SELECT
DISTINCT
CASE WHEN A>=B THEN B ELSE A END A,
CASE WHEN A>=B THEN A ELSE B END B
FROM TB
from tab t
where exists(select 1 from tab where a=t.b and b=t.a)
and not exists(select 1 from tab where a<t.a)
/**
a b
----------- -----------
1 2(1 行受影响)
**/
drop table tab
select * from ab t1
where exists(select 1 from ab t2 where t2.a=t1.b and t2.b=t1.a)
and a<b
再插入一些数据insert into ab values(3,3);
insert into ab values(4,3);
insert into ab values(3,4);
insert into ab values(5,6);
insert into ab values(7,8);
insert into ab values(6,9);
insert into ab values(9,9);
这里的值大小是没有顺序的,所以
and not exists(select 1 from tab where a<t.a)
这里会有很多值取不出来的
SELECT
DISTINCT
CASE WHEN A>=B THEN B ELSE A END A,
CASE WHEN A>=B THEN A ELSE B END B
FROM TB在这里写过5楼了
但是如果a和b的值不能调换位置那就不行了,
再插入
insert into ab values(9,5);这样再查询出来的9和5就调换了位置了,希望5楼能在完善下
(
a int not null,
b int not null
)
go
insert into ab values(1,2);
insert into ab values(2,1);
insert into ab values(9,5);
go
select
distinct
case when a>=b then b else a end a,
case when a>=b then a else b end b
from
ab a
where exists(select 1 from ab where a=a.b and b=a.a)
union allselect
*
from
ab a
where not exists(select 1 from ab where a=a.b and b=a.a)
将存在交叉值相等记录和交叉值不相等的记录分开查询然后再合并到一起,这种方法确实不错,呵呵