超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第七部分79-91）

最新推荐文章于 2022-07-30 19:28:24 发布

龍浮影

最新推荐文章于 2022-07-30 19:28:24 发布

阅读量368

点赞数

文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_48784015/article/details/108432694

版权

79. 活动参与者

写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字，可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表

展示效果：

+--------------+
| activity     |
+--------------+
| Singing      |
+--------------+

Create table If Not Exists 79_Friends (id int, name varchar(30), activity varchar(30));
Create table If Not Exists 79_Activities (id int, name varchar(30));
Truncate table 79_Friends;
insert into 79_Friends (id, name, activity) values ('1', 'Jonathan D.', 'Eating');
insert into 79_Friends (id, name, activity) values ('2', 'Jade W.', 'Singing');
insert into 79_Friends (id, name, activity) values ('3', 'Victor J.', 'Singing');
insert into 79_Friends (id, name, activity) values ('4', 'Elvis Q.', 'Eating');
insert into 79_Friends (id, name, activity) values ('5', 'Daniel A.', 'Eating');
insert into 79_Friends (id, name, activity) values ('6', 'Bob B.', 'Horse Riding');
Truncate table 79_Activities;
insert into 79_Activities (id, name) values ('1', 'Eating');
insert into 79_Activities (id, name) values ('2', 'Singing');
insert into 79_Activities (id, name) values ('3', 'Horse Riding');

提示：

Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)

Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)

Singing 活动有两个人参加 (Victor J. and Jade W.)

最终SQL：

-- 方法一
select 
     activity as ACTIVITY
from 
     79_friends
group by 
     activity
having
     count(*) > any(select count(*) from 79_friends group by activity)
     and
     count(*) < any (select count(*) from 79_friends group by activity);

-- 方法二
select
     activity
from 
     79_Friends
group by 1
having 
     count(distinct id) > some(select count(distinct id) from 79_Friends group by activity)
     and
     count(distinct id) < some(select count(distinct id) from 79_Friends group by activity);


-- 方法三
SELECT 
     tempAct.activity AS 'ACTIVITY'
FROM 
    (SELECT
          activity,
          COUNT(id) AS theTimes
     FROM
          79_Friends 
     GROUP BY
          activity 
     HAVING 
          theTimes NOT IN (
            (SELECT MAX(tMax.countTimes) AS maxCount 
             FROM 
                (SELECT activity, COUNT(id) AS countTimes
                 FROM 79_Friends 
                 GROUP BY activity)AS tMax),
            (SELECT MIN(tMin.countTimes) AS minCount 
             FROM 
                (SELECT activity, COUNT(id) AS countTimes
                 FROM 79_Friends 
                 GROUP BY activity)AS tMin))
    ) AS tempAct


-- 方法四
select 
     activity
from
    (select
          activity,
          rank()over(order by cnt) rk1,
          rank()over(order by cnt desc) rk2
     from
         (select
               activity,
               count(*) cnt
          from 
               79_Friends
          group by
               activity )t1
    )t2
where 
    rk1 != 1 and rk2 != 1;

80. 顾客的可信联系人数量

为每张发票 invoice_id 编写一个SQL查询以查找以下内容：

customer_name：与发票相关的顾客名称。
price：发票的价格。
contacts_cnt：该顾客的联系人数量。
trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。

将查询的结果按照 invoice_id 排序。

展示效果：

+------------+---------------+-------+--------------+----------------------+
| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
+------------+---------------+-------+--------------+----------------------+
| 44         | Alex          | 60    | 1            | 1                    |
| 55         | John          | 500   | 0            | 0                    |
| 66         | Bob           | 400   | 2            | 0                    |
| 77         | Alice         | 100   | 3            | 2                    |
| 88         | Alice         | 200   | 3            | 2                    |
| 99         | Bob           | 300   | 2            | 0                    |
+------------+---------------+-------+--------------+----------------------+

