數據

產品表：Product+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ product_id 是這個表的主鍵. 銷售表：Sales+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+ 這個表沒有主鍵，它可以有重復的行. product_id 是 Product 表的外鍵.

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/sales-analysis-i
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。

1082. 銷售分析 I

DROP table if exists Product, Sales; Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int); Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int); Truncate table Product; insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000'); insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800'); insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400'); Truncate table Sales; insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000'); insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800'); insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800'); insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800');

題目

編寫一個 SQL 查詢，查詢總銷售額最高的銷售者，如果有并列的，就都展示出來。

查詢結果格式如下所示：

Product 表： +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+Sales 表： +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+Result 表： +-------------+ | seller_id | +-------------+ | 1 | | 3 | +-------------+ Id 為 1 和 3 的銷售者，銷售總金額都為最高的 2800。

解題記錄

• 通過銷售id聚合得到員工銷售總額
• 然后篩選出銷售總額和最高銷售總額相同的員工

with t as (select seller_id, sum(price) total from Sales group by seller_id) select seller_id from t where total = (select max(total) from t)

1083. 銷售分析 II

題目

編寫一個 SQL 查詢，查詢購買了 S8 手機卻沒有購買 iPhone 的買家。注意這里 S8 和 iPhone 是 Product 表中的產品。

查詢結果格式如下圖表示：

Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+Sales table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 1 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 3 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+Result table: +-------------+ | buyer_id | +-------------+ | 1 | +-------------+ id 為 1 的買家購買了一部 S8，但是卻沒有購買 iPhone，而 id 為 3 的買家卻同時購買了這 2 部手機。

解題記錄

• 優先合并表將產品名稱合并，并通過買者id和產品名稱去重
• 然后通過設置分數來再求和來區分產品情況，類似2進制表示，S8：10, iphone：1
• 10就是只有S8沒有iphone，11就是有s8且有iphone，1就是只有iphone，0就是都沒有

with t as (select s.buyer_id, p.product_name from Sales s inner join Product p on s.product_id = p.product_id group by s.buyer_id, p.product_name),t1 as (select buyer_id, case when product_name='S8' then 10 when product_name='iPhone' then 1 else 0 end cost from t),t2 as (select buyer_id, sum(cost) cost from t1 group by buyer_id) select buyer_id from t2 where cost=10

1084. 銷售分析III

題目

編寫一個SQL查詢，報告2019年春季才售出的產品。即僅在2019-01-01至2019-03-31（含）之間出售的商品。

查詢結果格式如下所示：

Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+Sales table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+Result table: +-------------+--------------+ | product_id | product_name | +-------------+--------------+ | 1 | S8 | +-------------+--------------+ id為1的產品僅在2019年春季銷售，其他兩個產品在之后銷售。

解題記錄

• 通過對時間判斷獲取是否在產品春季銷售并去重
• 然后通過獲取只在春季銷售的產品，然后通過join獲取產品名稱

with t as (select product_id, IF((sale_date between DATE '2019-01-01' and DATE '2019-03-31'), 10, 1) inSpringfrom Salesgroup by product_id, inSpring),t2 as (select product_id, sum(inSpring) cost from t group by product_id) select t2.product_id, p.product_name from t2left join Product p on t2.product_id = p.product_id where cost = 10

總結

以上是生活随笔為你收集整理的SQL：1082. 销售分析 1-3的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。