1596. 每位顾客最经常订购的商品

Create table If Not Exists Customers (customer_id int, name varchar(10))

Create table If Not Exists Orders (order_id int, order_date date, customer_id int, product_id int)

Create table If Not Exists Products (product_id int, product_name varchar(20), price int)

Truncate table Customers

insert into Customers (customer_id, name) values ('1', 'Alice')

insert into Customers (customer_id, name) values ('2', 'Bob')

insert into Customers (customer_id, name) values ('3', 'Tom')

insert into Customers (customer_id, name) values ('4', 'Jerry')

insert into Customers (customer_id, name) values ('5', 'John')

Truncate table Orders

insert into Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1')

insert into Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2')

insert into Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3')

insert into Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1')

insert into Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2')

insert into Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1')

insert into Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '3')

insert into Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2')

insert into Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3')

insert into Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2')

Truncate table Products

insert into Products (product_id, product_name, price) values ('1', 'keyboard', '120')

insert into Products (product_id, product_name, price) values ('2', 'mouse', '80')

insert into Products (product_id, product_name, price) values ('3', 'screen', '600')

insert into Products (product_id, product_name, price) values ('4', 'hard disk', '450')

 

写一个 SQL 语句，找到每一个顾客最经常订购的商品。

结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。

返回结果 没有顺序要求。

查询结果格式如下例所示。

with temp as 

(

    select customer_id, product_id, count(*) as cnt from Orders

    group by customer_id, product_id

    order by customer_id, product_id, count(*) desc

)

select a.customer_id, a.product_id, b.product_name from 

(   select customer_id, product_id from temp 

    where (customer_id, cnt) in (select customer_id, max(cnt) from temp group by customer_id)) a

left join Products b

on a.product_id  = b.product_id