• 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')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    Employees 表：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    EmployeeUNI 表：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    写一段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 。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    题解一

    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
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    select unique_id,name from Employees e left join EmployeeUNI en on e.id = en.id; 

    • 1

    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')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    Product 表：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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 列存储销售期内该产品的日平均销售额。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    编写一段 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。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    题解一

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解二

    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 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    题解三

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    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')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    Customers 表：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    Orders 表：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    请你设计 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 。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    题解一

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解二

    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].*'
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    题解三

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    题解四

    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' )
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    题解五

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    题解六

    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，因为一个顾客可能买过多次同一产品
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    题解七

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解八

    # Write your MySQL query statement below
SELECT
    customer_id, customer_name
FROM
    Customers
WHERE
    customer_id NOT IN (
        SELECT customer_id
        FROM Orders
        WHERE product_name = 'C'
    ) AND 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'
    )
ORDER BY customer_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    题解九

    select a.customer_id, b.customer_name 
from
(
    select customer_id,
    max(case when product_name='A' then 1 else 0 end) as A,
    max(case when product_name='B' then 1 else 0 end) as B,
    max(case when product_name='C' then 1 else 0 end) as C
    from Orders
    group by customer_id
) a
left join Customers b
on a.customer_id = b.customer_id
where a.A=1 and a.B=1 and a.C=0
order by a.customer_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    1412. 查找成绩处于中游的学生

    Create table If Not Exists Student (student_id int, student_name varchar(30))
Create table If Not Exists Exam (exam_id int, student_id int, score int)
Truncate table Student
insert into Student (student_id, student_name) values ('1', 'Daniel')
insert into Student (student_id, student_name) values ('2', 'Jade')
insert into Student (student_id, student_name) values ('3', 'Stella')
insert into Student (student_id, student_name) values ('4', 'Jonathan')
insert into Student (student_id, student_name) values ('5', 'Will')
Truncate table Exam
insert into Exam (exam_id, student_id, score) values ('10', '1', '70')
insert into Exam (exam_id, student_id, score) values ('10', '2', '80')
insert into Exam (exam_id, student_id, score) values ('10', '3', '90')
insert into Exam (exam_id, student_id, score) values ('20', '1', '80')
insert into Exam (exam_id, student_id, score) values ('30', '1', '70')
insert into Exam (exam_id, student_id, score) values ('30', '3', '80')
insert into Exam (exam_id, student_id, score) values ('30', '4', '90')
insert into Exam (exam_id, student_id, score) values ('40', '1', '60')
insert into Exam (exam_id, student_id, score) values ('40', '2', '70')
insert into Exam (exam_id, student_id, score) values ('40', '4', '80')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    表: Student

    +---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是该表主键.
student_name 学生名字.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    表: Exam

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是该表主键.
学生 student_id 在测验 exam_id 中得分为 score.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

    写一个 SQL 语句，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。

    不要返回从来没有参加过测验的学生。返回结果表按照 student_id 排序。

    查询结果格式如下。

    Student 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+

Exam 表：
+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+

Result 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+

对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    题解一

    select
    s.student_id, s.student_name
from (
select
    *,
    min(score) over(partition by exam_id) AS min_score,
    max(score) over(partition by exam_id) AS max_score
from Exam ) t
JOIN Student AS s
ON s.student_id = t.student_id
group by s.student_id, s.student_name
having sum(if (t.score = min_score OR t.score = max_score, 1, 0)) = 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    题解二

    select t1.student_id,s.student_name
from
(
select *,
if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) d_rank,
if(dense_rank() over(partition by exam_id order by score )=1,1,0) a_rank
from Exam
) t1 left join Student s on t1.student_id =s.student_id
group by t1.student_id
having sum(d_rank)=0 and sum(a_rank)=0
order by student_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    题解三

    select b.student_id,b.student_name
from student b
where b.student_id in 
 (
     select student_id
     from (
        select *,
        max(score) over(partition by exam_id) max_score,
        min(score) over(partition by exam_id) min_score
        from exam
     ) a
     GROUP by student_id
     HAVING sum(if(a.score>min_score and a.score < max_score,1,0)) = count(DISTINCT exam_id)
) 
order by b.student_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    1421. 净现值查询

    Create Table If Not Exists NPV (id int, year int, npv int)
Create Table If Not Exists Queries (id int, year int)
Truncate table NPV
insert into NPV (id, year, npv) values ('1', '2018', '100')
insert into NPV (id, year, npv) values ('7', '2020', '30')
insert into NPV (id, year, npv) values ('13', '2019', '40')
insert into NPV (id, year, npv) values ('1', '2019', '113')
insert into NPV (id, year, npv) values ('2', '2008', '121')
insert into NPV (id, year, npv) values ('3', '2009', '21')
insert into NPV (id, year, npv) values ('11', '2020', '99')
insert into NPV (id, year, npv) values ('7', '2019', '0')
Truncate table Queries
insert into Queries (id, year) values ('1', '2019')
insert into Queries (id, year) values ('2', '2008')
insert into Queries (id, year) values ('3', '2009')
insert into Queries (id, year) values ('7', '2018')
insert into Queries (id, year) values ('7', '2019')
insert into Queries (id, year) values ('7', '2020')
insert into Queries (id, year) values ('13', '2019')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    表: NPV

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
| npv           | int     |
+---------------+---------+
(id, year) 是该表主键.
该表有每一笔存货的年份, id 和对应净现值的信息.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    表: Queries

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
+---------------+---------+
(id, year) 是该表主键.
该表有每一次查询所对应存货的 id 和年份的信息.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    写一个 SQL, 找到 Queries 表中每一次查询的净现值.

    结果表没有顺序要求.

    查询结果的格式如下所示:

    NPV 表:
+------+--------+--------+
| id   | year   | npv    |
+------+--------+--------+
| 1    | 2018   | 100    |
| 7    | 2020   | 30     |
| 13   | 2019   | 40     |
| 1    | 2019   | 113    |
| 2    | 2008   | 121    |
| 3    | 2009   | 12     |
| 11   | 2020   | 99     |
| 7    | 2019   | 0      |
+------+--------+--------+

