一、题目

Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+

• 此表主键是 user_id。
• 表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。  

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+

• 此表主键是 order_id。
• 外键是 item_id 和（buyer_id，seller_id）。  

Table: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+

此表主键是 item_id。  

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

以 任意顺序 返回结果表。

查询结果格式如下。

示例 1:

输入：
Users 表:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2018-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2018-08-04 | 1       | 4        | 2         |
| 5        | 2018-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
输出：
+-----------+------------+----------------+
| buyer_id  | join_date  | orders_in_2019 |
+-----------+------------+----------------+
| 1         | 2018-01-01 | 1              |
| 2         | 2018-02-09 | 2              |
| 3         | 2018-01-19 | 0              |
| 4         | 2018-05-21 | 0              |
+-----------+------------+----------------+

二、解题思路

创建数据表

根据题意创建数据表并填充数据

CREATE TABLE IF NOT EXISTS Users (
    user_id INT, 
    join_date DATE, 
    favorite_brand VARCHAR(10)
);
CREATE TABLE IF NOT EXISTS Orders (
    order_id INT, 
    order_date DATE, 
    item_id INT, 
    buyer_id INT, 
    seller_id INT
);
CREATE TABLE IF NOT EXISTS Items (
    item_id INT, 
    item_brand VARCHAR(10)
);

INSERT INTO Users VALUES
(1, '2018-01-01', 'Lenovo'),
(2, '2018-02-09', 'Samsung'),
(3, '2018-01-19', 'LG'),
(4, '2018-05-21', 'HP');

INSERT INTO Orders VALUES
(1, '2019-08-01', 4, 1, 2),
(2, '2018-08-02', 2, 1, 3),
(3, '2019-08-03', 3, 2, 3),
(4, '2018-08-04', 1, 4, 2),
(5, '2018-08-04', 1, 3, 4),
(6, '2019-08-05', 2, 2, 4);

INSERT INTO Items VALUES
(1, 'Samsung'),
(2, 'Lenovo'),
(3, 'LG'),
(4, 'HP');

解题

• 本题有两个要求，第一是查询每个用户的注册时间，第二是查询用户2019年买的订单总数
• 首先查找用户2019年的订单，判断YEAR(order_date)='2019',得到2019年的订单
• 计算2019年每个用户的订单数，这里使用group by 分组，用count计算
• 现在查询每个用户的注册时间，使用左连接查询 将第二个条件做为左连接的第二个表，条件为Users.user_id = 2019dates.buyer_id;
• 这里将user_id的假名为buyer_id,orders_in_2019的数据通过ifnull处理，如果有订单就填充订单，没有则填充0
• 最后，执行语句得到结果

SELECT Users.user_id AS buyer_id, join_date, IFNULL(2019dates.cnt, 0) orders_in_2019 
FROM Users
LEFT JOIN (SELECT buyer_id, COUNT(order_id) cnt 
FROM Orders
WHERE YEAR(order_date)='2019'
GROUP BY buyer_id) 2019dates
ON Users.user_id = 2019dates.buyer_id;

• 显示结果

三、执行语句

测试结果

四、总结

本题读题需要细读，查询2019年的订单还好，主要是要将第二个条件查询的结果集作为第二张表进行左连接查询，设置订单的数量的ifnull，都需要注意。