Create table If Not Exists 80_Customers (customer_id int, customer_name varchar(20), email varchar(30));
Create table If Not Exists 80_Contacts (user_id int, contact_name varchar(20), contact_email varchar(30));
Create table If Not Exists 80_80_Invoices (invoice_id int, price int, user_id int);
Truncate table 80_Customers;
insert into 80_Customers (customer_id, customer_name, email) values ('1', 'Alice', 'alice@leetcode.com');
insert into 80_Customers (customer_id, customer_name, email) values ('2', 'Bob', 'bob@leetcode.com');
insert into 80_Customers (customer_id, customer_name, email) values ('13', 'John', 'john@leetcode.com');
insert into 80_Customers (customer_id, customer_name, email) values ('6', 'Alex', 'alex@leetcode.com');
Truncate table 80_Contacts;
insert into 80_Contacts (user_id, contact_name, contact_email) values ('1', 'Bob', 'bob@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('1', 'John', 'john@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('1', 'Jal', 'jal@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('2', 'Omar', 'omar@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('2', 'Meir', 'meir@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('6', 'Alice', 'alice@leetcode.com');
Truncate table 80_Invoices;
insert into 80_Invoices (invoice_id, price, user_id) values ('77', '100', '1');
insert into 80_Invoices (invoice_id, price, user_id) values ('88', '200', '1');
insert into 80_Invoices (invoice_id, price, user_id) values ('99', '300', '2');
insert into 80_Invoices (invoice_id, price, user_id) values ('66', '400', '2');
insert into 80_Invoices (invoice_id, price, user_id) values ('55', '500', '13');
insert into 80_Invoices (invoice_id, price, user_id) values ('44', '60', '6');

最终SQL：

select 
     invoice_id, 
     customer_name,
     price,
     ifnull(cnt,0) contacts_cnt,
     ifnull(bc,0) trusted_contacts_cnt 
from 
     80_Invoices i
left join
     (select
           user_id, 
           count(*) cnt
      from
           80_Contacts
      group by 
           user_id ) t1
on 
      i.user_id=t1.user_id
left join
     (select
            user_id, 
            count(*) bc
      from 
            80_Contacts
      where 
            contact_name in(select customer_name from 80_Customers )
      group by user_id )t2
on 
      i.user_id = t2.user_id
left join 
      80_Customers c
on 
      i.user_id= c.customer_id
order by 
      invoice_id;

81. 获取最近第二次的活动

写一条SQL查询展示每一位用户 最近第二次 的活动，如果用户仅有一次活动，返回该活动。一个用户不能同时进行超过一项活动，以任意顺序返回结果。

展示效果：

+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+

建表语句：

Create table If Not Exists 81_UserActivity (username varchar(30), activity varchar(30), startDate date, endDate date);
Truncate table 81_UserActivity;
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-12', '2020-02-20');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Dancing', '2020-02-21', '2020-02-23');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-24', '2020-02-28');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Bob', 'Travel', '2020-02-11', '2020-02-18')

最终SQL：

select 
     username,
     activity,
     startDate,
     endDate 
from 
    (select 
          username,
          activity,
          startDate,
          endDate ,
          rank() over(partition by username order by startDate desc) rk,
          lag(startDate ,1,null) over(partition by username order by startDate ) lg
     from 81_UserActivity)t1
where
     rk=2 or (rk = 1 &&  lg is null)

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

写一段SQL查询来展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。你可以以任意顺序返回结果表。

展示效果：

+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+

建表语句：

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

最终SQL：

select
     unique_id,
     e.name
from 
     82_Employees e 
left join
     82_EmployeeUNI u
on
     e.id = u.id;

83. 按年度列出销售总额

编写一段SQL查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。

展示效果：

+------------+--------------+-------------+--------------+
| 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。

建表语句：

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

最终SQL：

    (select
           s1.product_id,
           product_name,
          '2018' as 'report_year', 
          if(period_start<'2019-01-01',(datediff(
                                           if(period_end<'2019-01-01', period_end, date('2018-12-31')),
                                           if(period_start>='2018-01-01', period_start, date('2018-01-01'))
                                           )+1
                                        ) * average_daily_sales, 0) as total_amount
     from 
          83_Sales as s1
     join 
          83_Product as p1
     on 
          s1.product_id = p1.product_id 
     having
          total_amount>0 )
union
    (select
          s2.product_id,
          product_name,
         '2019' as 'report_year',
         if( period_start<'2020-01-01', (datediff(
                                             if(period_end<'2020-01-01', period_end, date('2019-12-31')),                                                            if(period_start>='2019-01-01', period_start, date('2019-01-01'))
                                             )+1
                                        ) * average_daily_sales , 0) as total_amount
     from
          83_Sales as s2
     join
          83_Product as p2
     on
         s2.product_id = p2.product_id 
     having  total_amount>0)
union
    (select 
          s3.product_id,
          product_name,
          '2020' as 'report_year', 
          (datediff(
                   if(period_end<'2021-01-01', period_end, date('2020-12-31')),
                   if(period_start>='2020-01-01', period_start, date('2020-01-01'))
                   )+1
           ) * average_daily_sales as total_amount
     from 
          83_Sales as s3 
     join 
          83_Product as p3
     on 
          s3.product_id = p3.product_id
     having total_amount>0 ) 
order by product_id, report_year

84. 股票的资本损益

编写一个SQL查询来报告每支股票的资本损益。股票的资本损益是一次或多次买卖股票后的全部收益或损失。以任意顺序返回结果即可。

展示效果：

+---------------+-------------------+
| stock_name    | capital_gain_loss |
+---------------+-------------------+
| Corona Masks  | 9500              |
| Leetcode      | 8000              |
| Handbags      | -23000            |
+---------------+-------------------+
Leetcode 股票在第一天以1000美元的价格买入，在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。
Handbags 股票在第17天以30000美元的价格买入，在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。
Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买，在第5天以500美元的价格出售。最后，它在第6天以1000美元的价格被买走，在第10天以10000美元的价格被卖掉。资本损益是每次（’Buy'->'Sell'）操作资本收益或损失的和=（1010-10）+（500-1000）+（10000-1000）=1000-500+9000=9500美元。

Create Table If Not Exists 84_Stocks(stock_name varchar(15),operation ENUM('Sell', 'Buy'),operation_day int, price int);
Truncate table 84_Stocks;
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Leetcode', 'Buy', '1', '1000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '2', '10');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Leetcode', 'Sell', '5', '9000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Handbags', 'Buy', '17', '30000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '3', '1010');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '4', '1000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '5', '500');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '6', '1000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Handbags', 'Sell', '29', '7000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '10', '10000');