Queries 表:
+------+--------+
| id   | year   |
+------+--------+
| 1    | 2019   |
| 2    | 2008   |
| 3    | 2009   |
| 7    | 2018   |
| 7    | 2019   |
| 7    | 2020   |
| 13   | 2019   |
+------+--------+

结果表:
+------+--------+--------+
| id   | year   | npv    |
+------+--------+--------+
| 1    | 2019   | 113    |
| 2    | 2008   | 121    |
| 3    | 2009   | 12     |
| 7    | 2018   | 0      |
| 7    | 2019   | 0      |
| 7    | 2020   | 30     |
| 13   | 2019   | 40     |
+------+--------+--------+

(7, 2018)的净现值不在 NPV 表中, 我们把它看作是 0.
所有其它查询的净现值都能在 NPV 表中找到.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42

    题解一

    select q.id,q.year,if(isNUll(n.npv), 0, n.npv) as npv
 from Queries as q
 left join NPV as n
 on q.year = n.year and q.id = n.id
 order by q.id

    • 1
    • 2
    • 3
    • 4
    • 5

    题解二

    select
    q.id,
    q.year,
    ifnull(npv,0) npv
from
    Queries q
    left join
    NPV n using(id,year);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1435. 制作会话柱状图

    Create table If Not Exists Sessions (session_id int, duration int)
Truncate table Sessions
insert into Sessions (session_id, duration) values ('1', '30')
insert into Sessions (session_id, duration) values ('2', '199')
insert into Sessions (session_id, duration) values ('3', '299')
insert into Sessions (session_id, duration) values ('4', '580')
insert into Sessions (session_id, duration) values ('5', '1000')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    表：Sessions

    +---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 “[0-5>”, “[5-10>”, “[10-15>” 和 “15 or more” （单位：分钟）的会话数量，并以此绘制柱状图。

    写一个SQL查询来报告（访问时长区间，会话总数）。结果可用任何顺序呈现。

    下方为查询的输出格式：

    Sessions 表：
+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+

Result 表：
+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+

对于 session_id 1，2 和 3 ，它们的访问时间大于等于 0 分钟且小于 5 分钟。
对于 session_id 4，它的访问时间大于等于 5 分钟且小于 10 分钟。
没有会话的访问时间大于等于 10 分钟且小于 15 分钟。
对于 session_id 5, 它的访问时间大于等于 15 分钟。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    题解一

    select '[0-5>' as bin, count(*) as total from Sessions where duration/60>=0 and duration/60<5
union
select '[5-10>' as bin, count(*) as total from Sessions where duration/60>=5 and duration/60<10
union
select '[10-15>' as bin, count(*) as total from Sessions where duration/60>=10 and duration/60<15
union
select '15 or more'as bin, count(*) as total from Sessions where duration/60>=15

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    题解二

    select a.bin, count(b.bin) as total
from
(
    select '[0-5>' as bin union select '[5-10>' as bin union select '[10-15>' as bin union select '15 or more' as bin 
)a
left join 
(
    select case
        when duration < 300 then '[0-5>'
        when duration >= 300 and duration < 600 then '[5-10>'
        when duration >= 600 and duration < 900 then '[10-15>'
        else '15 or more'
        end bin
    from Sessions 
)b
on a.bin = b.bin
group by a.bin

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    题解三

    select
s1.bin as bin,
ifnull(s2.total, 0) as total
from
(
    select '[0-5>' as bin union
    select '[5-10>' as bin union
    select '[10-15>' as bin union
    select '15 or more' as bin
) as s1
left join
(
    select
    case
    when duration/60<5
    then '[0-5>'
    when duration/60<10
    then '[5-10>'
    when duration/60<15
    then '[10-15>'
    else '15 or more'
    end as bin,
    count(1) as total
    from `Sessions`
    group by bin
) as s2
on s1.bin = s2.bin

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    1440. 计算布尔表达式的值

    Create Table If Not Exists Variables (name varchar(3), value int)
Create Table If Not Exists Expressions (left_operand varchar(3), operator ENUM('>', '<', '='), right_operand varchar(3))
Truncate table Variables
insert into Variables (name, value) values ('x', '66')
insert into Variables (name, value) values ('y', '77')
Truncate table Expressions
insert into Expressions (left_operand, operator, right_operand) values ('x', '>', 'y')
insert into Expressions (left_operand, operator, right_operand) values ('x', '<', 'y')
insert into Expressions (left_operand, operator, right_operand) values ('x', '=', 'y')
insert into Expressions (left_operand, operator, right_operand) values ('y', '>', 'x')
insert into Expressions (left_operand, operator, right_operand) values ('y', '<', 'x')
insert into Expressions (left_operand, operator, right_operand) values ('x', '=', 'x')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    表 Variables:

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
name 是该表主键.
该表包含了存储的变量及其对应的值.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    表 Expressions:

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保证存在于 Variables 表单中.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式.

    返回的结果表没有顺序要求.

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

    Variables 表:
+------+-------+
| name | value |
+------+-------+
| x    | 66    |
| y    | 77    |
+------+-------+

Expressions 表:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x            | >        | y             |
| x            | <        | y             |
| x            | =        | y             |
| y            | >        | x             |
| y            | <        | x             |
| x            | =        | x             |
+--------------+----------+---------------+

Result 表:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+
如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    题解一

    with temp as 
(
    select a.*, b.value as v1, c.value as v2 from Expressions a
    left join 
    Variables b
    on a.left_operand = b.name
    left join 
    Variables c
    on a.right_operand = c.name
)

select left_operand, operator, right_operand,
case when (operator = '>' and v1 > v2)
        or (operator = '<' and v1 < v2) 
        or (operator = '=' and v1 = v2) then "true"
     else "false" end as "value"
from temp

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    题解二

    select e.left_operand,e.operator,e.right_operand,
case e.operator
    when '>' then if(v1.value>v2.value,'true','false')
    when '<' then if(v1.value<v2.value,'true','false')
    else  if(v1.value=v2.value,'true','false')
end value
    
from Expressions e
left join Variables v1 on v1.name = e.left_operand 
left join Variables v2 on v2.name = e.right_operand

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    题解三

    SELECT a.left_operand, operator, a.right_operand, 
CASE
    WHEN b.value > c.value AND operator = '>' THEN 'true'
    WHEN b.value < c.value AND operator = '<' THEN 'true'
    WHEN b.value = c.value AND operator = '=' THEN 'true'
    ELSE 'false'
END AS value
FROM Expressions a
INNER JOIN Variables b
INNER JOIN Variables c
ON a.left_operand = b.name AND a.right_operand = c.name

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    1445. 苹果和桔子

    Create table If Not Exists Sales (sale_date date, fruit ENUM('apples', 'oranges'), sold_num int)
Truncate table Sales
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-01', 'apples', '10')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-01', 'oranges', '8')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-02', 'apples', '15')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-02', 'oranges', '15')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-03', 'apples', '20')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-03', 'oranges', '0')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-04', 'apples', '15')
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-04', 'oranges', '16')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    表: Sales

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_date     | date    |
| fruit         | enum    | 
| sold_num      | int     | 
+---------------+---------+
(sale_date,fruit) 是该表主键.
该表包含了每一天中"苹果" 和 "桔子"的销售情况.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    写一个 SQL 查询, 报告每一天 苹果 和 桔子 销售的数目的差异.

    返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.

    查询结果表如下例所示:

    Sales 表:
+------------+------------+-------------+
| sale_date  | fruit      | sold_num    |
+------------+------------+-------------+
| 2020-05-01 | apples     | 10          |
| 2020-05-01 | oranges    | 8           |
| 2020-05-02 | apples     | 15          |
| 2020-05-02 | oranges    | 15          |
| 2020-05-03 | apples     | 20          |
| 2020-05-03 | oranges    | 0           |
| 2020-05-04 | apples     | 15          |
| 2020-05-04 | oranges    | 16          |
+------------+------------+-------------+

Result 表:
+------------+--------------+
| sale_date  | diff         |
+------------+--------------+
| 2020-05-01 | 2            |
| 2020-05-02 | 0            |
| 2020-05-03 | 20           |
| 2020-05-04 | -1           |
+------------+--------------+

在 2020-05-01, 卖了 10 个苹果 和 8 个桔子 (差异为 10 - 8 = 2).
在 2020-05-02, 卖了 15 个苹果 和 15 个桔子 (差异为 15 - 15 = 0).
在 2020-05-03, 卖了 20 个苹果 和 0 个桔子 (差异为 20 - 0 = 20).
在 2020-05-04, 卖了 15 个苹果 和 16 个桔子 (差异为 15 - 16 = -1).

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    题解一

    SELECT sale_date,
    SUM(CASE WHEN fruit='apples' THEN sold_num ELSE -sold_num END) AS diff
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

    • 1
    • 2
    • 3
    • 4
    • 5

    题解二

    select 
    sale_date,
    sum(case when fruit = 'apples' then sold_num else sold_num*(-1) end) as diff 
from sales 
group by 
    sale_date

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    题解三

    select s1.sale_date,(s1.sold_num-s2.sold_num ) diff from sales s1
join sales s2 on s1.sale_date=s2.sale_date and s1.fruit!=s2.fruit
where s1.fruit='apples'

    • 1
    • 2
    • 3

    1454. 活跃用户

    Create table If Not Exists Accounts (id int, name varchar(10))
Create table If Not Exists Logins (id int, login_date date)
Truncate table Accounts
insert into Accounts (id, name) values ('1', 'Winston')
insert into Accounts (id, name) values ('7', 'Jonathan')
Truncate table Logins
insert into Logins (id, login_date) values ('7', '2020-05-30')
insert into Logins (id, login_date) values ('1', '2020-05-30')
insert into Logins (id, login_date) values ('7', '2020-05-31')
insert into Logins (id, login_date) values ('7', '2020-06-01')
insert into Logins (id, login_date) values ('7', '2020-06-02')
insert into Logins (id, login_date) values ('7', '2020-06-02')
insert into Logins (id, login_date) values ('7', '2020-06-03')
insert into Logins (id, login_date) values ('1', '2020-06-07')
insert into Logins (id, login_date) values ('7', '2020-06-10')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    表 Accounts:

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    表 Logins:

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    写一个 SQL 查询, 找到活跃用户的 id 和 name.

    活跃用户是指那些至少连续 5 天登录账户的用户.

    返回的结果表按照 id 排序.

    结果表格式如下例所示:

    Accounts 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+

Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7  | 2020-05-30 |
| 1  | 2020-05-30 |
| 7  | 2020-05-31 |
| 7  | 2020-06-01 |
| 7  | 2020-06-02 |
| 7  | 2020-06-02 |
| 7  | 2020-06-03 |
| 1  | 2020-06-07 |
| 7  | 2020-06-10 |
+----+------------+

Result 表:
+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    题解一

    with logins as (select distinct id, login_date from logins order by id, login_date),
db as (
select 
    id,
    login_date,
    if(id = @lid, if(date_sub(login_date,interval 1 day) = @ldate, @cnt:=@cnt, @cnt:=@cnt+1), @cnt := 0) as cnt,
    @ldate := login_date,
    @lid := id
from logins, (select @ldate:='9999-12-12', @lid:=-999, @cnt:=0) as a)

select distinct ac.id, ac.name 
from db left join accounts ac on ac.id = db.id
group by db.id, cnt
having count(*)>=5

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    题解二

    select distinct t.id,a.name
from (
select
    id, login_date,
    datediff(lead(login_date, 4) over(partition by id order by login_date),login_date) as tag
from logins
group by id, login_date) as t
left join accounts a using(id)
where t.tag = 4

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解三（自连接）

    select distinct a.id,a.name
from (
        select a.id, a.login_date as ad, b.login_date as bd
        from logins a
        join logins b
        on a.id = b.id and datediff(a.login_date,b.login_date) between 0 and 4
        group by a.id, a.login_date
        having count(distinct b.login_date) = 5) as t 
