Mysql中表student_table(id,name,birth,sex),分别查询男生、女生的最大、最小出生日期,如下SQL正确的是()?
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 ;
select sex ,max(birth) as max_birth ,
min(birth) as min_birthfrom student_table where sex = '男' or sex = '女';
select sex ,max(birth) as max_birth ,
min(birth) as min_birth
from student_table;
select sex ,max(birth) as max_birth ,
min(birth) as min_birth
from student_table
group by sex ;
DROP TABLE IF EXISTS student_table; CREATE TABLE student_table( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), birth DATETIME, sex CHAR(1) ); INSERT INTO student_table VALUES(NULL,'李明','1998-01-01','男'), (NULL,'李华','1997-01-01','女'), (NULL,'张三','1999-01-01','男'), (NULL,'李四','2000-01-01','女'), (NULL,'王五','1995-01-01','男'), (NULL,'小刘','1996-01-01','女');自己试了一个才弄明白,D选项是对的,A选项是错的,错误原因在于UNION的前后max与min的位置没对应,如果前后max和min位置对应也就争取了,如果没明白的朋友也可以带入代码试一下!
SELECT sex, max( birth ) AS max_birth, min( birth ) AS min_birth FROM student_table GROUP BY sex;
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;
SELECT sex, min( birth ) AS min_birth, max( birth ) AS max_birth FROM student_table WHERE sex = '男' GROUP BY sex UNION ALL SELECT sex, -- 下面两行换一下位置就对了 min( birth ) AS min_birth, max( birth ) AS max_birth FROM student_table WHERE sex = '女' GROUP BY sex;
A 选项中的 SQL 语句会查询男生的最大和最小出生日期,再查询女生的最大和最小出生日期,并将两个结果合并在一起,这可能不是需要的结果。
B 选项中的 SQL 语句会查询所有男生和女生的最大和最小出生日期,不会按照性别分组。
C 选项中的 SQL 语句会查询所有学生的最大和最小出生日期,不会按照性别分组。