sql力扣刷题八

1378. 使用唯一标识码替换员工ID

Create table If Not Exists Employees (id int, name varchar(20))
Create table If Not Exists EmployeeUNI (id int, unique_id int)
Truncate table Employees
insert into Employees (id, name) values ('1', 'Alice')
insert into Employees (id, name) values ('7', 'Bob')
insert into Employees (id, name) values ('11', 'Meir')
insert into Employees (id, name) values ('90', 'Winston')
insert into Employees (id, name) values ('3', 'Jonathan')
Truncate table EmployeeUNI
insert into EmployeeUNI (id, unique_id) values ('3', '1')
insert into EmployeeUNI (id, unique_id) values ('11', '2')
insert into EmployeeUNI (id, unique_id) values ('90', '3')

Employees 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。

EmployeeUNI 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+
(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。

写一段SQL查询来展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

你可以以 任意 顺序返回结果表。

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

示例 1：

输入：
Employees 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 7  | Bob      |
| 11 | Meir     |
| 90 | Winston  |
| 3  | Jonathan |
+----+----------+
EmployeeUNI 表:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3  | 1         |
| 11 | 2         |
| 90 | 3         |
+----+-----------+
输出：
+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+
解释：
Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。

题解一

SELECT IFNULL(e2.unique_id, NULL) AS 'unique_id',
    e1.name AS 'name'
FROM Employees AS e1
    LEFT OUTER JOIN EmployeeUNI AS e2
    ON e1.id = e2.id
;

select unique_id,name from Employees e left join EmployeeUNI en on e.id = en.id; 

1384. 按年度列出销售总额

Create table If Not Exists Product (product_id int, product_name varchar(30))
Create table If Not Exists Sales (product_id int, period_start date, period_end date, average_daily_sales int)
Truncate table Product
insert into Product (product_id, product_name) values ('1', 'LC Phone ')
insert into Product (product_id, product_name) values ('2', 'LC T-Shirt')
insert into Product (product_id, product_name) values ('3', 'LC Keychain')
Truncate table Sales
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('1', '2019-01-25', '2019-02-28', '100')
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('2', '2018-12-01', '2020-01-01', '10')
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('3', '2019-12-01', '2020-01-31', '1')

Product 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+
product_id 是这张表的主键。
product_name 是产品的名称。

Sales 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | date    |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+
product_id 是这张表的主键。
period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。

编写一段 SQL 查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。

销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。

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

示例 1：

输入：
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1          | LC Phone     |
| 2          | LC T-Shirt   |
| 3          | LC Keychain  |
+------------+--------------+
Sales table:
+------------+--------------+-------------+---------------------+
| product_id | period_start | period_end  | average_daily_sales |
+------------+--------------+-------------+---------------------+
| 1          | 2019-01-25   | 2019-02-28  | 100                 |
| 2          | 2018-12-01   | 2020-01-01  | 10                  |
| 3          | 2019-12-01   | 2020-01-31  | 1                   |
+------------+--------------+-------------+---------------------+
输出：
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1          | LC Phone     |    2019     | 3500         |
| 2          | LC T-Shirt   |    2018     | 310          |
| 2          | LC T-Shirt   |    2019     | 3650         |
| 2          | LC T-Shirt   |    2020     | 10           |
| 3          | LC Keychain  |    2019     | 31           |
| 3          | LC Keychain  |    2020     | 31           |
+------------+--------------+-------------+--------------+
解释：
LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是31*1=31、31*1=31。

题解一

select s.product_id, p.product_name, y.year report_year, s.average_daily_sales * (if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) + 1) total_amount
from Sales s
inner join (
    select '2018' year, 365 days_of_year
    union all select '2019' year, 365 days_of_year
    union all select '2020' year, 366 days_of_year
) y on year(s.period_start) <= y.year and year(s.period_end) >= y.year
inner join Product p on p.product_id = s.product_id
order by s.product_id, y.year

题解二

with recursive Dates(dates) as
(
    select min(period_start) 
    from Sales 
    union all 
    select  dates + interval 1 day from Dates
    where dates + interval 1 day <= (select max(period_end) from Sales)

),
Trans as
(
    select s.product_id,product_name,period_start,period_end,average_daily_sales
    from Sales s 
    left join Product p 
    on s.product_id = p.product_id
)
select product_id,product_name,date_format(dates,'%Y') as report_year 
        ,sum(average_daily_sales) total_amount
from Dates 
join Trans
on dates between period_start and period_end
and date_format(dates,'%Y') between  "2018" and  "2020" 
group by product_id,product_name,report_year
order by product_id,report_year 

题解三

