【高频SQL基础50题】16-20

day by day.

1.进店却未进行过交易的顾客

2.项目员工 I

3.销售分析III

4. 判断三角形

5. 电影评分

1.进店却未进行过交易的顾客

连接题。

思路：根据trans表中的visit_id号在 visits表中排除，再将剩下的合并相同客户（累加visit次数）后返回客户id即可。

左连接

原来左连接不需要手动调整，自动对齐，空位自动补null。

# Write your MySQL query statement below
SELECT customer_id,count(customer_id) count_no_trans
FROM Visits v
LEFT JOIN transactions t
ON v.visit_id=t.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;

2.项目员工 I

聚合函数题+左连接题.

使用avg函数

# Write your MySQL query statement below
SELECT project_id,ROUND(AVG(e.experience_years),2) AS average_years
FROM Project p 
LEFT JOIN Employee e
ON p.employee_id=e.employee_id
GROUP BY project_id;

3.销售分析III

只需要去除重复但超出时间的售出商品即可。

在这个时间内售出的商品数量等于总商品数量。

# Write your MySQL query statement below
SELECT sales.product_id AS product_id,product.product_name AS product_name
FROM sales LEFT JOIN product 
ON sales.product_id=product.product_id
GROUP BY product_id
HAVING COUNT(sale_date BETWEEN '2019-01-01' AND '2019-03-31' OR NULL)=COUNT(*)

4. 判断三角形

高级查询和连接题。

# Write your MySQL query statement below
SELECT x,y,z,
     CASE 
          WHEN x+y>z AND x+z>y AND y+z>x THEN 'Yes'
          ELSE 'No'
     END AS 'triangle'
FROM
     triangle
;

5. 电影评分

子查询题

# Write your MySQL query statement below
(SELECT u.name AS results
FROM Users u
LEFT JOIN MovieRating mr ON u.user_id=mr.user_id
GROUP BY u.user_id
ORDER BY COUNT(*) DESC,name ASC
LIMIT 1
)

UNION ALL

(SELECT title AS results
FROM Movies m
LEFT JOIN MovieRating mr 
ON m.movie_id=mr.movie_id AND YEAR(mr.created_at)=2020 AND MONTH(mr.created_at)=2
GROUP BY mr.movie_id
ORDER BY AVG(mr.rating) DESC,title
LIMIT 1
);