表A 字段 id name
需要查询结果:显示有重复姓名的信息及重复条数id name count(name)目前我试验的一种方式可以查出这个结果:
select id ,name, con from A,(select count(name) con from A group by name having count(name)>1) 但记得曾经有人指导过有其他写法,貌似直接在前边加个(select 。)就可以,本人技术有限,没能领会其意,望高手给丢个方法吧
需要查询结果:显示有重复姓名的信息及重复条数id name count(name)目前我试验的一种方式可以查出这个结果:
select id ,name, con from A,(select count(name) con from A group by name having count(name)>1) 但记得曾经有人指导过有其他写法,貌似直接在前边加个(select 。)就可以,本人技术有限,没能领会其意,望高手给丢个方法吧
SELECT '1' as a,'A' AS b from dual
union all
SELECT '1' as a,'A' AS b from dual
union all
SELECT '2' as a,'B' AS b from dual
union all
SELECT '3' as a,'C' AS b from dual
union all
SELECT '3' as a,'C' AS b from dual
)
select * from (
SELECT A,B,COUNT(*) as result FROM TEST GROUP BY A,B) where result >1是不是这样子?