TO 2楼: 数据不止上面列出的那些,上面列出的只是示例!To 1楼: 数据库中的记录是成对出现的(比如字段1为A,字段2为B,则必有一条字段1为B,字段2为A的记录),现在想得到的结果是只取出其中的一半记录
--需要有一个主键列 create table TableA ( id int, c1 varchar(1), c2 varchar(2) ) insert into TableA select 1,'A','B' insert into TableA select 2,'C','D' insert into TableA select 3,'B','A' insert into TableA select 4,'D','C'--查询 select * from tablea a where not exists(select 1 from tablea where id < a.id and c1 = a.c2 and c2 = a.c1)--结果 /* id c1 c2 ----------- ---- ---- 1 A B 2 C D(2 row(s) affected) */
create table TableA (c1 varchar(1), c2 varchar(2) ) insert into TableA select 'A','B' insert into TableA select 'C','D' insert into TableA select 'B','A' insert into TableA select 'D','C' select * from tablea where c1>c2c1 c2 ---- ---- B A D C(2 行受影响) select * from tablea where c1<c2 c1 c2 ---- ---- A B C D(2 行受影响)
和冷箫轻笛打个招呼 hello
A B C D或者 B A D C select * from table where (字段1='A' and 字段2='B') or (字段1='C' and 字段2='D') or (字段1='B' and 字段2='A') or (字段1='D' and 字段2='C')
declare @T table ( id int, c1 varchar(1), c2 varchar(2) ) insert into @T select 1,'A','B' insert into @T select 2,'C','D' insert into @T select 3,'B','A' insert into @T select 4,'D','C' select * from @T t where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID>t.ID)select * from @T t where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID<t.ID)(所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)id c1 c2 ----------- ---- ---- 3 B A 4 D C(所影响的行数为 2 行)id c1 c2 ----------- ---- ---- 1 A B 2 C D(所影响的行数为 2 行)
如果没有ID递增列,可生成临时表代替: 2005时可用row_number()over(order by c1)--生成递增列
declare @TableA table ( c1 varchar(1), c2 varchar(2) ) insert into @TableA select 'A','B' insert into @TableA select 'C','D' insert into @TableA select 'B','A' insert into @TableA select 'D','C';with cte as ( select ROW_NUMBER() over(order by c1) seqno,* from @TableA ) select * from cte a where not exists(select 1 from cte where seqno > a.seqno and c1 = a.c2 and c2 = a.c1)
declare @T table ( id int, c1 varchar(1), c2 varchar(2) ) insert into @T select 1,'A','B' insert into @T select 2,'C','D' insert into @T select 3,'B','A' insert into @T select 4,'D','C' insert into @T select 5,'A','C' insert into @T select 6,'B','D' insert into @T select 7,'C','A' insert into @T select 8,'D','B' insert into @T select 9,'A','D' insert into @T select 10,'B','C' insert into @T select 11,'D','A' insert into @T select 12,'C','B' select * from @T t where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID<t.ID) UNION ALL select * from @T t where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID>t.ID) --结果 1 A B 2 C D 11 D A 12 C B --额不对了。。貌似上面的也不对
数据不止上面列出的那些,上面列出的只是示例!To 1楼:
数据库中的记录是成对出现的(比如字段1为A,字段2为B,则必有一条字段1为B,字段2为A的记录),现在想得到的结果是只取出其中的一半记录
--需要有一个主键列
create table TableA
(
id int,
c1 varchar(1),
c2 varchar(2)
)
insert into TableA select 1,'A','B'
insert into TableA select 2,'C','D'
insert into TableA select 3,'B','A'
insert into TableA select 4,'D','C'--查询
select * from tablea a
where not exists(select 1 from tablea where id < a.id and c1 = a.c2 and c2 = a.c1)--结果
/*
id c1 c2
----------- ---- ----
1 A B
2 C D(2 row(s) affected)
*/
(c1 varchar(1),
c2 varchar(2)
)
insert into TableA select 'A','B'
insert into TableA select 'C','D'
insert into TableA select 'B','A'
insert into TableA select 'D','C'
select * from tablea where c1>c2c1 c2
---- ----
B A
D C(2 行受影响)
select * from tablea where c1<c2
c1 c2
---- ----
A B
C D(2 行受影响)
hello
C D或者
B A
D C select * from table
where (字段1='A' and 字段2='B') or
(字段1='C' and 字段2='D') or (字段1='B' and 字段2='A')
or (字段1='D' and 字段2='C')
id int,
c1 varchar(1),
c2 varchar(2)
)
insert into @T select 1,'A','B'
insert into @T select 2,'C','D'
insert into @T select 3,'B','A'
insert into @T select 4,'D','C'
select
*
from
@T t
where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID>t.ID)select
*
from
@T t
where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID<t.ID)(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id c1 c2
----------- ---- ----
3 B A
4 D C(所影响的行数为 2 行)id c1 c2
----------- ---- ----
1 A B
2 C D(所影响的行数为 2 行)
2005时可用row_number()over(order by c1)--生成递增列
(
c1 varchar(1),
c2 varchar(2)
)
insert into @TableA select 'A','B'
insert into @TableA select 'C','D'
insert into @TableA select 'B','A'
insert into @TableA select 'D','C';with cte
as
(
select ROW_NUMBER() over(order by c1) seqno,* from @TableA
)
select * from cte a
where not exists(select 1 from cte where seqno > a.seqno and c1 = a.c2 and c2 = a.c1)
declare @T table (
id int,
c1 varchar(1),
c2 varchar(2)
)
insert into @T select 1,'A','B'
insert into @T select 2,'C','D'
insert into @T select 3,'B','A'
insert into @T select 4,'D','C'
insert into @T select 5,'A','C'
insert into @T select 6,'B','D'
insert into @T select 7,'C','A'
insert into @T select 8,'D','B'
insert into @T select 9,'A','D'
insert into @T select 10,'B','C'
insert into @T select 11,'D','A'
insert into @T select 12,'C','B'
select
*
from
@T t
where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID<t.ID)
UNION ALL
select
*
from
@T t
where not exists(select 1 from @T where (c1=t.c2 or c2=t.c1) and ID>t.ID)
--结果
1 A B
2 C D
11 D A
12 C B
--额不对了。。貌似上面的也不对