Leetcode1158. 市场分析 I（中等）

题目
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: Item

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

此表主键是 item_id。

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

查询结果格式如下：

Users table:

+---------+------------+----------------+
| 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 table:

+----------+------------+---------+----------+-----------+
| 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 table:

+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+

Result table:

+-----------+------------+----------------+
| 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 Users1 (user_id INT, join_date DATE, favorite_brand VARCHAR(20))
CREATE TABLE Orders4 (order_id INT, order_date DATE, item_id INT, buyer_id INT, seller_id INT)
CREATE TABLE Items (item_id INT, item_brand VARCHAR(20))
 
INSERT INTO Users1 (user_id, join_date, favorite_brand) VALUES ('1', '2018-01-01', 'Lenovo');
INSERT INTO Users1 (user_id, join_date, favorite_brand) VALUES ('2', '2018-02-09', 'Samsung');
INSERT INTO Users1 (user_id, join_date, favorite_brand) VALUES ('3', '2018-01-19', 'LG');
INSERT INTO Users1 (user_id, join_date, favorite_brand) VALUES ('4', '2018-05-21', 'HP');

INSERT INTO Orders4 (order_id, order_date, item_id, buyer_id, seller_id) VALUES ('1', '2019-08-01', '4', '1', '2');
INSERT INTO Orders4 (order_id, order_date, item_id, buyer_id, seller_id) VALUES ('2', '2018-08-02', '2', '1', '3');
INSERT INTO Orders4 (order_id, order_date, item_id, buyer_id, seller_id) VALUES ('3', '2019-08-03', '3', '2', '3');
INSERT INTO Orders4 (order_id, order_date, item_id, buyer_id, seller_id) VALUES ('4', '2018-08-04', '1', '4', '2');
INSERT INTO Orders4 (order_id, order_date, item_id, buyer_id, seller_id) VALUES ('5', '2018-08-04', '1', '3', '4');
INSERT INTO Orders4 (order_id, order_date, item_id, buyer_id, seller_id) VALUES ('6', '2019-08-05', '2', '2', '4');
 
INSERT INTO Items (item_id, item_brand) VALUES ('1', 'Samsung');
INSERT INTO Items (item_id, item_brand) VALUES ('2', 'Lenovo');
INSERT INTO Items (item_id, item_brand) VALUES ('3', 'LG');
INSERT INTO Items (item_id, item_brand) VALUES ('4', 'HP');

解答
查询每个用户的注册日期和在 2019 年作为买家的订单总数。

先查询2019 年作为买家的订单总数

SELECT O.`buyer_id`, COUNT(O.buyer_id) AS cnt_2019
FROM Orders4 AS O
WHERE O.`order_date` BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY O.`buyer_id`;

与user表进行连接即可

SELECT U.`user_id`, U.`join_date`, IFNULL(tmp.cnt_2019, 0) AS orders_in_2019
FROM Users1 AS U
LEFT JOIN (SELECT O.`buyer_id`, COUNT(O.buyer_id) AS cnt_2019
FROM Orders4 AS O
WHERE O.`order_date` BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY O.`buyer_id`) AS tmp
ON U.`user_id` = tmp.buyer_id;

选2019年的字段可以用 year(order_date)='2019'

select u.user_id buyer_id, u.join_date, ifnull(t.cnt,0) orders_in_2019
from Users u
left join
(select buyer_id, count(distinct order_id) cnt
from orders
where year(order_date)='2019'
group by buyer_id) t
on u.user_id=t.buyer_id;
————————————————
版权声明：本文为CSDN博主「红楼终究一场梦」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Hello_JavaScript/java/article/details/104610178