最终SQL：

select stock_name,sell-buy capital_gain_loss
from(
select stock_name ,
       sum(if(operation='Buy', price,0))over(partition by stock_name ) buy,
       sum(if(operation='Sell',price,0))over(partition by stock_name) sell
from 84_Stocks s
)t1
group by stock_name,buy,sell

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

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

展示效果：

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+

建表语句：

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

最终SQL：

select
     o.customer_id,
     customer_name 
from 
     85_Orders o 
left join
     85_Customers c
on
     o.customer_id=c.customer_id
group by
     customer_id
having 
     sum(product_name ='A')>=1 
     and
     sum(product_name='B')>=1
     and
     sum(product_name='C')=0;

86. 排名靠前的旅行者

写一段 SQL , 报告每个用户的旅行距离。返回的结果表单, 以 travelled_distance 降序排列, 如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列.

展示效果：

+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
+----------+--------------------+
Elvis 和 Lee 旅行了 450 英里, Elvis 是排名靠前的旅行者, 因为他的名字在字母表上的排序比 Lee 更小.
Bob, Jonathan, Alex 和 Alice 只有一次行程, 我们只按此次行程的全部距离对他们排序.
Donald 没有任何行程, 他的旅行距离为 0.

建表语句：

Create Table If Not Exists 86_Users (id int, name varchar(30));
Create Table If Not Exists 86_Rides (id int, user_id int, distance int);
Truncate table 86_Users;
insert into 86_Users (id, name) values ('1', 'Alice');
insert into 86_Users (id, name) values ('2', 'Bob');
insert into 86_Users (id, name) values ('3', 'Alex');
insert into 86_Users (id, name) values ('4', 'Donald');
insert into 86_Users (id, name) values ('7', 'Lee');
insert into 86_Users (id, name) values ('13', 'Jonathan');
insert into 86_Users (id, name) values ('19', 'Elvis');
Truncate table 86_Rides;
insert into 86_Rides (id, user_id, distance) values ('1', '1', '120');
insert into 86_Rides (id, user_id, distance) values ('2', '2', '317');
insert into 86_Rides (id, user_id, distance) values ('3', '3', '222');
insert into 86_Rides (id, user_id, distance) values ('4', '7', '100');
insert into 86_Rides (id, user_id, distance) values ('5', '13', '312');
insert into 86_Rides (id, user_id, distance) values ('6', '19', '50');
insert into 86_Rides (id, user_id, distance) values ('7', '7', '120');
insert into 86_Rides (id, user_id, distance) values ('8', '19', '400');
insert into 86_Rides (id, user_id, distance) values ('9', '7', '230');

