Mysql中表student_table(id,name,birth,sex),查询男生、女生人数分别最多的3个姓氏及人数,正确的SQL是()?
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1
group by first_name , sex order by sex ,c1 desc limit 3 ;
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '男'
group by first_name order by sex ,c1 desc limit 3
union all
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '女'
group by first_name order by sex ,c1 desc limit 3 ;
select * from (
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '男'
group by first_name order by sex ,c1 desc limit 3
) t1
UNION all
select * from (
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '女'
group by first_name order by sex ,c1 desc limit 3
) t2 ;
select * from (
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '男'
group by first_name having c1 >=3 order by sex ,c1 desc
) t1
UNION all
select * from (
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '女'
group by first_name having c1 >=3 order by sex ,c1 desc limit 3
) t2 ;