举个例子:
表名: 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语句是错的。但是我想要表达的意思求指教
表名: 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语句是错的。但是我想要表达的意思求指教
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
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);
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 Name from person where sex ='女' and age=28
)
from person t
where (select count(*) from person s where sex = '女' and age = 28) >= 2 and
sex = '女' and
age = 28