最终SQL：

select
      name,
      sum(ifnull(distance,0)) travelled_distance 
from 
      86_Users u 
left join 
      86_Rides r
on 
      u.id = r.user_id
group by
      name
order by
      travelled_distance  desc, name;

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

写一个 SQL 语句，找出在所有测验中都处于中游的学生 (student_id, student_name)。成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。不要返回从来没有参加过测验的学生。返回结果表按照 student_id 排序。

展示效果：

+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+

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

建表语句：

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

最终SQL：

select 
      e.student_id,
      student_name
from 
      87_Exam e 
left join
      87_Student s
on 
      e.student_id=s.student_id
where 
      e.student_id not in(select 
                                student_id
                          from
                               (select 
                                      student_id,
                                      rank() over(partition by exam_id order by score desc) rkmax,
                                      rank() over(partition by exam_id order by score ) rkmin
                                from 
                                      87_Exam )t1
                          where 
                                rkmax = 1 or rkmin =1 )
group by 
      e.student_id,
      student_name
order by
      e.student_id;

88. 净现值查询

写一个 SQL, 找到 Queries 表中每一次查询的净现值，结果表没有顺序要求.

展示效果：

+------+--------+--------+
| 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 表中找到.

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

最终SQL：

select
      q.id,
      q.year,
      ifnull(npv,0) npv
from 
      88_Queries q
left join
      88_NPV n
on 
      q.id = n.id 
      and
      q.year = n.year;

89. 制作会话柱状图

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

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

展示效果：

+--------------+--------------+
| 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 分钟。

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

最终SQL：

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


-- 方法二
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 89_Sessions 
)b
on a.bin = b.bin
group by a.bin

90. 计算布尔表达式的值

写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式，返回的结果表没有顺序要求.

展示效果：

+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+

建表语句：

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

最终SQL：

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 
     90_Expressions e
left join
     90_Variables v1 
on
     v1.name = e.left_operand 
left join
     90_Variables v2 
on
     v2.name = e.right_operand;

91. 苹果和桔子

写一个 SQL 查询, 报告每一天苹果和桔子销售的数目的差异.返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.

查询结果表如下例所示:

+------------+--------------+
| 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).

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

select  
     sale_date,
     sold_num - ld as diff
from 
    (select
          sale_date,
          sold_num, 
          fruit,
          lead(sold_num ,1,null) over(partition by  sale_date ) ld
     from 91_Sales )t1
where 
     fruit='apples';

龍浮影

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第七部分79-91）

79. 活动参与者写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字，可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表展示效果：+--------------+| activity |+--------------+| Singing |+--------------+Create table If Not Exists 79_Friends (id int, name varchar(30), activity
复制链接

扫一扫