力扣(LeetCode)数据库SQL最新109题(9)
以下题目均来自力扣（LeetCode）官网和其他网站，仅用作数据库爱好者学习交流，严禁进行商业及任何非法用途。
1364. 顾客的可信联系人数量
顾客表：Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| customer_name | varchar |
| email | varchar |
+---------------+---------+
customer_id 是这张表的主键。
此表的每一行包含了某在线商店顾客的姓名和电子邮件。
联系方式表：Contacts
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | id |
| contact_name | varchar |
| contact_email | varchar |
+---------------+---------+
(user_id, contact_email) 是这张表的主键。
此表的每一行表示编号为 use_id 的顾客的某位联系人的姓名和电子邮件。
此表包含每位顾客的联系人信息，但顾客的联系人不一定存在于顾客表中。
发票表：Invoices
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| invoice_id | int |
| price | int |
| user_id | int |
+--------------+---------+
invoice_id 是这张表的主键。
此表的每一行分别表示编号为 use_id 的顾客拥有有一张编号为 invoice_id、价格为 price 的发票。
为每张发票 invoice_id 编写一个SQL查询以查找以下内容：
customer_name：与发票相关的顾客名称。price：发票的价格。contacts_cnt：该顾客的联系人数量。trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。
将查询的结果按照 invoice_id 排序。
查询结果的格式如下例所示：
Customerstable:
+-------------+---------------+--------------------+
| customer_id | customer_name | email |
+-------------+---------------+--------------------+
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 13 | John | [email protected] |
| 6 | Alex | [email protected] |
+-------------+---------------+--------------------+
Contacts table:
+-------------+--------------+--------------------+
| user_id | contact_name | contact_email |
+-------------+--------------+--------------------+
| 1 | Bob | [email protected] |
| 1 | John | [email protected] |
| 1 | Jal | [email protected] |
| 2 | Omar | [email protected] |
| 2 | Meir | [email protected] |
| 6 | Alice | [email protected] |
+-------------+--------------+--------------------+
Invoices table:
+------------+-------+---------+
| invoice_id | price | user_id |
+------------+-------+---------+
| 77 | 100 | 1 |
| 88 | 200 | 1 |
| 99 | 300 | 2 |
| 66 | 400 | 2 |
| 55 | 500 | 13 |
| 44 | 60 | 6 |
+------------+-------+---------+
Result table:
+------------+---------------+-------+--------------+----------------------+
| 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 |
+------------+---------------+-------+--------------+----------------------+
Alice 有三位联系人，其中两位(Bob 和 John)是可信联系人。
Bob 有两位联系人, 他们中的任何一位都不是可信联系人。
Alex 只有一位联系人(Alice)，并是一位可信联系人。
John 没有任何联系人。
select invoice_id,customer_name,price,ifnull(count(contact_name),0) contacts_cnt,ifnull(sum(case when contact_name in (select customer_name from customers) then 1 else 0 end),0) trusted_contacts_cntfrom invoices i join customers cuon i.user_id=cu.customer_idleft join contacts coon i.user_id=co.user_idgroup by invoice_id,customer_name,priceorder by 1;
或者：
select i.invoice_id, c1.customer_name, i.price,count(ct.contact_name) contacts_cnt ,count(c2.customer_name) trusted_contacts_cntfrom invoices i join customers c1 on i.user_id=c1.customer_idleft join contacts ct on i.user_id=ct.user_idleft join customers c2 on ct.contact_email=c2.emailgroup by i.invoice_id,c1.customer_name, i.priceorder by i.invoice_id;
1369. 获取最近第二次的活动
表: UserActivity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| username | varchar |
| activity | varchar |
| startDate | Date |
| endDate | Date |
+---------------+---------+
该表不包含主键
该表包含每个用户在一段时间内进行的活动的信息
名为 username 的用户在 startDate 到 endDate 日内有一次活动
写一条SQL查询展示每一位用户 最近第二次 的活动
如果用户仅有一次活动，返回该活动
一个用户不能同时进行超过一项活动，以 任意 顺序返回结果
下面是查询结果格式的例子：
UserActivity表:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Travel | 2020-02-12 | 2020-02-20 |
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Alice | Travel | 2020-02-24 | 2020-02-28 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
Result 表:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
Alice 最近第二次的活动是从 2020-02-24 到 2020-02-28 的旅行, 在此之前的 2020-02-21 到 2020-02-23 她进行了舞蹈
Bob 只有一条记录，我们就取这条记录
select u.*from UserActivity uwhere (u.username, u.startDate) in(select a.username, max(a.startDate)from UserActivity awhere (a.username, a.startDate) not in (select b.username, max(b.startDate)from UserActivity bgroup by b.usernamehaving count(b.username) > 1)group by a.username);
题目的意思是从现在算起最近第二次活动，也就是越靠近现在的一个活动是第一个活动，然后是最靠近现在的前一个活动，所以先剔除活动数超过1且最近的一个活动。或者这样：
SELECT u1.username,u1.activity,u1.startDate,u1.endDatefrom(SELECT u.*,rank() over(partition by username order by startDate desc) rank1,COUNT(*)over(partition by username) count1FROM UserActivity u) u1WHERE rank1 = 2or count1 =1;
1378. 使用唯一标识码替换员工ID
Employees 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。
EmployeeUNI 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| unique_id | int |
+---------------+---------+
(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。
写一段SQL查询来展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。
你可以以 任意 顺序返回结果表。
查询结果的格式如下例所示：
Employeestable:
+----+----------+
| id | name |
+----+----------+
| 1 | Alice |
| 7 | Bob |
| 11 | Meir |
| 90 | Winston |
| 3 | Jonathan |
+----+----------+EmployeeUNItable:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3 | 1 |
| 11 | 2 |
| 90 | 3 |
+----+-----------+EmployeeUNItable:
+-----------+----------+
| unique_id | name |
+-----------+----------+
| null | Alice |
| null | Bob |
| 2 | Meir |
| 3 | Winston |
| 1 | Jonathan |
+-----------+----------+
Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。
select unique_id,namefrom employees e1 left join employeeuni e2on e1.id = e2.id;
1384. 按年度列出销售总额
Product 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
+---------------+---------+
product_id 是这张表的主键。
product_name 是产品的名称。
Sales 表：
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| product_id | int |
| period_start | varchar |
| period_end | date |
| average_daily_sales | int |
+---------------------+---------+
product_id 是这张表的主键。
period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。
编写一段SQL查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。
销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。
查询结果格式如下例所示：
Producttable:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1 | LC Phone |
| 2 | LC T-Shirt |
| 3 | LC Keychain |
+------------+--------------+Salestable:
+------------+--------------+-------------+---------------------+
| 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 |
+------------+--------------+-------------+---------------------+
Result table:
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1 | LC Phone | 2019 | 3500 |
| 2 | LC T-Shirt | 2018 | 310 |
| 2 | LC T-Shirt | 2019 | 3650 |
| 2 | LC T-Shirt | 2020 | 10 |
| 3 | LC Keychain | 2019 | 31 |
| 3 | LC Keychain | 2020 | 31 |
+------------+--------------+-------------+--------------+
LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是31*1=31、31*1=31。
select t.product_id,product_name,report_year,sum(total_amount)total_amountfrom (select product_id,"2020" 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 total_amountfrom Saleshaving total_amount>0union allselect product_id,"2019" report_year,(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 total_amountfrom Saleshaving total_amount>0union allselect product_id,"2018" report_year,(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 total_amountfrom Saleshaving total_amount>0) t left join product pon p.product_id=t.product_idgroup by product_id,report_yearorder by product_id,report_year;
分别计算每条记录2018、2019和2020年的total_amount,然后用union将三年的数据融合起来
或者：
SELECTconcat(s.product_id,'') PRODUCT_ID,p.product_name PRODUCT_NAME,s.report_year REPORT_YEAR,s.total_amount*s.average_daily_sales TOTAL_AMOUNTFROM(SELECTa.product_id,a.report_year,CASE WHEN (a.report_year = YEAR(b.period_start) AND a.report_year = YEAR(b.period_end)) THEN (DATEDIFF(b.period_end,b.period_start) + 1)WHEN (a.report_year = YEAR(b.period_start) AND a.report_year != YEAR(b.period_end)) THEN (DATEDIFF(str_to_date(concat(a.report_year,'-12-31'),'%Y-%m-%d'),b.period_start) + 1)WHEN (a.report_year != YEAR(b.period_start) AND a.report_year = YEAR(b.period_end)) THEN (DATEDIFF(b.period_end,str_to_date(concat(a.report_year,'-01-01'),'%Y-%m-%d')) + 1)ELSE IF(a.report_year = '2020',366,365)END AS total_amount,b.average_daily_salesFROM(SELECT product_id,'2018' AS report_year FROM ProductUNION ALLSELECT product_id,'2019' AS report_year FROM ProductUNION ALLSELECT product_id,'2020' AS report_year FROM Product)aINNER JOIN Sales bON a.product_id = b.product_idAND (a.report_year BETWEEN YEAR(b.period_start) AND YEAR(b.period_end))ORDER BY product_id,report_year)s, Product pWHERE s.product_id = p.product_idORDER BY product_id,report_year;
1393. 股票的资本损益
Stocks 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| stock_name | varchar |
| operation | enum |
| operation_day | int |
| price | int |
+---------------+---------+
(stock_name, day) 是这张表的主键
operation 列使用的是一种枚举类型，包括：('Sell','Buy')
此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
保证股票的每次'Sell'操作前，都有相应的'Buy'操作。
编写一个SQL查询来报告每支股票的资本损益。
股票的资本损益是一次或多次买卖股票后的全部收益或损失。
以任意顺序返回结果即可。
SQL查询结果的格式如下例所示：
Stocks表:
+---------------+-----------+---------------+--------+
| stock_name | operation | operation_day | price |
+---------------+-----------+---------------+--------+
| Leetcode | Buy | 1 | 1000 |
| Corona Masks | Buy | 2 | 10 |
| Leetcode | Sell | 5 | 9000 |
| Handbags | Buy | 17 | 30000 |
| Corona Masks | Sell | 3 | 1010 |
| Corona Masks | Buy | 4 | 1000 |
| Corona Masks | Sell | 5 | 500 |
| Corona Masks | Buy | 6 | 1000 |
| Handbags | Sell | 29 | 7000 |
| Corona Masks | Sell | 10 | 10000 |
+---------------+-----------+---------------+--------+
Result 表:
+---------------+-------------------+
| 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美元。
select stock_name,sum(case when operation = 'Buy' then price*-1 else price end) as capital_gain_lossfrom Stocksgroup by stock_name;
1398. 购买了产品A和产品B却没有购买产品C的顾客
Customers 表：
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------------+---------+
customer_id 是这张表的主键。
customer_name 是顾客的名称。
Orders 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_name | varchar |
+---------------+---------+
order_id 是这张表的主键。
customer_id 是购买了名为 "product_name" 产品顾客的id。
请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和 customer_name ），我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照 customer_id 排序。
查询结果如下例所示。
Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | Daniel |
| 2 | Diana |
| 3 | Elizabeth |
| 4 | Jhon |
+-------------+---------------+
Orders table:
+------------+--------------+---------------+
| order_id | customer_id | product_name |
+------------+--------------+---------------+
| 10 | 1 | A |
| 20 | 1 | B |
| 30 | 1 | D |
| 40 | 1 | C |
| 50 | 2 | A |
| 60 | 3 | A |
| 70 | 3 | B |
| 80 | 3 | D |
| 90 | 4 | C |
+------------+--------------+---------------+
Result table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3 | Elizabeth |
+-------------+---------------+
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。
select * from Customers cwhere 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');
1407. 排名靠前的旅行者
表单: Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表单主键.
name 是用户名字.
表单: Rides
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+
id 是该表单主键.
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance.
写一段 SQL , 报告每个用户的旅行距离.
返回的结果表单, 以 travelled_distance 降序排列, 如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列.
查询结果格式, 如下例所示.
Users 表单:
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Alex |
| 4 | Donald |
| 7 | Lee |
| 13 | Jonathan |
| 19 | Elvis |
+------+-----------+
Rides 表单:
+------+----------+----------+
| id | user_id | distance |
+------+----------+----------+
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 13 | 312 |
| 6 | 19 | 50 |
| 7 | 7 | 120 |
| 8 | 19 | 400 |
| 9 | 7 | 230 |
+------+----------+----------+
Result 表单:
+----------+--------------------+
| 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.
select name,ifnull(sum(distance),0) travelled_distancefrom rides r right join users uon r.user_id=u.idgroup by nameorder by travelled_distance desc,name;
1412. Find the Quiet Students in All Exams
Table: Student
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| student_id | int |
| student_name | varchar |
+---------------------+---------+
student_id is the primary key for this table.
student_name is the name of the student.
Table: Exam
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| exam_id | int |
| student_id | int |
| score | int |
+---------------+---------+
(exam_id, student_id) is the primary key for this table.
Student with student_id got score points in exam with id exam_id.
A "quite" student is the one who took at least one exam and didn't score neither the high score nor the low score.
Write an SQL query to report the students (student_id, student_name) being "quiet" in ALL exams.
Don't return the student who has never taken any exam. Return the result table ordered by student_id.
The query result format is in the following example.
Student table:
+-------------+---------------+
| student_id | student_name |
+-------------+---------------+
| 1 | Daniel |
| 2 | Jade |
| 3 | Stella |
| 4 | Jonathan |
| 5 | Will |
+-------------+---------------+
Exam table:
+------------+--------------+-----------+
| 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 table:
+-------------+---------------+
| student_id | student_name |
+-------------+---------------+
| 2 | Jade |
+-------------+---------------+
For exam 1: Student 1 and 3 hold the lowest and high score respectively.
For exam 2: Student 1 hold both highest and lowest score.
For exam 3 and 4: Studnet 1 and 4 hold the lowest and high score respectively.
Student 2 and 5 have never got the highest or lowest in any of the exam.
Since student 5 is not taking any exam, he is excluded from the result.
So, we only return the information of Student 2.
select student_id, student_namefrom Studentwhere student_id in(select distinct student_id from Exam)and student_id not in(select student_idfrom Exam where (exam_id, score) in(select exam_id, max(score) socrefrom Examgroup by exam_id))and student_id not in(select student_idfrom Exam where (exam_id, score) in(select exam_id, min(score) socrefrom Examgroup by exam_id));
或者：
select student_id, student_namefrom studentwhere student_id not in(select student_idfrom exam ewhere score >= all(select score from exam where exam_id=e.exam_id)or score <= all(select score from exam where exam_id=e.exam_id))and student_id in (select student_id from exam);
1421. NPV Queries
Table: NPV
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
| npv | int |
+---------------+---------+
(id, year) is the primary key of this table.
The table has information about the id and the year of each inventory and the corresponding net present value.
Table: Queries
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
+---------------+---------+
(id, year) is the primary key of this table.
The table has information about the id and the year of each inventory query.
Write an SQL query to find the npv of all each query of queries table.
Return the result table in any order.
The query result format is in the following example:
NPV table:
+------+--------+--------+
| 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 table:
+------+--------+
| id | year |
+------+--------+
| 1 | 2019 |
| 2 | 2008 |
| 3 | 2009 |
| 7 | 2018 |
| 7 | 2019 |
| 7 | 2020 |
| 13 | 2019 |
+------+--------+
Result table:
+------+--------+--------+
| id | year | npv |
+------+--------+--------+
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 7 | 2018 | 0 |
| 7 | 2019 | 0 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
+------+--------+--------+
The npv value of (7, 2018) is not present in the NPV table, we consider it 0.
The npv values of all other queries can be found in the NPV table.
select q.id,q.year,ifnull(n.npv,0) as npvfrom Queries q left join NPV non n.id=q.id and n.year=q.yearorder by q.id, q.year;
1435. Create a Session Bar Chart
Table: Sessions
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| session_id | int |
| duration | int |
+---------------------+---------+
session_id is the primary key for this table.
duration is the time in seconds that a user has visited the application.
You want to know how long a user visits your application. You decided to create bins of "[0-5>", "[5-10>", "[10-15>" and "15 minutes or more" and count the number of sessions on it.
Write an SQL query to report the (bin, total) in any order.
The query result format is in the following example.
Sessions table:
+-------------+---------------+
| session_id | duration |
+-------------+---------------+
| 1 | 30 |
| 2 | 199 |
| 3 | 299 |
| 4 | 580 |
| 5 | 1000 |
+-------------+---------------+
Result table:
+--------------+--------------+
| bin | total |
+--------------+--------------+
| [0-5> | 3 |
| [5-10> | 1 |
| [10-15> | 0 |
| 15 or more | 1 |
+--------------+--------------+
For session_id 1, 2 and 3 have a duration greater or equal than 0 minutes and less than 5 minutes.
For session_id 4 has a duration greater or equal than 5 minutes and less than 10 minutes.
There are no session with a duration greater or equial than 10 minutes and less than 15 minutes.
For session_id 5 has a duration greater or equal than 15 minutes.
select '[0-5>' as bin,count(*) as totalfrom sessions swhere s.duration/60 >= 0 and s.duration/60 < 5unionselect '[5-10>' as bin,count(*) as totalfrom Sessions swhere s.duration/60 >= 5 and s.duration/60 < 10unionselect '[10-15>' as bin,count(*) as totalfrom Sessions swhere s.duration/60 >= 10 and s.duration/60 < 15unionselect '15 or more' as bin,count(*) as totalfrom Sessions swhere s.duration/60 >= 15;
1440. Evaluate Boolean Expression
Table Variables:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| name | varchar |
| value | int |
+---------------+---------+
name is the primary key for this table.
This table contains the stored variables and their values.
Table Expressions:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| left_operand | varchar |
| operator | enum |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) is the primary key for this table.
This table contains a boolean expression that should be evaluated.
operator is an enum that takes one of the values ('<', '>', '=')
The values of left_operand and right_operand are guaranteed to be in the Variables table.
Write an SQL query to evaluate the boolean expressions in Expressions table.
Return the result table in any order.
The query result format is in the following example.
Variables table:
+------+-------+
| name | value |
+------+-------+
| x | 66 |
| y | 77 |
+------+-------+
Expressions table:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x | > | y |
| x | < | y |
| x | = | y |
| y | > | x |
| y | < | x |
| x | = | x |
+--------------+----------+---------------+
Result table:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x | > | y | false |
| x | < | y | true |
| x | = | y | false |
| y | > | x | true |
| y | < | x | false |
| x | = | x | true |
+--------------+----------+---------------+-------+
As shown, you need find the value of each boolean exprssion in the table using the variables table.
select e.left_operand,e.operator,e.right_operand,case e.operatorwhen '>' 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 valuefrom Expressions eleft join Variables v1 on v1.name = e.left_operandleft join Variables v2 on v2.name = e.right_operand;
或者：
select e.left_operand,e.operator,e.right_operand,case e.operatorwhen '>' then case when v1.value>v2.value then 'true' else 'false' endwhen '<' then case when v1.value<v2.value then 'true' else 'false' endelse case when v1.value=v2.value then 'true' else 'false' endend valuefrom Expressions eleft join Variables v1 on v1.name = e.left_operandleft join Variables v2 on v2.name = e.right_operand;
截止2020年5月7日，全剧终！。。。终于干掉了