if object_id('pubs..tb') is not null drop table tb gocreate table tb(A int,B int,C int)insert into tb(A,B,C) values(1,2,3) insert into tb(A,B,C) values(1,3,2) insert into tb(A,B,C) values(2,3,2) insert into tb(A,B,C) values(3,2,3)select A.* from (select * from tb where A in (select A from tb group by A having count(*) > 1)) A, (select A, B , max(C) C from(select * from tb where A in (select A from tb group by A having count(*) > 1)) t group by A ,B) B where A.A = B.A and A.B = B.B and A.C = B.Cdrop table tb /* A B C ----------- ----------- ----------- 1 2 3 1 3 2(所影响的行数为 2 行) */
select * from tb a where exists(select * from tb where A=a.B) and not exists(select * from tb where A=a.A and B=a.B)
Create Table table1 (A Int, B Int, C Int) Insert table1 Select 1,2,3 Union All Select 1,3,2 Union All Select 2,3,2 Union All Select 3,2,3 GO Select Distinct A.* From Table1 A Inner Join Table1 B On A.A = B.A And A.B != B.B GO Drop Table Table1 --Result /* A B C 1 2 3 1 3 2 */
我使用的语句是: select distinct a.* from table1 a,table b where a.a=b.a and a.b<>b.b 不知我写的这句是否正确。
Create Table table1 (A Int, B Int, C Int) Insert table1 Select 1,2,3 Union All Select 1,3,2 Union All Select 2,3,2 Union All Select 3,2,3 GOselect * from table1 where A in (select A from table1 group by A,B having count(1) > 1)
drop table tb
gocreate table tb(A int,B int,C int)insert into tb(A,B,C) values(1,2,3)
insert into tb(A,B,C) values(1,3,2)
insert into tb(A,B,C) values(2,3,2)
insert into tb(A,B,C) values(3,2,3)select A.* from
(select * from tb where A in (select A from tb group by A having count(*) > 1)) A,
(select A, B , max(C) C from(select * from tb where A in (select A from tb group by A having count(*) > 1)) t group by A ,B) B
where A.A = B.A and A.B = B.B and A.C = B.Cdrop table tb
/*
A B C
----------- ----------- -----------
1 2 3
1 3 2(所影响的行数为 2 行)
*/
where exists(select * from tb where A=a.B)
and not exists(select * from tb where A=a.A and B=a.B)
(A Int,
B Int,
C Int)
Insert table1 Select 1,2,3
Union All Select 1,3,2
Union All Select 2,3,2
Union All Select 3,2,3
GO
Select
Distinct
A.*
From
Table1 A
Inner Join
Table1 B
On A.A = B.A And A.B != B.B
GO
Drop Table Table1
--Result
/*
A B C
1 2 3
1 3 2
*/
select distinct a.* from table1 a,table b
where a.a=b.a
and a.b<>b.b
不知我写的这句是否正确。
(A Int,
B Int,
C Int)
Insert table1 Select 1,2,3
Union All Select 1,3,2
Union All Select 2,3,2
Union All Select 3,2,3
GOselect *
from table1
where A in (select A from table1 group by A,B having count(1) > 1)