首页 > 数据库技术 > 详细

MySQL笔记9

时间:2018-09-09 19:09:22      阅读:186      评论:0      收藏:0      [点我收藏+]

1.Left and Right joins

‘‘‘
A right outer join will include all of the rows of the table to the right of the RIGHT JOIN clause.
‘‘‘
%%sql
SELECT r.dog_guid AS rDogID, d.dog_guid AS dDogID, r.user_guid AS rUserID, d.user_guid AS dUserID, AVG(r.rating) AS AvgRating, COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type
FROM dogs d RIGHT JOIN reviews r
  ON r.dog_guid=d.dog_guid AND r.user_guid=d.user_guid
WHERE r.dog_guid IS NOT NULL
GROUP BY r.dog_guid
HAVING NumRatings >= 10
ORDER BY AvgRating DESC
LIMIT 10;

Example2

%%sql 
SELECT DISTINCT u.user_guid AS UserID, COUNT(d.dog_guid) AS Numberdogs
FROM users u LEFT JOIN dogs d
ON u.user_guid = d.user_guid
GROUP BY UserID 
ORDER BY Numberdogs DESC
LIMIT 10;

Example3:

‘‘‘
Question 10: How would you write a query that used a left join to return the number of distinct user_guids that were in the users table, but not the dogs table (your query should return a value of 2226)?
‘‘‘
%%sql 
SELECT  COUNT(DISTINCT u.user_guid) AS Number
FROM  users u LEFT JOIN  dogs d
ON u.user_guid = d.user_guid
WHERE d.user_guid IS NULL;

2.关于 COUNT DISTINCT 的注意点。

‘‘‘
Thats because COUNT DISTINCT does NOT count NULL values, 
while SELECT/GROUP BY clauses roll up NULL values into one group.
If you want to infer the number of distinct entries from the results of a query using joins and GROUP BY clauses,
remember to include an "IS NOT NULL" clause to ensure you are not counting NULL values ‘‘‘

 

MySQL笔记9

原文:https://www.cnblogs.com/Shinered/p/9614448.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!