返回身份證號(hào)相同，但性別不同的信息
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;
將查詢出的兩列數(shù)據(jù) 合并成一列 并去重。表明為union_1
1464057850931296.png
select nid from union_1
union
select mid from union_1
用mysql語(yǔ)句實(shí)現(xiàn) 右邊那樣的結(jié)果
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;
查詢兩門及兩門以上不及格同學(xué)的平均分
1464061334369148.png
select name,avg(score),sum(score<60)as gk from stu
group by name having gk>=2;
