select count 函数的应用

[winsonink]

表大概是这样的,
id name county
1 tracy 日本
2 john 美国
3 sam 日本
4 revoke 德国
5 mine 美国

查询存在相同的国家的人物信息

id name county
1 tracy 日本
2 john 美国
3 sam 日本
5 mine 美国

[中国风]

select * from table1 as a where exists(select 1 from table1 where county=a.county and ID<>a.ID)

[中国风]

or

select * from table1 as a where exists(select 1 from table1 where county=a.county and name <>a.name )

[黄_瓜]

select * from table1 as a where exists(select 1 from table1 where county=a.county and ID<>a.ID)

[-晴天]

select * from tb a
where exists(select 1 from tb where name<>a.name and county=a.county)

[geniuswjt]

被秒了，路过

[黄_瓜]

--count


select * from [tb] where [county] in(select [county] from tb group by [county] having count(*)>1)

[raymonshi]

select * from [tb] where [county] in(select [county] from tb group by [county] having count(*)>1)

觉得这个思路比较好

[winsonink]

国家是一张独立的表,不能直接group by。。。