SQL获取重复数据 （具有两种性别的名字和对应的人数）

有表TableA, 包含列Name, Gender, Number(人数），某个人名在一种性别中是唯一的，如Mary F只有一行；在两种性别中可能唯一也可能不唯一, 如Mary 有F也有M, 而Marylin只有F没有M。目标是获取具有两种性别的名字和对应的人数， 如Mary F 3745809 , Mary M 9643

尝试一下
SELECT *
FROM
TABLEA
GROUP BY name, gender
HAVING COUNT(name) > 1
错误提示：Star expansion expression references column total which is neither grouped nor aggregated

1.select后的内容必须是group by之后的字段，或者接聚合函数内容，你的sql语法有误。
2.sql方法①where exists：

select t.name,t.gender,t.number 
  from tableA t 
    where exists
(select 1 from tableA t1 
    join tableA t2 
       on t1.name=t2.name and t1.gender<>t2.gender 
    where t.name=t1.name)

方法②:in 方式

select t.name,t.gender,t.number 
  from tableA t 
    where t.name in 
(select t1.name from tableA t1 
    join tableA t2 
       on t1.name=t2.name and t1.gender<>t2.gender)

方法③:

select t.name,t.gender,t.number 
  from tableA t 
   where t.name in
(select t1.name from tableA t1 
    group by t1.name 
  having count(*)>1)

方法④:同理把方法三改为where exists方式

select name,count(*) ,count(distinct gender) from tableA group by name having count(distinct gender)=2

举个栗子：大于1的数据就是重复数据
SELECT * FROM (
select *,ROW_NUMBER()OVER(PARTITION BY PRODNAME,PRODCODE ORDER BY SPEC) RO  FROM (
SELECT '001' PRODCODE,'沐浴露' PRODNAME,'300ml' SPEC ,'30' PRICE,'80' TQTY
UNION ALL
SELECT '002' PRODCODE,'洗发水' PRODNAME,'500ml' SPEC ,'40' PRICE,'40' TQTY
UNION ALL 
SELECT '001' PRODCODE,'沐浴露' PRODNAME,'300ml' SPEC ,'30' PRICE,'80' TQTY) A ) A 
WHERE RO > 1 

你直接把having 去掉不就可以了吗