left join accounts a on a.id = t.id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解四

    select distinct t.id, c.name
from(
    select id
    from (
        select id,login_date,
            date_sub(login_date, interval dense_rank() over(partition by id order by login_date asc) day) as tag
        from logins) a
    group by id, tag
    having count(distinct login_date)>=5) as t
left join accounts c on t.id = c.id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    1459. 矩形面积

    Create table If Not Exists Points (id int, x_value int, y_value int)
Truncate table Points
insert into Points (id, x_value, y_value) values ('1', '2', '7')
insert into Points (id, x_value, y_value) values ('2', '4', '8')
insert into Points (id, x_value, y_value) values ('3', '2', '10')

    • 1
    • 2
    • 3
    • 4
    • 5

    表: Points

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| x_value       | int     |
| y_value       | int     |
+---------------+---------+
id 是该表主键
每个点都用二维坐标 (x_value, y_value) 表示

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    写一个 SQL 语句，报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。

    结果表中的每一行包含三列 (p1, p2, area) 如下:

    p1 和 p2 是矩形两个对角的 id
矩形的面积由列 area 表示

    • 1
    • 2

    请按照面积 area 大小降序排列；如果面积相同的话, 则按照 p1 升序排序；若仍相同，则按 p2 升序排列。

    查询结果如下例所示：

    Points 表:
+----------+-------------+-------------+
| id       | x_value     | y_value     |
+----------+-------------+-------------+
| 1        | 2           | 7           |
| 2        | 4           | 8           |
| 3        | 2           | 10          |
+----------+-------------+-------------+

Result 表:
+----------+-------------+-------------+
| p1       | p2          | area        |
+----------+-------------+-------------+
| 2        | 3           | 4           |
| 1        | 2           | 2           |
+----------+-------------+-------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    在这里插入图片描述

    p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |8-10| = 4
p1 = 1 且 p2 = 2 时, 面积等于 ||2-4| * |7-8| = 2 
p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 面积等于 0

    • 1
    • 2
    • 3

    题解一

    SELECT
    t1.id AS 'p1',
    t2.id AS 'p2',
    ABS(t2.x_value - t1.x_value) * ABS(t2.y_value - t1.y_value) AS 'area'
FROM
    Points AS t1
INNER JOIN Points AS t2 ON t1.id < t2.id
WHERE (t2.x_value - t1.x_value) != 0
AND (t2.y_value - t1.y_value) != 0
ORDER BY area DESC, t1.id, t2.id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    题解二

    SELECT p1.id AS p1,
        p2.id AS p2,
        ABS(p1.x_value-p2.x_value)*ABS(p1.y_value-p2.y_value) AS area
FROM Points p1,Points p2
WHERE p1.id<p2.id
        AND ABS(p1.x_value-p2.x_value)>0 
        AND ABS(p1.y_value-p2.y_value)>0
ORDER BY area DESC,p1,p2

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    题解三

    select tmp.id1 P1,tmp.id2 P2,abs(tmp.xv2-tmp.xv1)*abs(tmp.yv2-tmp.yv1) AREA
from
    (select p1.id id1, p1.x_value xv1,p1.y_value yv1, p2.id id2,p2.x_value xv2,p2.y_value yv2
    from Points p1
    cross join Points p2
    where p1.x_value<>p2.x_value and p1.y_value<>p2.y_value
    ) tmp
where tmp.id1<tmp.id2
order by area desc,p1 asc,p2 asc

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解四

    SELECT
    p1.id P1,
    p2.id P2,
    (ABS(p1.x_value - p2.x_value) * ABS(p1.y_value - p2.y_value)) AS AREA
FROM
    Points p1
JOIN
    Points p2
ON
    p1.id < p2.id
HAVING
    AREA <> 0
ORDER BY
    AREA DESC,
    P1,
    P2;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    1468. 计算税后工资

    Create table If Not Exists Salaries (company_id int, employee_id int, employee_name varchar(13), salary int)
Truncate table Salaries
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '1', 'Tony', '2000')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '2', 'Pronub', '21300')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '3', 'Tyrrox', '10800')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '1', 'Pam', '300')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '7', 'Bassem', '450')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '9', 'Hermione', '700')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '7', 'Bocaben', '100')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '2', 'Ognjen', '2200')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '13', 'Nyancat', '3300')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '15', 'Morninngcat', '7777')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    Salaries 表：

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| company_id    | int     |
| employee_id   | int     |
| employee_name | varchar |
| salary        | int     |
+---------------+---------+
(company_id, employee_id) 是这个表的主键
这个表包括员工的company id, id, name 和 salary 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    写一条查询 SQL 来查找每个员工的税后工资

    每个公司的税率计算依照以下规则

    如果这个公司员工最高工资不到 1000 ，税率为 0%
如果这个公司员工最高工资在 1000 到 10000 之间，税率为 24%
如果这个公司员工最高工资大于 10000 ，税率为 49%

    • 1
    • 2
    • 3

    按任意顺序返回结果，税后工资结果取整

    结果表格式如下例所示：

    Salaries 表：
+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1          | 1           | Tony          | 2000   |
| 1          | 2           | Pronub        | 21300  |
| 1          | 3           | Tyrrox        | 10800  |
| 2          | 1           | Pam           | 300    |
| 2          | 7           | Bassem        | 450    |
| 2          | 9           | Hermione      | 700    |
| 3          | 7           | Bocaben       | 100    |
| 3          | 2           | Ognjen        | 2200   |
| 3          | 13          | Nyancat       | 3300   |
| 3          | 15          | Morninngcat   | 7777   |
+------------+-------------+---------------+--------+

