mysql 练习题收集

返回身份证号相同，但性别不同的信息

1464056596539971.png

select A.name,A.people_id from
(select name,people_id,count(distinct sex) as 'count' from people  group by name,people_id) as A  
where A.count= 2;

将查询出的两列数据 合并成一列 并去重。表明为union_1

1464057850931296.png

select nid from union_1
union
select mid from union_1

用mysql语句实现 右边那样的结果

SELECT a.ver,a.kid,a.mid  FROM top3 a 
inner join top3 b  on a.ver =b.ver and a.kid=b.kid and a.mid<=b.mid
group by a.ver,a.kid,a.mid having count(*)<=3
order by a.ver, a.kid desc, a.mid desc;

查询两门及两门以上不及格同学的平均分

1464061334369148.png

select name,avg(score),sum(score<60)as gk from stu 
group by name having gk>=2;