mysql习题03

Mysql中表student_table(id,name,birth,sex)，分别查询男生、女生的最大、最小出生日期，如下SQL正确的是（）？

A：select sex ,

min(birth) as min_birth ,

max(birth) as max_birth

from student_table where sex = '男'

group by sex

union all

select sex ,

max(birth) as max_birth ,

min(birth) as min_birth

from student_table where sex = '女'

group by sex ;

B：select sex ,

max(birth) as max_birth ,

min(birth) as min_birth

from student_table where sex = ‘男' or sex = '女';

C：select sex ,

max(birth) as max_birth ,

min(birth) as min_birth

from student_table;

D：select sex ,

max(birth) as max_birth ,

min(birth) as min_birth

from student_table

group by sex ;

答案：D

解析：A中union all时，前后多个子表的字段顺序不一致时，会以第一个子表的字段顺序为准显示出来，所以女生的max_birth会作为min_birth显示出来，结果错误。BC执行时都会报错【1140 - In aggregated query without GROUP BY】，需要加上【group by sex】才行！正确选D。