查询某个表中某两个字段数据一致的所有记录
如:
表A
a1 a2 a3 a4
1 1 aa b
2 1 aa bb
3 1 aa bbb
4 2 bb a
5 3 cc d
6 4 bb f
7 2 bb aa 要查询的结果就是
a1 a2 a3 a4
1 1 aa b
2 1 aa bb
3 1 aa bbb
4 2 bb a
7 2 bb aa
即count("a2+a3")>1
如:
表A
a1 a2 a3 a4
1 1 aa b
2 1 aa bb
3 1 aa bbb
4 2 bb a
5 3 cc d
6 4 bb f
7 2 bb aa 要查询的结果就是
a1 a2 a3 a4
1 1 aa b
2 1 aa bb
3 1 aa bbb
4 2 bb a
7 2 bb aa
即count("a2+a3")>1
6 4 bb f
(select a2 , a3 from a group by a2 , a3 having count(*) > 1) n where n.a2 = t.a2 and n.a3 = t.a3
)
insert into a values(1 , 1 , 'aa' , 'b')
insert into a values(2 , 1 , 'aa' , 'bb')
insert into a values(3 , 1 , 'aa' , 'bbb')
insert into a values(4 , 2 , 'bb' , 'a')
insert into a values(5 , 3 , 'cc' , 'd')
insert into a values(6 , 4 , 'bb' , 'f')
insert into a values(7 , 2 , 'bb' , 'aa')
goselect t.* from a t where exists(select 1 from
(select a2 , a3 from a group by a2 , a3 having count(*) > 1) n where n.a2 = t.a2 and n.a3 = t.a3
)drop table a /*
a1 a2 a3 a4
----------- ----------- ---------- ----------
1 1 aa b
2 1 aa bb
3 1 aa bbb
4 2 bb a
7 2 bb aa(所影响的行数为 5 行)
*/
create table land
(
a1 int,
a2 int,
a3 nchar(10),
a4 nchar(10)
)
insert into land
select 1 , 1 , 'aa' , 'b' union all
select 2, 1 , 'aa' , 'bb' union all
select 3, 1 , 'aa' , 'bbb' union all
select 4 , 2 , 'bb' , 'a' union all
select 5 , 3 , 'cc' , 'd' union all
select 6 , 4 , 'bb' , 'f' union all
select 7 , 2 , 'bb' , 'aa '
------------------------------------------------------
select * from land A
where exists
(select 1 from land where A.a1<>land.a1 and cast(a2 as nvarchar(10))+a3=cast(A.a2 as nvarchar(10))+A.a3 )
--------------------------------------------------------
a1 a2 a3 a4
---- --------- --------- --------------
1 1 aa b
2 1 aa bb
3 1 aa bbb
4 2 bb a
7 2 bb aa