Result 表：
+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1          | 1           | Tony          | 1020   |
| 1          | 2           | Pronub        | 10863  |
| 1          | 3           | Tyrrox        | 5508   |
| 2          | 1           | Pam           | 300    |
| 2          | 7           | Bassem        | 450    |
| 2          | 9           | Hermione      | 700    |
| 3          | 7           | Bocaben       | 76     |
| 3          | 2           | Ognjen        | 1672   |
| 3          | 13          | Nyancat       | 2508   |
| 3          | 15          | Morninngcat   | 5911   |
+------------+-------------+---------------+--------+
对于公司 1 ，最高工资是 21300 ，其每个员工的税率为 49%
对于公司 2 ，最高工资是 700 ，其每个员工税率为 0%
对于公司 3 ，最高工资是 7777 ，其每个员工税率是 24%
税后工资计算 = 工资 - ( 税率 / 100）*工资
对于上述案例，Morninngcat 的税后工资 = 7777 - 7777 * ( 24 / 100) = 7777 - 1866.48 = 5910.52 ，取整为 5911

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    题解一

    select 
    s.company_id, 
    s.employee_id, 
    s.employee_name, 
    round(s.salary*(1-companytaxrate.taxrate)) as salary
from salaries s join
(select 
    company_id, 
    case 
        when max(salary)<1000 then 0
        when max(salary) between 1000 and 10000 then 0.24
        when max(salary)>10000 then 0.49
    end as taxrate
from salaries
group by company_id) as companytaxrate on s.company_id=companytaxrate.company_id;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    题解二

    select
company_id,
employee_id,
employee_name,
round(
case when max(salary)over(partition by company_id)>10000 then salary*0.51
     when max(salary)over(partition by company_id) between 1000 and 10000 then salary*0.76
     else salary end,0) as salary
from  Salaries


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    题解三

    select s.company_id,s.employee_id,s.employee_name,round(s.salary*(1 - t.rate),0) as salary from Salaries s
left join 
(select company_id as company_id,
case when max(salary) < 1000 then 0 
when  max(salary) between 1000 and 10000 then 0.24
when max(salary) >= 10000 then 0.49 end as rate
from Salaries group by company_id) as t
on s.company_id = t.company_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1479. 周内每天的销售情况

    Create table If Not Exists Orders (order_id int, customer_id int, order_date date, item_id varchar(30), quantity int)
Create table If Not Exists Items (item_id varchar(30), item_name varchar(30), item_category varchar(30))
Truncate table Orders
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('1', '1', '2020-06-01', '1', '10')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('2', '1', '2020-06-08', '2', '10')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('3', '2', '2020-06-02', '1', '5')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('4', '3', '2020-06-03', '3', '5')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('5', '4', '2020-06-04', '4', '1')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('6', '4', '2020-06-05', '5', '5')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('7', '5', '2020-06-05', '1', '10')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('8', '5', '2020-06-14', '4', '5')
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('9', '5', '2020-06-21', '3', '5')
Truncate table Items
insert into Items (item_id, item_name, item_category) values ('1', 'LC Alg. Book', 'Book')
insert into Items (item_id, item_name, item_category) values ('2', 'LC DB. Book', 'Book')
insert into Items (item_id, item_name, item_category) values ('3', 'LC SmarthPhone', 'Phone')
insert into Items (item_id, item_name, item_category) values ('4', 'LC Phone 2020', 'Phone')
insert into Items (item_id, item_name, item_category) values ('5', 'LC SmartGlass', 'Glasses')
insert into Items (item_id, item_name, item_category) values ('6', 'LC T-Shirt XL', 'T-shirt')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    表：Orders

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    | 
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(order_id, item_id) 是该表主键
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    表：Items

    +---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id 是该表主键
item_name 是商品的名字
item_category 是商品的类别

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    你是企业主，想要获得分类商品和周内每天的销售报告。

    写一个SQL语句，报告 周内每天 每个商品类别下订购了多少单位。

    返回结果表单 按商品类别排序 。

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

    示例 1：

    输入：
Orders 表：
+------------+--------------+-------------+--------------+-------------+
| order_id   | customer_id  | order_date  | item_id      | quantity    |
+------------+--------------+-------------+--------------+-------------+
| 1          | 1            | 2020-06-01  | 1            | 10          |
| 2          | 1            | 2020-06-08  | 2            | 10          |
| 3          | 2            | 2020-06-02  | 1            | 5           |
| 4          | 3            | 2020-06-03  | 3            | 5           |
| 5          | 4            | 2020-06-04  | 4            | 1           |
| 6          | 4            | 2020-06-05  | 5            | 5           |
| 7          | 5            | 2020-06-05  | 1            | 10          |
| 8          | 5            | 2020-06-14  | 4            | 5           |
| 9          | 5            | 2020-06-21  | 3            | 5           |
+------------+--------------+-------------+--------------+-------------+

Items 表：
+------------+----------------+---------------+
| item_id    | item_name      | item_category |
+------------+----------------+---------------+
| 1          | LC Alg. Book   | Book          |
| 2          | LC DB. Book    | Book          |
| 3          | LC SmarthPhone | Phone         |
| 4          | LC Phone 2020  | Phone         |
| 5          | LC SmartGlass  | Glasses       |
| 6          | LC T-Shirt XL  | T-Shirt       |
+------------+----------------+---------------+
输出：
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
解释：
在周一(2020-06-01, 2020-06-08)，Book分类(ids: 1, 2)下，总共销售了20个单位(10 + 10)
在周二(2020-06-02)，Book分类(ids: 1, 2)下，总共销售了5个单位
在周三(2020-06-03)，Phone分类(ids: 3, 4)下，总共销售了5个单位
在周四(2020-06-04)，Phone分类(ids: 3, 4)下，总共销售了1个单位
在周五(2020-06-05)，Book分类(ids: 1, 2)下，总共销售了10个单位，Glasses分类(ids: 5)下，总共销售了5个单位
在周六, 没有商品销售
在周天(2020-06-14, 2020-06-21)，Phone分类(ids: 3, 4)下，总共销售了10个单位(5 + 5)
没有销售 T-Shirt 类别的商品

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45

    题解一

    select distinct b.item_category as Category,
ifnull(sum(case when dayofweek(a.order_date) = 2 then a.quantity end),0) Monday,
ifnull(sum(case when dayofweek(a.order_date) = 3 then a.quantity end),0) Tuesday,
ifnull(sum(case when dayofweek(a.order_date) = 4 then a.quantity end),0) Wednesday,
ifnull(sum(case when dayofweek(a.order_date) = 5 then a.quantity end),0) Thursday,
ifnull(sum(case when dayofweek(a.order_date) = 6 then a.quantity end),0) Friday,
ifnull(sum(case when dayofweek(a.order_date) = 7 then a.quantity end),0) Saturday,
ifnull(sum(case when dayofweek(a.order_date) = 1 then a.quantity end),0) Sunday
from Orders a right join Items b
on a.item_id = b.item_id
group by Category
order by Category

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    1495. 上月播放的儿童适宜电影

    Create table If Not Exists TVProgram (program_date date, content_id int, channel varchar(30))
Create table If Not Exists Content (content_id varchar(30), title varchar(30), Kids_content ENUM('Y', 'N'), content_type varchar(30))
Truncate table TVProgram
insert into TVProgram (program_date, content_id, channel) values ('2020-06-10 08:00', '1', 'LC-Channel')
insert into TVProgram (program_date, content_id, channel) values ('2020-05-11 12:00', '2', 'LC-Channel')
insert into TVProgram (program_date, content_id, channel) values ('2020-05-12 12:00', '3', 'LC-Channel')
insert into TVProgram (program_date, content_id, channel) values ('2020-05-13 14:00', '4', 'Disney Ch')
insert into TVProgram (program_date, content_id, channel) values ('2020-06-18 14:00', '4', 'Disney Ch')
insert into TVProgram (program_date, content_id, channel) values ('2020-07-15 16:00', '5', 'Disney Ch')
Truncate table Content
insert into Content (content_id, title, Kids_content, content_type) values ('1', 'Leetcode Movie', 'N', 'Movies')
insert into Content (content_id, title, Kids_content, content_type) values ('2', 'Alg. for Kids', 'Y', 'Series')
insert into Content (content_id, title, Kids_content, content_type) values ('3', 'Database Sols', 'N', 'Series')
insert into Content (content_id, title, Kids_content, content_type) values ('4', 'Aladdin', 'Y', 'Movies')
insert into Content (content_id, title, Kids_content, content_type) values ('5', 'Cinderella', 'Y', 'Movies')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    表: TVProgram

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| program_date  | date    |
| content_id    | int     |
| channel       | varchar |
+---------------+---------+
(program_date, content_id) 是该表主键.
该表包含电视上的节目信息.
content_id 是电视一些频道上的节目的 id.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    表: Content

    ------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| content_id       | varchar |
| title            | varchar |
| Kids_content     | enum    |
| content_type     | varchar |
+------------------+---------+
content_id 是该表主键.
Kids_content 是枚举类型, 取值为('Y', 'N'), 其中: 
'Y' 表示儿童适宜内容, 而'N'表示儿童不宜内容.
content_type 表示内容的类型, 比如电影, 电视剧等.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    写一个 SQL 语句, 报告在 2020 年 6 月份播放的儿童适宜电影的去重电影名.

    返回的结果表单 没有顺序要求 .

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

    示例 1:

    输入：
TVProgram 表:
+--------------------+--------------+-------------+
| program_date       | content_id   | channel     |
+--------------------+--------------+-------------+
| 2020-06-10 08:00   | 1            | LC-Channel  |
| 2020-05-11 12:00   | 2            | LC-Channel  |
| 2020-05-12 12:00   | 3            | LC-Channel  |
| 2020-05-13 14:00   | 4            | Disney Ch   |
| 2020-06-18 14:00   | 4            | Disney Ch   |
| 2020-07-15 16:00   | 5            | Disney Ch   |
+--------------------+--------------+-------------+
Content 表:
+------------+----------------+---------------+---------------+
| content_id | title          | Kids_content  | content_type  |
+------------+----------------+---------------+---------------+
| 1          | Leetcode Movie | N             | Movies        |
| 2          | Alg. for Kids  | Y             | Series        |
| 3          | Database Sols  | N             | Series        |
| 4          | Aladdin        | Y             | Movies        |
| 5          | Cinderella     | Y             | Movies        |
+------------+----------------+---------------+---------------+
输出：
+--------------+
| title        |
+--------------+
| Aladdin      |
+--------------+
解释：
"Leetcode Movie" 是儿童不宜的电影.
"Alg. for Kids" 不是电影.
"Database Sols" 不是电影
"Alladin" 是电影, 儿童适宜, 并且在 2020 年 6 月份播放.
"Cinderella" 不在 2020 年 6 月份播放.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    题解一

    SELECT DISTINCT c1.title AS 'title'
FROM Content AS c1
    INNER JOIN TVProgram AS t1
    ON t1.content_id = c1.content_id
WHERE t1.program_date BETWEEN '2020-06-01' AND '2020-06-30'
    AND c1.Kids_content = 'Y'
    AND c1.content_type = 'Movies'
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1501. 可以放心投资的国家

    Create table If Not Exists Person (id int, name varchar(15), phone_number varchar(11))
Create table If Not Exists Country (name varchar(15), country_code varchar(3))
Create table If Not Exists Calls (caller_id int, callee_id int, duration int)
Truncate table Person
insert into Person (id, name, phone_number) values ('3', 'Jonathan', '051-1234567')
insert into Person (id, name, phone_number) values ('12', 'Elvis', '051-7654321')
insert into Person (id, name, phone_number) values ('1', 'Moncef', '212-1234567')
insert into Person (id, name, phone_number) values ('2', 'Maroua', '212-6523651')
insert into Person (id, name, phone_number) values ('7', 'Meir', '972-1234567')
insert into Person (id, name, phone_number) values ('9', 'Rachel', '972-0011100')
Truncate table Country
insert into Country (name, country_code) values ('Peru', '051')
insert into Country (name, country_code) values ('Israel', '972')
insert into Country (name, country_code) values ('Morocco', '212')
insert into Country (name, country_code) values ('Germany', '049')
insert into Country (name, country_code) values ('Ethiopia', '251')
Truncate table Calls
insert into Calls (caller_id, callee_id, duration) values ('1', '9', '33')
insert into Calls (caller_id, callee_id, duration) values ('2', '9', '4')
insert into Calls (caller_id, callee_id, duration) values ('1', '2', '59')
insert into Calls (caller_id, callee_id, duration) values ('3', '12', '102')
insert into Calls (caller_id, callee_id, duration) values ('3', '12', '330')
insert into Calls (caller_id, callee_id, duration) values ('12', '3', '5')
insert into Calls (caller_id, callee_id, duration) values ('7', '9', '13')
insert into Calls (caller_id, callee_id, duration) values ('7', '1', '3')
insert into Calls (caller_id, callee_id, duration) values ('9', '7', '1')
insert into Calls (caller_id, callee_id, duration) values ('1', '7', '7')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    表 Person:

    +----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| name           | varchar |
| phone_number   | varchar |
+----------------+---------+
id 是该表主键.
该表每一行包含一个人的名字和电话号码.
电话号码的格式是:'xxx-yyyyyyy', 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    表 Country:

    +----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| name           | varchar |
| country_code   | varchar |
+----------------+---------+
country_code是该表主键.
该表每一行包含国家名和国家码. country_code的格式是'xxx', x是数字.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    表 Calls:

    +-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id   | int  |
| callee_id   | int  |
| duration    | int  |
+-------------+------+
该表无主键, 可能包含重复行.
每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.

    写一段 SQL, 找到所有该公司可以投资的国家.

    返回的结果表没有顺序要求.

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

    Person 表:
+----+----------+--------------+
| id | name     | phone_number |
+----+----------+--------------+
| 3  | Jonathan | 051-1234567  |
| 12 | Elvis    | 051-7654321  |
| 1  | Moncef   | 212-1234567  |
| 2  | Maroua   | 212-6523651  |
| 7  | Meir     | 972-1234567  |
| 9  | Rachel   | 972-0011100  |
+----+----------+--------------+

Country 表:
+----------+--------------+
| name     | country_code |
+----------+--------------+
| Peru     | 051          |
| Israel   | 972          |
| Morocco  | 212          |
| Germany  | 049          |
| Ethiopia | 251          |
+----------+--------------+

Calls 表:
+-----------+-----------+----------+
| caller_id | callee_id | duration |
+-----------+-----------+----------+
| 1         | 9         | 33       |
| 2         | 9         | 4        |
| 1         | 2         | 59       |
| 3         | 12        | 102      |
| 3         | 12        | 330      |
| 12        | 3         | 5        |
| 7         | 9         | 13       |
| 7         | 1         | 3        |
| 9         | 7         | 1        |
| 1         | 7         | 7        |
+-----------+-----------+----------+

Result 表:
+----------+
| country  |
+----------+
| Peru     |
+----------+
国家Peru的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
国家Israel的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
国家Morocco的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000 
全球平均通话时长 = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50

    题解一

    # Write your MySQL query statement below
with
# 国家维度的通话时长
call_log as (
    -- caller所属国家的通话时长
    select a.caller_id as id,
        a.duration,
        left(b.phone_number,3) as country_code
    from calls a 
    left join person b on a.caller_id=b.id
    union all
    -- callee所属国家的通话时长
    select a.callee_id as id,
        a.duration,
        left(b.phone_number,3) as country_code
    from calls a 
    left join person b on a.callee_id=b.id
),
# 每个国家的平均通话时长
duration_avg_country as (
select country_code,
    sum(duration)/count(id) as duration_c
from call_log
group by country_code
)
# 选出平均通话时长大于全球平均通话时长的国家
select b.name as country
from duration_avg_country as a 
left join country as b on a.country_code=b.country_code
where a.duration_c>(
    select sum(duration)/count(id) as duration_all -- 全球通话时长
    from call_log  
    )

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    题解二

    SELECT c.name AS country
FROM Calls, Person, Country c
WHERE (caller_id = id OR callee_id = id) AND country_code = LEFT(phone_number, 3)
GROUP BY country_code
HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls);

    • 1
    • 2
    • 3
    • 4
    • 5

    题解三

    with a as (
    select caller_id caller, duration from Calls
    union all
    select callee_id caller, duration from Calls
)
select c.name country from a left join Person p on a.caller=p.id
left join Country c on left(p.phone_number, 3)=c.country_code
group by c.name
having avg(a.duration) > (select avg(duration) from a)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    题解四

    with temp1 as 