select t.product_id,p.product_name,report_year,
sum(average_daily_sales*(total_days+1)) as total_amount
from product p 
join (
select product_id,average_daily_sales, '2018' as report_year, 
case 
when period_start<='2018-01-01' and period_end>'2018-12-31' then datediff('2018-12-31','2018-01-01') 
when period_start<='2018-01-01' and period_end>='2018-01-01'and period_end<='2018-12-31' then datediff(period_end,'2018-01-01') 
when period_start>'2018-01-01' and period_start<='2018-12-31'and period_end>'2018-12-31' then datediff('2018-12-31',period_start) 
when period_start>'2018-01-01' and period_start<='2018-12-31' and period_end<'2018-12-31' then datediff(period_end,period_start)  
end as total_days
from sales
union all 
select product_id,average_daily_sales, '2019' as report_year, 
case 
when period_start<='2019-01-01' and period_end>'2019-12-31' then datediff('2019-12-31','2019-01-01') 
when period_start<='2019-01-01' and period_end>='2019-01-01'and period_end<='2019-12-31' then datediff(period_end,'2019-01-01') 
when period_start>'2019-01-01' and period_start<='2019-12-31'and period_end>'2019-12-31' then datediff('2019-12-31',period_start) 
when period_start>'2019-01-01' and period_start<='2019-12-31' and period_end<'2019-12-31' then datediff(period_end,period_start)  
end as total_days
from sales
union all 
select product_id,average_daily_sales, '2020' as report_year, 
case 
when period_start<='2020-01-01' and period_end>'2020-12-31' then datediff('2020-12-31','2020-01-01') 
when period_start<='2020-01-01' and period_end>='2020-01-01'and period_end<='2020-12-31' then datediff(period_end,'2020-01-01') 
when period_start>'2020-01-01' and period_start<='2020-12-31'and period_end>'2020-12-31' then datediff('2020-12-31',period_start) 
when period_start>'2020-01-01' and period_start<='2020-12-31' and period_end<'2020-12-31' then datediff(period_end,period_start)  
end as total_days
from sales) t 
on t.product_id=p.product_id
where total_days is not null 
group by 1,3
order by 1,3

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

Create table If Not Exists Customers (customer_id int, customer_name varchar(30))
Create table If Not Exists Orders (order_id int, customer_id int, product_name varchar(30))
Truncate table Customers
insert into Customers (customer_id, customer_name) values ('1', 'Daniel')
insert into Customers (customer_id, customer_name) values ('2', 'Diana')
insert into Customers (customer_id, customer_name) values ('3', 'Elizabeth')
insert into Customers (customer_id, customer_name) values ('4', 'Jhon')
Truncate table Orders
insert into Orders (order_id, customer_id, product_name) values ('10', '1', 'A')
insert into Orders (order_id, customer_id, product_name) values ('20', '1', 'B')
insert into Orders (order_id, customer_id, product_name) values ('30', '1', 'D')
insert into Orders (order_id, customer_id, product_name) values ('40', '1', 'C')
insert into Orders (order_id, customer_id, product_name) values ('50', '2', 'A')
insert into Orders (order_id, customer_id, product_name) values ('60', '3', 'A')
insert into Orders (order_id, customer_id, product_name) values ('70', '3', 'B')
insert into Orders (order_id, customer_id, product_name) values ('80', '3', 'D')
insert into Orders (order_id, customer_id, product_name) values ('90', '4', 'C')

Customers 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| customer_id         | int     |
| customer_name       | varchar |
+---------------------+---------+
customer_id 是这张表的主键。
customer_name 是顾客的名称。

Orders 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_name  | varchar |
+---------------+---------+
order_id 是这张表的主键。
customer_id 是购买了名为 "product_name" 产品顾客的id。

请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和 customer_name ），我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照 customer_id 排序。

查询结果如下例所示。

Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+

Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+

Result table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。

题解一

SELECT o.customer_id customer_id,
        c.customer_name customer_name
FROM Orders o
LEFT JOIN Customers c  USING(customer_id)
GROUP BY customer_id
HAVING 
        SUM(IF(product_name='A',1,0))>0 AND
        SUM(IF(product_name='B',1,0))>0 AND
        SUM(IF(product_name='C',1,0))=0

题解二

select customer_id, customer_name
from Customers 
where customer_id in (
select 
	customer_id
from Orders
group by customer_id
having 
	group_concat(distinct product_name) REGEXP '^A,B$|^A,B,[^C].*'
)

题解三

select c.customer_id, customer_name
from Customers c
where exists(
    select 1 from Orders o1 
    where c.customer_id=o1.customer_id and o1.product_name = 'A'
) and exists(
    select 1 from Orders o2 
    where c.customer_id=o2.customer_id and o2.product_name = 'B'
) and not exists(
    select 1 from Orders o3 
    where c.customer_id=o3.customer_id and o3.product_name = 'C'
)
order by customer_id

题解四

SELECT * FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Customers
    WHERE customer_id IN (
        SELECT customer_id FROM Orders
        WHERE product_name = 'A' )
    AND customer_id IN (
        SELECT customer_id FROM Orders
        WHERE product_name = 'B' )
    AND customer_id NOT IN (
        SELECT customer_id FROM Orders
        WHERE product_name = 'C' )
)

题解五

SELECT customer_id, customer_name
FROM Orders 
    LEFT JOIN Customers
    USING(customer_id) 
GROUP BY customer_id
    HAVING SUM(product_name = 'A') * SUM(product_name = 'B') > 0
    AND SUM(product_name = 'C') = 0
ORDER BY customer_id

题解六

SELECT customer_id, customer_name
FROM Customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM (  SELECT customer_id,
            (CASE WHEN product_name='A' THEN 100
                  WHEN product_name='B' THEN 10
                  WHEN product_name='C' THEN 1
             END ) AS product_num
            FROM Orders
         ) TT
    GROUP BY customer_id
    HAVING SUM( DISTINCT product_num ) = 110
    -- 注意DISTINCT，因为一个顾客可能买过多次同一产品
);

题解七

SELECT
    c.customer_id, c.customer_name
FROM
    Orders o LEFT JOIN Customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING
    SUM(product_name = 'A') * SUM(product_name = 'B') > 0
    AND SUM(product_name = 'C') = 0
ORDER BY c.customer_id

题解八

# Write your MySQL query statement below
SELECT
    customer_id, customer_name
FROM
    Customers
WHERE
    customer_id NOT IN (
        SELECT customer_id
        FROM Orders