join查询通常是代替子查询，典型的join查询是自连接，即自己和自己join起来查询数据。
练习一：
从下表中找出英语成绩=100的该同学的其他科目的成绩
image.png
方法一：子查询
SELECT * FROM student
WHERE
NAME IN ( SELECT NAME FROM student WHERE SUBJECT = 'Math' AND score = 100 );
方法二：自连接查询
SELECT a.id,a.name,a.subject,a.score
FROM student a
JOIN student b ON a.NAME = b.NAME
AND b.SUBJECT = 'math'
AND b.score = 100;
练习二：
从下表中筛选出每个类别的销量前三的商品
image.png
方法一：
子查询
SELECT F.ID,F.Productname,F.Typename,F.salecount
FROM fruit F
WHERE 3>(SELECT COUNT(*) FROM fruit WHERE Typename =F.Typename AND salecount>F.salecount)
ORDER BY F.Typename,F.salecount DESC
方法二：
自连接
SELECT F.ID,F.Productname,F.Typename,F.salecount
FROM fruit F LEFT join fruit L_F
ON F.Typename = L_F.Typename AND F.salecount < L_F.salecount
GROUP BY F.ID#进行分组聚合，也可以进行排重，与distinct关键字作用类似
HAVING COUNT(F.ID)< 3#分组聚合后次数小于3的才被选出来
ORDER BY F.Typename,F.salecount DESC
关于上面自连接的逻辑解释：
fruit表自身左连接，会从笛卡尔积表中取出满足on条件的记录，并将左表中剩余部分也补足进去，这样的话，F.salecount<L_F.salecount,就会形成：最大的salecount的那个类别出现一次（这是左表中剩余部分补足进去的），第二大的salecount的那个类别在自身左连接过程中，出现一次，因为它只比最大的那个小，其次，第三大的那个salecount会出现两次，因此在group by 后的having中，对ID进行分组聚合后是count(id)<3可以得出各个分类中salecount排名前三的那些。
上面的解法有一个bug，如果水果类目里面，还有一个‘火龙果’价格也是866，那么销量第三高的樱桃的677就会选不出来，就得不到想要的每个类目的前三名。
问题出现在 having count()那里，因为将左表中的677与右表进行比较时，遇到右表的两个866，会返回两次677，这样就增加了677的count次数，遇到897时一次，遇到866时两次，这样就有了三次677，在进行having count（l.id）<3时就不会得到677。可以用右表来限制取值，对左表的l.id进行group by以后，与左表进行比较的右表中的比相应左表大的值也会划分进左表的group by里面，这时取having(distinct L_F.salecount)<3,逻辑是右表中比左表相应数据大的数据的distinct值小于3个，那么左表的值就排前三，比如，最表是677，右表是相应的866，866，897，distinct后是两个数值比左表大，那么左表的这个值就排行老三，就被取出来了，左表的866和897是同样的道理。
image.png
练习三：
person表：
image.png
movie表：
image.png
求：person表中各ID对应的最高的duration的movie表中的category，取出p_id和category
方法一：子查询
SELECT p_id,category
FROM person INNER JOIN movie ON movie_id = id
WHERE
movie_id IN ( SELECT movie_id FROM person GROUP BY p_id HAVING duration = max( duration ) );
方法二：连接查询
SELECT p_id,category
FROM ( SELECT p_id, movie_id, duration FROM person GROUP BY p_id HAVING duration = max( duration ) ) a
JOIN
( SELECT * FROM movie ) b
ON a.p_id = b.id;
网友评论