(
    select * from Calls
    UNION ALL
    select callee_id as caller_id, caller_id as callee_id, duration from Calls
)

, temp2 as 
(
    select a.*, b.name as country from Person a
    left join Country b
    on substring(a.phone_number, 1, 3) = b.country_code

)

select distinct b.country from temp1 a
left join temp2 b
on a.caller_id = b.id
group by b.country
having avg(a.duration) > (select avg(duration) from temp1)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    1511. 消费者下单频率

    Create table If Not Exists Customers (customer_id int, name varchar(30), country varchar(30))Create table If Not Exists Product (product_id int, description varchar(30), price int)
Create table If Not Exists Orders (order_id int, customer_id int, product_id int, order_date date, quantity int)
Truncate table Customersinsert into Customers (customer_id, name, country) values ('1', 'Winston', 'USA')insert into Customers (customer_id, name, country) values ('2', 'Jonathan', 'Peru')insert into Customers (customer_id, name, country) values ('3', 'Moustafa', 'Egypt')Truncate table Productinsert into Product (product_id, description, price) values ('10', 'LC Phone', '300')insert into Product (product_id, description, price) values ('20', 'LC T-Shirt', '10')insert into Product (product_id, description, price) values ('30', 'LC Book', '45')insert into Product (product_id, description, price) values ('40', 'LC Keychain', '2')Truncate table Ordersinsert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('1', '1', '10', '2020-06-10', '1')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('2', '1', '20', '2020-07-01', '1')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('3', '1', '30', '2020-07-08', '2')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('4', '2', '10', '2020-06-15', '2')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('5', '2', '40', '2020-07-01', '10')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('6', '3', '20', '2020-06-24', '2')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('7', '3', '30', '2020-06-25', '2')insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('9', '3', '30', '2020-05-08', '3')

    • 1
    • 2
    • 3

    表: Customers

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| country       | varchar |
+---------------+---------+
customer_id 是该表主键.
该表包含公司消费者的信息.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    表: Product

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| description   | varchar |
| price         | int     |
+---------------+---------+
product_id 是该表主键.
该表包含公司产品的信息.
price 是本产品的花销.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    表: Orders

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_id    | int     |
| order_date    | date    |
| quantity      | int     |
+---------------+---------+
order_id 是该表主键.
该表包含消费者下单的信息.
customer_id 是买了数量为"quantity", id为"product_id"产品的消费者的 id.
Order_date 是订单发货的日期, 格式为('YYYY-MM-DD').

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    写一个 SQL 查询，报告在 2020 年 6 月和 7 月 每个月至少花费 $100 的客户的 customer_id 和 customer_name 。

    以任意顺序返回结果表.

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

    示例 1:

    输入：
