1551_Medium_净现值查询
表: NPV
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
| npv | int |
+---------------+---------+
(id, year) 是该表主键.
该表有每一笔存货的年份, id 和对应净现值的信息.
表: Queries
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
+---------------+---------+
(id, year) 是该表主键.
该表有每一次查询所对应存货的 id 和年份的信息.
写一个 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 表中找到.
建表+导数
create table 1551_NPV(id int, `year` int, npv int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1551_NPV" overwrite into table 1551_NPV;
select * from 1551_NPV;
create table 1551_Queries(id int, `year` int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1551_Queries" overwrite into table 1551_Queries;
select * from 1551_Queries;
知识点： coalesce(xx,yy)返回第一个不为空的值
select
q.id, q.`year`, coalesce(n.npv,0)
from 1551_Queries q
left join 1551_NPV n
on q.id=n.id and q.`year`=n.`year`
执行结果：
1564_Easy_制作会话柱状图
表：Sessions
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| session_id | int |
| duration | int |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位
你想知道用户在你的 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 分钟。
create table 1564_Sessions(session_id int, duration int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1564_Sessions" overwrite into table 1564_Sessions;
select * from 1564_Sessions;
建表+导数
create table 1564_Sessions(session_id int, duration int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1564_Sessions" overwrite into table 1564_Sessions;
select * from 1564_Sessions;
知识点：union all 关联空表
select
t2.bin,
coalesce(t1.total,
0) total
from
(
select
t.bin,
count(1) total
from
(
select
case
when duration < (5 * 60) then '[0-5>'
when (duration >= (5 * 60)
and duration < (10 * 60)) then '[5-10>'
when (duration >= (10 * 60)
and duration < (15 * 60)) then '[10-15>'
else '15 or more'
end as bin
from
1564 _Sessions ) t
group by
t.bin ) t1
right join (
select
'[0-5>' bin
union all
select
'[5-10>' bin
union all
select
'[10-15>' bin
union all
select
'15 or more' bin ) t2 on
t1.bin = t2.bin
执行结果：
1565_Medium_计算布尔表达式的值
表 Variables:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| name | varchar |
| value | int |
+---------------+---------+
name 是该表主键.
该表包含了存储的变量及其对应的值.
表 Expressions:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| left_operand | varchar |
| operator | enum |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保证存在于 Variables 表单中.
写一个 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 表中的每一个布尔表达式的值.
建表+导数
create table 1565_Variables(name string,vale int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1565_Variables" overwrite into table 1565_Variables;
select * from 1565_Variables;
create table 1565_Expressions(left_operand string, operator string, right_operand string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1565_Expressions" overwrite into table 1565_Expressions;
select * from 1565_Expressions;
知识点：left join拼出新列，在新列的基础上采用case when then end
select
t.left_operand,
t.operator,
t.right_operand,
case
when t.operator = '>' then if(t.xx > t.yy,'true','false')
when t.operator = '<' then if(t.xx < t.yy,'true','false')
when t.operator = '=' then if(t.xx = t.yy,'true','false')
end as value
from
(
select
e.left_operand,
e.operator,
e.right_operand,
v1.vale xx,
v2.vale yy
from
1565 _Expressions e
left join 1565 _Variables v1 on
e.left_operand = v1.name
left join 1565 _Variables v2 on
e.right_operand = v2.name ) t
执行结果：
1578_Medium_苹果和桔子
表: Sales
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| sale_date | date |
| fruit | enum |
| sold_num | int |
+---------------+---------+
(sale_date,fruit) 是该表主键.
该表包含了每一天中"苹果" 和 "桔子"的销售情况.
写一个 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).
建表+导数
create table 1578_Sales(sale_date date, fruit string, sold_num int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1578_Sales" overwrite into table 1578_Sales;
select * from 1578_Sales;
知识点：将求different转为求sum。通过case when then end语法实现正负号判断
select
sale_date, sum(case when fruit='apples' then sold_num when fruit='oranges' then -sold_num end) diff
from 1578_Sales
group by sale_date
执行结果：
1579_Medium_活跃用户
表 Accounts:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.
表 Logins:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| login_date | date |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.
写一个 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 是活跃用户.
建表+导数
create table 1579_Accounts(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1579_Accounts" overwrite into table 1579_Accounts;
select * from 1579_Accounts;
create table 1579_Logins(id int, login_date date) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1579_Logins" overwrite into table 1579_Logins;
select * from 1579_Logins;
知识点：求连续登陆通用解法，注意重复登陆。即排序函数采用dense_rank。
select
tt.id,a.name
from
(
select
t.id
from
(
select
id,date_sub(login_date, dense_rank() over(partition by id order by login_date)) mk
from 1579_Logins
) t
group by t.id,t.mk
having count(t.mk)>4
) tt
left join 1579_Accounts a
on tt.id=a.id
执行结果：
进阶问题:
如果活跃用户是那些至少连续n天登录账户的用户,你能否写出通用的解决方案?
1607_Medium_矩形面积
表: Points
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| x_value | int |
| y_value | int |
+---------------+---------+
id 是该表主键
每个点都用二维坐标 (x_value, y_value) 表示
写一个 SQL 语句，报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。
结果表中的每一行包含三列 (p1, p2, area)如下:
p1和p2是矩形两个对角的id- 矩形的面积由列
area****表示
请按照面积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 |
+----------+-------------+-------------+
p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |8-10| = 4
p1 = 1 且 p2 = 2 时, 面积等于 ||2-4| * |7-8| = 2
p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 面积等于 0
1608_Medium_计算税后工资
Salaries 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| company_id | int |
| employee_id | int |
| employee_name | varchar |
| salary | int |
+---------------+---------+
(company_id, employee_id) 是这个表的主键
这个表包括员工的company id, id, name 和 salary
写一条查询 SQL 来查找每个员工的税后工资
每个公司的税率计算依照以下规则
- 如果这个公司员工最高工资不到 1000 ，税率为 0%
- 如果这个公司员工最高工资在 1000 到 10000 之间，税率为 24%
- 如果这个公司员工最高工资大于 10000 ，税率为 49%
按任意顺序返回结果，税后工资结果取整
结果表格式如下例所示：
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
建表+导数
create table 1608_Salaries(company_id int, employee_id int, employee_name string, salary int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1608_Salaries" overwrite into table 1608_Salaries;
select * from 1608_Salaries;
知识点： case when then else做条件判断，ceil() 向上取整，四舍五入
select
company_id, employee_id, employee_name,
case when (salary < 1000) then salary
when (salary >= 1000 and salary < 10000) then ceil(salary - (salary*0.24))
when (salary > 10000) then ceil(salary - (salary*0.49))
end as salary
from 1608_Salaries
执行结果：
1623_Hard_周内每天的销售情况
表：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 的消费者订购的日期.
表：Items
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| item_id | varchar |
| item_name | varchar |
| item_category | varchar |
+---------------------+---------+
item_id 是该表主键
item_name 是商品的名字
item_category 是商品的类别
你是企业主，想要获得分类商品和周内每天的销售报告。
写一个SQL语句，报告 周内每天 每个商品类别下订购了多少单位。
返回结果表单 按商品类别排序 。
查询结果格式如下例所示：
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 |
+------------+----------------+---------------+
Result 表：
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 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 类别的商品
建表+导数
create table 1623_Orders(order_id int, customer_id int, order_date date, item_id string, quantity int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1623_Orders" overwrite into table 1623_Orders;
select * from 1623_Orders;
create table 1623_Items(item_id string, item_name string, item_category string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1623_Items" overwrite into table 1623_Items;
select * from 1623_Items;
知识点：group by聚合，case when then end根据结合udf条件创建新列，udf嵌套udaf。（待优化）
select
t.item_category,
coalesce(sum(Monday),0) Monday,
coalesce(sum(Tuesday),0) Tuesday,
coalesce(sum(Wednesday),0) Wednesday,
coalesce(sum(Thursday),0) Thursday ,
coalesce(sum(Friday),0) Friday,
coalesce(sum(Saturday),0) Saturday,
coalesce(sum(Sunday),0) Sunday
from
(
select
i.item_category,
case when dayofweek(o.order_date) = 2 then sum(o.quantity) end as Monday,
case when dayofweek(o.order_date) = 3 then sum(o.quantity) end as Tuesday,
case when dayofweek(o.order_date) = 4 then sum(o.quantity) end as Wednesday,
case when dayofweek(o.order_date) = 5 then sum(o.quantity) end as Thursday,
case when dayofweek(o.order_date) = 6 then sum(o.quantity) end as Friday,
case when dayofweek(o.order_date) = 7 then sum(o.quantity) end as Saturday,
case when dayofweek(o.order_date) = 1 then sum(o.quantity) end as Sunday
from
1623 _Orders o
right join 1623 _Items i on
o.item_id = i.item_id
group by
i.item_category,
o.order_date ) t
group by
t.item_category
执行结果：13.612
优化，聚合后采用sum函数嵌套udf函数。
select
i.item_category Category,
sum(case when dayofweek(o.order_date) = 2 then o.quantity else 0 end) as Monday,
sum(case when dayofweek(o.order_date) = 3 then o.quantity else 0 end) as Tuesday,
sum(case when dayofweek(o.order_date) = 4 then o.quantity else 0 end) as Wednesday,
sum(case when dayofweek(o.order_date) = 5 then o.quantity else 0 end) as Thursday,
sum(case when dayofweek(o.order_date) = 6 then o.quantity else 0 end) as Friday,
sum(case when dayofweek(o.order_date) = 7 then o.quantity else 0 end) as Saturday,
sum(case when dayofweek(o.order_date) = 1 then o.quantity else 0 end) as Sunday
from
1623_Orders o
right join 1623_Items i on
o.item_id = i.item_id
group by
i.item_category
执行结果：9.45s
1625_Easy_按日期分组销售产品
表 Activities：
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。
Activities 表：
+------------+-------------+
| sell_date | product |
+------------+-------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+-------------+
Result 表：
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。
建表+导数
create table 1625_Activities(sell_date date, product string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1625_Activities" overwrite into table 1625_Activities;
select * from 1625_Activities;
知识点：concat_ws(separator, [string | array(string)]+),返回以separator分隔的字符串
select
t.sell_date, count(1) num_sold, concat_ws(',', collect_list(t.product))
from
(
select
sell_date, product
from 1625_Activities
group by sell_date, product
) t
group by t.sell_date
执行结果：
1639_Easy_上月播放的儿童适宜电影
表: TVProgram
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| program_date | date |
| content_id | int |
| channel | varchar |
+---------------+---------+
(program_date, content_id) 是该表主键.
该表包含电视上的节目信息.
content_id 是电视一些频道上的节目的 id.
表: 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 表示内容的类型, 比如电影, 电视剧等.
写一个 SQL 语句, 报告在 2020 年 6 月份播放的儿童适宜电影的去重电影名.
返回的结果表单没有顺序要求.
查询结果的格式如下例所示.
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 |
+------------+----------------+---------------+---------------+
Result 表:
+--------------+
| title |
+--------------+
| Aladdin |
+--------------+
"Leetcode Movie" 是儿童不宜的电影.
"Alg. for Kids" 不是电影.
"Database Sols" 不是电影
"Alladin" 是电影, 儿童适宜, 并且在 2020 年 6 月份播放.
"Cinderella" 不在 2020 年 6 月份播放.
建表+导数
create table 1639_TVProgram(program_date string, content_id int, channel string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1639_TVProgram" overwrite into table 1639_TVProgram;
select * from 1639_TVProgram;
create table 1639_Content(content_id string, title string, Kids_content string, content_type string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data inpath "/leetcode/1639_Content" overwrite into table 1639_Content;
select * from 1639_Content;
知识点：where xxx on yyy on zzz根据条件精确过滤即可。
select
c.title
from
1639 _Content c
left join 1639 _TVProgram t on
c.content_id = t.content_id
where
c.Kids_content = 'Y'
and c.content_type = 'Movies'
and date_format(t.program_date,'yyyy-MM')= '2020-06'
执行结果：
