求SQL语句

[处处留心]

举个例子：
表名： person
字段： Name sex age（整数，例如：1，2，3，4） CardNo
现在我想检索出，性别是女的，某个年龄段人数大于1的所有人的信息集合

举个例子，如果表中，28岁女性数目有二个人以上，就取出年龄在28岁的所有女性的信息
select t.CardNo count(t.CardNo), t.Name, t.age ,t.sex from person t where t.sex = '女' and count(t.CardNo) > 1 Group by t.Age , t.CardNo

这个SQL语句是错的。但是我想要表达的意思



求指教

[碧水幽幽泉]

某个年龄段人数大于1的所有人的信息集合，麻烦详细解释下。

[mayanzs]

 select   t.CardNo,  t.Name,  t.age ,t.sex ,count(t.CardNo) as count_num from person  t
   where   t.sex = '女' 
   Group   by   t.CardNo,  t.Name,  t.age ,t.sex
   having count(t.CardNo) > 1

[mayanzs]

with ag as (select t.age,count(t.CardNo) as count_n from person t where t.sex='女' group by t.age having count(t.CardNo)>1)
select * from person u where u.sex='女' and exists (select 1 from ag where ag.age=u.age);

[Tiger_Zhao]

    SELECT t.*
      FROM ( -- 先统计出一人以上的年龄段
                SELECT age
                  FROM person
                 WHERE sex = '女'
              GROUP BY age
                HAVING COUNT(*) > 1
           ) s
      JOIN person t
        ON s.age = t.age -- 再用年龄段过滤明细
     WHERE t.sex = '女'
  ORDER BY t.Age , t.CardNo

[印度张三]

 select * from person where name in (
 select Name from   person where sex ='女' and  age=28 
 )
 

[塑料勺]

select * from person t where (select count(*) from person s where sex = '女' and age = 28) >= 2 and sex = '女' and age = 28

[越烟]

select * from ( select p.*, count(1) over(partition by age) cnt from person where sex='女' ) where cnt>=2 楼主这个查询可以满足你的要求， 速度最快。 这个是oracle 分析函数， 我看其他人也没有写......

[惑先生]

select * from ( select t.CardNo count(t.CardNo) over(partition by t.age) as cnt, t.Name, t.age ,t.sex from person t where t.sex = '女' ) where cnt > 1

[zy18755122285]