Customers table:
+--------------+-----------+-------------+
| customer_id  | name      | country     |
+--------------+-----------+-------------+
| 1            | Winston   | USA         |
| 2            | Jonathan  | Peru        |
| 3            | Moustafa  | Egypt       |
+--------------+-----------+-------------+

Product table:
+--------------+-------------+-------------+
| product_id   | description | price       |
+--------------+-------------+-------------+
| 10           | LC Phone    | 300         |
| 20           | LC T-Shirt  | 10          |
| 30           | LC Book     | 45          |
| 40           | LC Keychain | 2           |
+--------------+-------------+-------------+

Orders table:
+--------------+-------------+-------------+-------------+-----------+
| order_id     | customer_id | product_id  | order_date  | quantity  |
+--------------+-------------+-------------+-------------+-----------+
| 1            | 1           | 10          | 2020-06-10  | 1         |
| 2            | 1           | 20          | 2020-07-01  | 1         |
| 3            | 1           | 30          | 2020-07-08  | 2         |
| 4            | 2           | 10          | 2020-06-15  | 2         |
| 5            | 2           | 40          | 2020-07-01  | 10        |
| 6            | 3           | 20          | 2020-06-24  | 2         |
| 7            | 3           | 30          | 2020-06-25  | 2         |
| 9            | 3           | 30          | 2020-05-08  | 3         |
+--------------+-------------+-------------+-------------+-----------+

