第5天合并
🚩175. 组合两个表
🚀 表: Person +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ personId 是该表的主键列。 该表包含一些人的 ID 和他们的姓和名的信息。 表: Address +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ addressId 是该表的主键列。 该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。 🚀 需求 编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为空 null 。 以 任意顺序 返回结果表。 查询结果格式如下所示。 示例 1: 输入: Person表: +----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ | 1 | Wang | Allen | | 2 | Alice | Bob | +----------+----------+-----------+ Address表: +-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ | 1 | 2 | New York City | New York | | 2 | 3 | Leetcode | California | +-----------+----------+---------------+------------+ 输出: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang | Null | Null | | Bob | Alice | New York City | New York | +-----------+----------+---------------+----------+ 解释: 地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。 addressId = 1 包含了 personId = 2 的地址信息。 🐴🐴 答案 # Write your MySQL query statement below select a.firstName, a.lastName, b.city, b.state from Person a left join Address b on a.PersonId = b.PersonId /* Write your T-SQL query statement below */ select a.firstName, a.lastName, b.city, b.state from Person a left join Address b on a.PersonId = b.PersonId /* Write your PL/SQL query statement below */ select a.firstName "firstName", a.lastName "lastName", b.city "city", b.state "state" from Person a,Address b where a.PersonId = b.PersonId(+) order by 1
🚩1581. 进店却未进行过交易的顾客
🚀 表：Visits +-------------+---------+ | Column Name | Type | +-------------+---------+ | visit_id | int | | customer_id | int | +-------------+---------+ visit_id 是该表的主键。 该表包含有关光临过购物中心的顾客的信息。 表：Transactions +----------------+---------+ | Column Name | Type | +----------------+---------+ | transaction_id | int | | visit_id | int | | amount | int | +----------------+---------+ transaction_id 是此表的主键。 此表包含 visit_id 期间进行的交易的信息。 🚀 需求 有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。 返回以 任何顺序 排序的结果表。 查询结果格式如下例所示。 示例 1： 输入: Visits +----------+-------------+ | visit_id | customer_id | +----------+-------------+ | 1 | 23 | | 2 | 9 | | 4 | 30 | | 5 | 54 | | 6 | 96 | | 7 | 54 | | 8 | 54 | +----------+-------------+ Transactions +----------------+----------+--------+ | transaction_id | visit_id | amount | +----------------+----------+--------+ | 2 | 5 | 310 | | 3 | 5 | 300 | | 9 | 5 | 200 | | 12 | 1 | 910 | | 13 | 2 | 970 | +----------------+----------+--------+ 输出: +-------------+----------------+ | customer_id | count_no_trans | +-------------+----------------+ | 54 | 2 | | 30 | 1 | | 96 | 1 | +-------------+----------------+ 解释: ID = 23 的顾客曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。 ID = 9 的顾客曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。 ID = 30 的顾客曾经去过购物中心，并且没有进行任何交易。 ID = 54 的顾客三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。 ID = 96 的顾客曾经去过购物中心，并且没有进行任何交易。 如我们所见，ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。 🐴🐴 答案 # Write your MySQL query statement below select customer_id "customer_id", count(*) "count_no_trans" from Visits a where not exists (select * from Transactions b where a.visit_id = b.visit_id ) group by customer_id order by 2 desc /* Write your T-SQL query statement below */ select customer_id, count(*) count_no_trans from Visits a where not exists (select * from Transactions b where a.visit_id = b.visit_id ) group by customer_id order by count_no_trans desc /* Write your PL/SQL query statement below */ select customer_id "customer_id", count(*) "count_no_trans" from Visits a where not exists (select * from Transactions b where a.visit_id = b.visit_id ) group by customer_id order by 2 desc
🚩1148.文章浏览I
🚀 Views 表： +---------------+---------+ | Column Name | Type | +---------------+---------+ | article_id | int | | author_id | int | | viewer_id | int | | view_date | date | +---------------+---------+ 此表无主键，因此可能会存在重复行。 此表的每一行都表示某人在某天浏览了某位作者的某篇文章。 请注意，同一人的 author_id 和 viewer_id 是相同的。 🚀 需求 请编写一条 SQL 查询以找出所有浏览过自己文章的作者，结果按照 id 升序排列。 查询结果的格式如下所示： Views 表： +------------+-----------+-----------+------------+ | article_id | author_id | viewer_id | view_date | +------------+-----------+-----------+------------+ | 1 | 3 | 5 | 2019-08-01 | | 1 | 3 | 6 | 2019-08-02 | | 2 | 7 | 7 | 2019-08-01 | | 2 | 7 | 6 | 2019-08-02 | | 4 | 7 | 1 | 2019-07-22 | | 3 | 4 | 4 | 2019-07-21 | | 3 | 4 | 4 | 2019-07-21 | +------------+-----------+-----------+------------+ 结果表： +------+ | id | +------+ | 4 | | 7 | +------+ 🐴🐴 答案 # Write your MySQL query statement below select distinct author_id id from Views where author_id = viewer_id order by id /* Write your T-SQL query statement below */ select distinct author_id id from Views where author_id = viewer_id order by id /* Write your PL/SQL query statement below */ select distinct author_id "id" from Views where author_id = viewer_id order by 1
第6天 合并
🚩197. 上升的温度
表： Weather +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id 是这个表的主键 该表包含特定日期的温度信息 编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。 返回结果 不要求顺序 。 查询结果格式如下例。 示例 1： 输入： Weather 表： +----+------------+-------------+ | id | recordDate | Temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+ 输出： +----+ | id | +----+ | 2 | | 4 | +----+ 解释： 2015-01-02 的温度比前一天高（10 -> 25） 2015-01-04 的温度比前一天高（20 -> 30） 🐴🐴 答案 # Write your MySQL query statement below select a.Id from Weather as a join Weather as b on a.Temperature > b.Temperature and dateDiff(a.RecordDate,b.RecordDate) = 1 /* Write your T-SQL query statement below */ SELECT w2.id AS 'Id' FROM Weather w1, Weather w2 WHERE DATEDIFF(day, w1.RecordDate, w2.RecordDate) = 1 AND w1.Temperature < w2.Temperature; /* Write your PL/SQL query statement below */ select id "id" from ( select id, Temperature, lag(Temperature) over(order by recordDate) dk from Weather ) where Temperature>dk
🚩607. 销售员
🚀 表: SalesPerson +-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id 是该表的主键列。 该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。 表: Company +-------------+---------+ | Column Name | Type | +-------------+---------+ | com_id | int | | name | varchar | | city | varchar | +-------------+---------+ com_id 是该表的主键列。 该表的每一行都表示公司的名称和 ID ，以及公司所在的城市。 表: Orders +-------------+------+ | Column Name | Type | +-------------+------+ | order_id | int | | order_date | date | | com_id | int | | sales_id | int | | amount | int | +-------------+------+ order_id 是该表的主键列。 com_id 是 Company 表中 com_id 的外键。 sales_id 是来自销售员表 sales_id 的外键。 该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。 🚀 需求 编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。 以任意顺序 返回结果表。 查询结果格式如下所示。 示例： 输入： SalesPerson 表: +----------+------+--------+-----------------+------------+ | sales_id | name | salary | commission_rate | hire_date | +----------+------+--------+-----------------+------------+ | 1 | John | 100000 | 6 | 4/1/2006 | | 2 | Amy | 12000 | 5 | 5/1/2010 | | 3 | Mark | 65000 | 12 | 12/25/2008 | | 4 | Pam | 25000 | 25 | 1/1/2005 | | 5 | Alex | 5000 | 10 | 2/3/2007 | +----------+------+--------+-----------------+------------+ Company 表: +--------+--------+----------+ | com_id | name | city | +--------+--------+----------+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +--------+--------+----------+ Orders 表: +----------+------------+--------+----------+--------+ | order_id | order_date | com_id | sales_id | amount | +----------+------------+--------+----------+--------+ | 1 | 1/1/2014 | 3 | 4 | 10000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +----------+------------+--------+----------+--------+ 输出： +------+ | name | +------+ | Amy | | Mark | | Alex | +------+ 解释： 根据表 orders 中的订单 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。 所以我们需要输出表 salesperson 中所有其他人的名字。 🐴🐴 答案 # Write your MySQL query statement below select a.name from SalesPerson a where not exists ( select n.name from Orders m,Company n where m.com_id = n.com_id and n.name = 'RED' and m.sales_id = a.sales_id) /* Write your T-SQL query statement below */ select a.name from SalesPerson a where not exists ( select n.name from Orders m,Company n where m.com_id = n.com_id and n.name = 'RED' and m.sales_id = a.sales_id) /* Write your T-SQL query statement below */ select a.name from SalesPerson a where sales_id not in ( select m.sales_id from Orders m,Company n where m.com_id = n.com_id and n.name = 'RED' and m.sales_id = a.sales_id)
第7天 统计去重
🚩1141.查询近30天活跃用户数
🚀 活动记录表：Activity +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | session_id | int | | activity_date | date | | activity_type | enum | +---------------+---------+ 该表是用户在社交网站的活动记录。 该表没有主键，可能包含重复数据。 activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。 每个 session_id 只属于一个用户。 🚀 需求 请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。 以 任意顺序 返回结果表。 查询结果示例如下。 示例 1: 输入： Activity table: +---------+------------+---------------+---------------+ | user_id | session_id | activity_date | activity_type | +---------+------------+---------------+---------------+ | 1 | 1 | 2019-07-20 | open_session | | 1 | 1 | 2019-07-20 | scroll_down | | 1 | 1 | 2019-07-20 | end_session | | 2 | 4 | 2019-07-20 | open_session | | 2 | 4 | 2019-07-21 | send_message | | 2 | 4 | 2019-07-21 | end_session | | 3 | 2 | 2019-07-21 | open_session | | 3 | 2 | 2019-07-21 | send_message | | 3 | 2 | 2019-07-21 | end_session | | 4 | 3 | 2019-06-25 | open_session | | 4 | 3 | 2019-06-25 | end_session | +---------+------------+---------------+---------------+ 输出： +------------+--------------+ | day | active_users | +------------+--------------+ | 2019-07-20 | 2 | | 2019-07-21 | 2 | +------------+--------------+ 解释：注意非活跃用户的记录不需要展示。 🐴🐴 答案 # Write your MySQL query statement below select activity_date day, count(distinct user_id) active_users from activity where activity_date > date_sub('2019-07-27', interval 30 day) and activity_date <= '2019-07-27' group by activity_date /* Write your T-SQL query statement below */ select activity_date day, count(distinct user_id ) active_users from Activity where activity_date > CONVERT(varchar(100), dateadd(dd,-30,'2019-07-27'), 23) and activity_date<='2019-07-27' group by activity_date /* Write your PL/SQL query statement below */ select to_char(activity_date,'yyyy-mm-dd') "day", count(distinct user_id ) "active_users" from Activity where activity_date > to_date('20190727','yyyymmdd')-30 and to_char(activity_date,'yyyy-mm-dd') < '2019-07-27' group by to_char(activity_date,'yyyy-mm-dd')
🚩1693.每天的领导和合伙人
🚀 表：DailySales +-------------+---------+ | Column Name | Type | +-------------+---------+ | date_id | date | | make_name | varchar | | lead_id | int | | partner_id | int | +-------------+---------+ 该表没有主键。 该表包含日期、产品的名称，以及售给的领导和合伙人的编号。 名称只包含小写英文字母。 🚀 需求 写一条 SQL 语句，使得对于每一个 date_id 和 make_name，返回不同的 lead_id 以及不同的 partner_id 的数量。 按 任意顺序 返回结果表。 查询结果格式如下示例所示。 示例 1: 输入： DailySales 表： +-----------+-----------+---------+------------+ | date_id | make_name | lead_id | partner_id | +-----------+-----------+---------+------------+ | 2020-12-8 | toyota | 0 | 1 | | 2020-12-8 | toyota | 1 | 0 | | 2020-12-8 | toyota | 1 | 2 | | 2020-12-7 | toyota | 0 | 2 | | 2020-12-7 | toyota | 0 | 1 | | 2020-12-8 | honda | 1 | 2 | | 2020-12-8 | honda | 2 | 1 | | 2020-12-7 | honda | 0 | 1 | | 2020-12-7 | honda | 1 | 2 | | 2020-12-7 | honda | 2 | 1 | +-----------+-----------+---------+------------+ 输出： +-----------+-----------+--------------+-----------------+ | date_id | make_name | unique_leads | unique_partners | +-----------+-----------+--------------+-----------------+ | 2020-12-8 | toyota | 2 | 3 | | 2020-12-7 | toyota | 1 | 2 | | 2020-12-8 | honda | 2 | 2 | | 2020-12-7 | honda | 3 | 2 | +-----------+-----------+--------------+-----------------+ 解释： 在 2020-12-8，丰田（toyota）有领导者 = [0, 1] 和合伙人 = [0, 1, 2] ，同时本田（honda）有领导者 = [1, 2] 和合伙人 = [1, 2]。 在 2020-12-7，丰田（toyota）有领导者 = [0] 和合伙人 = [1, 2] ，同时本田（honda）有领导者 = [0, 1, 2] 和合伙人 = [1, 2]。 🐴🐴 答案 # Write your MySQL query statement below select date_id,make_name, count(distinct lead_id) unique_leads, count(distinct partner_id) unique_partners from DailySales group by date_id,make_name /* Write your PL/SQL query statement below */ select to_char(date_id,'yyyy-mm-dd') "date_id", make_name "make_name", count(distinct lead_id) "unique_leads", count(distinct partner_id) "unique_partners" from DailySales group by to_char(date_id,'yyyy-mm-dd'),make_name
🚩1729.求关注者的数量
🚀 表： Followers +-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | follower_id | int | +-------------+------+ (user_id, follower_id) 是这个表的主键。 该表包含一个关注关系中关注者和用户的编号，其中关注者关注用户。 🚀 需求 写出 SQL 语句，对于每一个用户，返回该用户的关注者数量。 按 user_id 的顺序返回结果表。 查询结果的格式如下示例所示。 示例 1： 输入： Followers 表： +---------+-------------+ | user_id | follower_id | +---------+-------------+ | 0 | 1 | | 1 | 0 | | 2 | 0 | | 2 | 1 | +---------+-------------+ 输出： +---------+----------------+ | user_id | followers_count| +---------+----------------+ | 0 | 1 | | 1 | 1 | | 2 | 2 | +---------+----------------+ 解释： 0 的关注者有 {1} 1 的关注者有 {0} 2 的关注者有 {0,1} 🐴🐴 答案 # Write your MySQL query statement below SELECT user_id, COUNT(DISTINCT follower_id) followers_count FROM Followers GROUP BY user_id ORDER BY user_id; /* Write your T-SQL query statement below */ SELECT user_id, COUNT(DISTINCT follower_id) followers_count FROM Followers GROUP BY user_id ORDER BY user_id; /* Write your PL/SQL query statement below */ SELECT user_id "user_id", COUNT(DISTINCT follower_id) "followers_count" FROM Followers GROUP BY user_id ORDER BY user_id;
第8天 计算函数
🚩586. 订单最多的客户
🚀 表: Orders +-----------------+----------+ | Column Name | Type | +-----------------+----------+ | order_number | int | | customer_number | int | +-----------------+----------+ Order_number是该表的主键。 此表包含关于订单ID和客户ID的信息。 🚀 需求 编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。 查询结果格式如下所示。 示例 1: 输入: Orders 表: +--------------+-----------------+ | order_number | customer_number | +--------------+-----------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 3 | +--------------+-----------------+ 输出: +-----------------+ | customer_number | +-----------------+ | 3 | +-----------------+ 解释: customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单。 所以结果是该顾客的 customer_number ，也就是 3 。 🐴🐴 答案 # Write your MySQL query statement below select customer_number from Orders group by customer_number order by count(*) desc limit 1 /* Write your T-SQL query statement below */ select top 1 customer_number from Orders group by customer_number order by count(*) desc /* Write your PL/SQL query statement below */ select * from ( select customer_number "customer_number" from Orders group by customer_number order by count(*) desc ) where rownum =1
🚩511. 游戏玩法分析 I
🚀 活动表 Activity： +--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ 表的主键是 (player_id, event_date)。 这张表展示了一些游戏玩家在游戏平台上的行为活动。 每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。 🚀 需求 写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。 查询结果的格式如下所示： Activity 表： +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ Result 表： +-----------+-------------+ | player_id | first_login | +-----------+-------------+ | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 | +-----------+-------------+ 🐴🐴 答案 # Write your MySQL query statement below select player_id,min(event_date) first_login from Activity group by player_id /* Write your T-SQL query statement below */ select player_id,min(event_date) first_login from Activity group by player_id /* Write your PL/SQL query statement below */ select player_id "player_id", to_char(event_date,'yyyy-mm-dd') "first_login" from ( select player_id, event_date, rank() over(partition by player_id order by event_date) dk from Activity ) where dk =1 order by 1
🚩1890.2020年最后一次登录
🚀 表: Logins +----------------+----------+ | 列名 | 类型 | +----------------+----------+ | user_id | int | | time_stamp | datetime | +----------------+----------+ (user_id, time_stamp) 是这个表的主键。 每一行包含的信息是user_id 这个用户的登录时间。 🚀 需求 编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。 返回的结果集可以按 任意顺序 排列。 查询结果格式如下例。 示例 1: 输入： Logins 表: +---------+---------------------+ | user_id | time_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 6 | 2021-04-21 14:06:06 | | 6 | 2019-03-07 00:18:15 | | 8 | 2020-02-01 05:10:53 | | 8 | 2020-12-30 00:46:50 | | 2 | 2020-01-16 02:49:50 | | 2 | 2019-08-25 07:59:08 | | 14 | 2019-07-14 09:00:00 | | 14 | 2021-01-06 11:59:59 | +---------+---------------------+ 输出： +---------+---------------------+ | user_id | last_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 8 | 2020-12-30 00:46:50 | | 2 | 2020-01-16 02:49:50 | +---------+---------------------+ 解释： 6号用户登录了3次，但是在2020年仅有一次，所以结果集应包含此次登录。 8号用户在2020年登录了2次，一次在2月，一次在12月，所以，结果集应该包含12月的这次登录。 2号用户登录了2次，但是在2020年仅有一次，所以结果集应包含此次登录。 14号用户在2020年没有登录，所以结果集不应包含。 🐴🐴 答案 # Write your MySQL query statement below SELECT user_id, max(time_stamp) last_stamp FROM Logins WHERE year(time_stamp) = 2020 GROUP BY user_id /* Write your T-SQL query statement below */ select user_id,max(time_stamp) as last_stamp from Logins where time_stamp between '2020-01-01 00:00:00' and '2020-12-31 12:59:59' group by user_id /* Write your PL/SQL query statement below */ SELECT user_id "user_id", max(time_stamp) "last_stamp" FROM Logins WHERE to_char(time_stamp, 'yyyy' ) = '2020' GROUP BY user_id
🚩1741.查找每个员工花费的总时间
🚀 表: Employees +-------------+------+ | Column Name | Type | +-------------+------+ | emp_id | int | | event_day | date | | in_time | int | | out_time | int | +-------------+------+ (emp_id, event_day, in_time) 是这个表的主键。 该表显示了员工在办公室的出入情况。 event_day 是此事件发生的日期，in_time 是员工进入办公室的时间，而 out_time 是他们离开办公室的时间。 in_time 和 out_time 的取值在1到1440之间。 题目保证同一天没有两个事件在时间上是相交的，并且保证 in_time 小于 out_time。 🚀 需求 编写一个SQL查询以计算每位员工每天在办公室花费的总时间（以分钟为单位）。 请注意，在一天之内，同一员工是可以多次进入和离开办公室的。 在办公室里一次进出所花费的时间为out_time 减去 in_time。 返回结果表单的顺序无要求。 查询结果的格式如下： Employees table: +--------+------------+---------+----------+ | emp_id | event_day | in_time | out_time | +--------+------------+---------+----------+ | 1 | 2020-11-28 | 4 | 32 | | 1 | 2020-11-28 | 55 | 200 | | 1 | 2020-12-03 | 1 | 42 | | 2 | 2020-11-28 | 3 | 33 | | 2 | 2020-12-09 | 47 | 74 | +--------+------------+---------+----------+ Result table: +------------+--------+------------+ | day | emp_id | total_time | +------------+--------+------------+ | 2020-11-28 | 1 | 173 | | 2020-11-28 | 2 | 30 | | 2020-12-03 | 1 | 41 | | 2020-12-09 | 2 | 27 | +------------+--------+------------+ 雇员 1 有三次进出: 有两次发生在 2020-11-28 花费的时间为 (32 - 4) + (200 - 55) = 173, 有一次发生在 2020-12-03 花费的时间为 (42 - 1) = 41。 雇员 2 有两次进出: 有一次发生在 2020-11-28 花费的时间为 (33 - 3) = 30, 有一次发生在 2020-12-09 花费的时间为 (74 - 47) = 27。 🐴🐴 答案 # Write your MySQL query statement below select event_day day, emp_id, sum(out_time-in_time) total_time from Employees group by event_day,emp_id /* Write your T-SQL query statement below */ select event_day day, emp_id, sum(out_time-in_time) total_time from Employees group by event_day,emp_id /* Write your PL/SQL query statement below */ select to_char(event_day,'yyyy-mm-dd') "day", emp_id "emp_id", sum(out_time-in_time) "total_time" from Employees group by to_char(event_day,'yyyy-mm-dd'),emp_id
第9天 控制流
🚩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美元。 🐴🐴 答案 # Write your MySQL query statement below select stock_name, sum(case when operation='Sell' then price else 0 end) -sum(case when operation='Buy' then price else 0 end) capital_gain_loss from Stocks group by stock_name /* Write your T-SQL query statement below */ select stock_name, sum(case when operation='buy' then -price else price end ) as 'capital_gain_loss' from Stocks group by stock_name /* Write your PL/SQL query statement below */ select stock_name "stock_name", sum( case when operation = 'Sell' then price when operation = 'Buy' then -price end ) as "capital_gain_loss" from Stocks group by stock_name
🚩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。 🐴🐴 答案 # Write your MySQL query statement below select max(name) name,ifnull(sum(distance),0) travelled_distance from Users u left join Rides r on r.user_id=u.id group by r.user_id order by travelled_distance desc,name /* Write your T-SQL query statement below */ select max(name) name,isnull(sum(distance),0) travelled_distance from Users u left join Rides r on r.user_id=u.id group by r.user_id order by travelled_distance desc,name /* Write your PL/SQL query statement below */ select max(name) "name", sum(nvl(distance,0)) "travelled_distance" from Users u left join Rides r on r.user_id=u.id group by r.user_id order by 2 desc,1
🚩1158.市场分析 I
🚀 Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ 此表主键是 user_id。 表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。 Table: Orders +---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ 此表主键是 order_id。 外键是 item_id 和（buyer_id，seller_id）。 Table: Items +---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ 此表主键是 item_id。 🚀 需求 请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。 以 任意顺序 返回结果表。 查询结果格式如下。 示例 1: 输入： Users 表: +---------+------------+----------------+ | user_id | join_date | favorite_brand | +---------+------------+----------------+ | 1 | 2018-01-01 | Lenovo | | 2 | 2018-02-09 | Samsung | | 3 | 2018-01-19 | LG | | 4 | 2018-05-21 | HP | +---------+------------+----------------+ Orders 表: +----------+------------+---------+----------+-----------+ | order_id | order_date | item_id | buyer_id | seller_id | +----------+------------+---------+----------+-----------+ | 1 | 2019-08-01 | 4 | 1 | 2 | | 2 | 2018-08-02 | 2 | 1 | 3 | | 3 | 2019-08-03 | 3 | 2 | 3 | | 4 | 2018-08-04 | 1 | 4 | 2 | | 5 | 2018-08-04 | 1 | 3 | 4 | | 6 | 2019-08-05 | 2 | 2 | 4 | +----------+------------+---------+----------+-----------+ Items 表: +---------+------------+ | item_id | item_brand | +---------+------------+ | 1 | Samsung | | 2 | Lenovo | | 3 | LG | | 4 | HP | +---------+------------+ 输出： +-----------+------------+----------------+ | buyer_id | join_date | orders_in_2019 | +-----------+------------+----------------+ | 1 | 2018-01-01 | 1 | | 2 | 2018-02-09 | 2 | | 3 | 2018-01-19 | 0 | | 4 | 2018-05-21 | 0 | +-----------+------------+----------------+ 🐴🐴 答案 # Write your MySQL query statement below SELECT user_id AS buyer_id, join_date, IFNULL(Buy.cnt, 0) AS orders_in_2019 FROM Users LEFT JOIN ( SELECT buyer_id, COUNT(order_id) AS cnt FROM Orders WHERE Year(order_date)='2019' GROUP BY buyer_id ) AS Buy ON user_id=Buy.buyer_id /* Write your T-SQL query statement below */ SELECT user_id AS buyer_id, join_date, isnull(Buy.cnt,0) AS orders_in_2019 FROM Users LEFT JOIN ( SELECT buyer_id, COUNT(order_id) AS cnt FROM Orders WHERE Year(order_date)='2019' GROUP BY buyer_id ) Buy ON user_id=Buy.buyer_id /* Write your PL/SQL query statement below */ select buyer_id "buyer_id", to_char(max(join_date),'yyyy-mm-dd') "join_date", count(case when to_number(to_char(order_date,'yyyy')) = 2019 then order_id else null end) "orders_in_2019" from Users a,Orders b where a.user_id = b. buyer_id group by buyer_id order by 1
第10天 过滤
🚩182.查找重复的电子邮箱
🚀 编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。 示例： +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ 根据以上输入，你的查询应返回以下结果： +---------+ | Email | +---------+ | a@b.com | +---------+ 说明：所有电子邮箱都是小写字母。 🐴🐴 答案 # Write your MySQL query statement below select Email from Person group by Email having count(*)>1 /* Write your T-SQL query statement below */ select distinct(p1.Email) Email from Person p1 join Person p2 on p1.Email = p2.Email AND p1.Id!=p2.Id /* Write your PL/SQL query statement below */ select Email "Email" from( select Email,count(Email) as num from Person group by Email ) where num>1
🚩1050. 合作过至少三次的演员和导演
🚀 ActorDirector 表： +-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp 是这张表的主键. 🚀 需求 写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id) 示例： ActorDirector 表： +-------------+-------------+-------------+ | actor_id | director_id | timestamp | +-------------+-------------+-------------+ | 1 | 1 | 0 | | 1 | 1 | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 1 | 2 | 4 | | 2 | 1 | 5 | | 2 | 1 | 6 | +-------------+-------------+-------------+ Result 表： +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ 唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。 🐴🐴 答案 # Write your MySQL query statement below select actor_id,director_id from ActorDirector group by actor_id,director_id having count(timestamp)>=3 /* Write your T-SQL query statement below */ select actor_id,director_id from ActorDirector group by actor_id,director_id having count(timestamp)>=3 /* Write your PL/SQL query statement below */ select actor_id "actor_id", director_id "director_id" from ActorDirector group by actor_id,director_id having count(*) > =3
🚩1587.银行账户概要 II
🚀 表: Users +--------------+---------+ | Column Name | Type | +--------------+---------+ | account | int | | name | varchar | +--------------+---------+ account 是该表的主键. 表中的每一行包含银行里中每一个用户的账号. 🚀 需求 表: Transactions +---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | account | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id 是该表主键. 该表的每一行包含了所有账户的交易改变情况. 如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的. 所有账户的起始余额为 0. 写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和. 返回结果表单没有顺序要求. 查询结果格式如下例所示. Users table: +------------+--------------+ | account | name | +------------+--------------+ | 900001 | Alice | | 900002 | Bob | | 900003 | Charlie | +------------+--------------+ Transactions table: +------------+------------+------------+---------------+ | trans_id | account | amount | transacted_on | +------------+------------+------------+---------------+ | 1 | 900001 | 7000 | 2020-08-01 | | 2 | 900001 | 7000 | 2020-09-01 | | 3 | 900001 | -3000 | 2020-09-02 | | 4 | 900002 | 1000 | 2020-09-12 | | 5 | 900003 | 6000 | 2020-08-07 | | 6 | 900003 | 6000 | 2020-09-07 | | 7 | 900003 | -4000 | 2020-09-11 | +------------+------------+------------+---------------+ Result table: +------------+------------+ | name | balance | +------------+------------+ | Alice | 11000 | +------------+------------+ Alice 的余额为(7000 + 7000 - 3000) = 11000. Bob 的余额为1000. Charlie 的余额为(6000 + 6000 - 4000) = 8000. 🐴🐴 答案 # Write your MySQL query statement below WITH t AS ( SELECT account, SUM(amount) balance FROM Transactions GROUP BY account HAVING SUM(amount)>10000 ) SELECT name, balance FROM t JOIN Users USING(account) /* Write your T-SQL query statement below */ select name, sum(isnull(b.amount, 0)) balance from Users a left join Transactions b on a.account = b. account group by name having sum(isnull(b.amount, 0))>10000 /* Write your PL/SQL query statement below */ select name, sum(nvl(b.amount, 0)) balance from Users a left join Transactions b on a.account = b. account group by name having sum(nvl(b.amount, 0))>10000
🚩1084. 销售分析III
🚀 Table: Product +--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ Product_id是该表的主键。 该表的每一行显示每个产品的名称和价格。 Table: Sales +-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+ 这个表没有主键，它可以有重复的行。 product_id 是 Product 表的外键。 该表的每一行包含关于一个销售的一些信息。 🚀 需求 编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。 以任意顺序 返回结果表。 查询结果格式如下所示。 示例 1: 输入： Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+ Sales table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+ 输出： +-------------+--------------+ | product_id | product_name | +-------------+--------------+ | 1 | S8 | +-------------+--------------+ 解释: id为1的产品仅在2019年春季销售。 id为2的产品在2019年春季销售，但也在2019年春季之后销售。 id为3的产品在2019年春季之后销售。 我们只返回产品1，因为它是2019年春季才销售的产品。 🐴🐴 答案 # Write your MySQL query statement below select p.product_id, p.product_name from Product p, Sales s where p.product_id = s.product_id group by p.product_id, p.product_name having(sum(sale_date between '2019-01-01' and '2019-03-31') = count(*)) /* Write your T-SQL query statement below */ SELECT s.product_id , product_name FROM Sales s JOIN Product p ON s.product_id = p.product_id GROUP BY s.product_id,product_name HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31' /* Write your PL/SQL query statement below */ SELECT s.product_id "product_id", product_name "product_name" FROM Sales s JOIN Product p ON s.product_id = p.product_id GROUP BY s.product_id,product_name HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'