输出：
+--------------+------------+
| customer_id  | name       |  
+--------------+------------+
| 1            | Winston    |
+--------------+------------+ 
解释：
Winston 在2020年6月花费了$300(300 * 1), 在7月花费了$100(10 * 1 + 45 * 2).
Jonathan 在2020年6月花费了$600(300 * 2), 在7月花费了$20(2 * 10).
Moustafa 在2020年6月花费了$110 (10 * 2 + 45 * 2), 在7月花费了$0.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44

    题解一

    select c.customer_id,c.name
from customers c
join orders o on o.customer_id=c.customer_id
join product p on p.product_id=o.product_id
group by c.customer_id, c.name
having sum(case when left(o.order_date,7)='2020-06' then p.price*o.quantity else 0 end)>=100 and
sum(case when left(o.order_date,7)='2020-07' then p.price*o.quantity else 0 end)>=100

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
           | Winston   | USA         |

    • 1

    | 2 | Jonathan | Peru |
    | 3 | Moustafa | Egypt |
    ±-------------±----------±------------+

    Product table:
    ±-------------±------------±------------+
    | product_id | description | price |
    ±-------------±------------±------------+
    | 10 | LC Phone | 300 |
    | 20 | LC T-Shirt | 10 |
    | 30 | LC Book | 45 |
    | 40 | LC Keychain | 2 |
    ±-------------±------------±------------+

    Orders table:
    ±-------------±------------±------------±------------±----------+
    | order_id | customer_id | product_id | order_date | quantity |
    ±-------------±------------±------------±------------±----------+
    | 1 | 1 | 10 | 2020-06-10 | 1 |
    | 2 | 1 | 20 | 2020-07-01 | 1 |
    | 3 | 1 | 30 | 2020-07-08 | 2 |
    | 4 | 2 | 10 | 2020-06-15 | 2 |
    | 5 | 2 | 40 | 2020-07-01 | 10 |
    | 6 | 3 | 20 | 2020-06-24 | 2 |
    | 7 | 3 | 30 | 2020-06-25 | 2 |
    | 9 | 3 | 30 | 2020-05-08 | 3 |
    ±-------------±------------±------------±------------±----------+

    输出：
    ±-------------±-----------+
    | customer_id | name |
    ±-------------±-----------+
    | 1 | Winston |
    ±-------------±-----------+
    解释：
    Winston 在2020年6月花费了$300(300 * 1), 在7月花费了$100(10 * 1 + 45 * 2).
    Jonathan 在2020年6月花费了$600(300 * 2), 在7月花费了$20(2 * 10).
    Moustafa 在2020年6月花费了$110 (10 * 2 + 45 * 2), 在7月花费了$0.

    
题解一

```sql
select c.customer_id,c.name
from customers c
join orders o on o.customer_id=c.customer_id
join product p on p.product_id=o.product_id
group by c.customer_id, c.name
having sum(case when left(o.order_date,7)='2020-06' then p.price*o.quantity else 0 end)>=100 and
sum(case when left(o.order_date,7)='2020-07' then p.price*o.quantity else 0 end)>=100

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
  • 相关阅读:
    【C语言】C的编译过程&&预处理
    SQL入门
    2023青岛大学计算机考研信息汇总
    【黄啊码】PHP如何防止重复提交
    极光笔记 | 推送服务数据中心选择：合规性与传输效率的双重考量
    操作系统：文件IO
    Hadoop_HDFS笔记2（HDFS的API操作，HDFS的读写流程（面试重点））
    函数声明与函数表达式
    机器学习笔记 - CRAFT(文本检测的字符区域感知)论文解读
    金九银十助力面试——手把手轻松读懂HashMap源码
  • 原文地址：https://blog.csdn.net/weixin_45682261/article/details/126076619