网站导航
> 文档中心 > LeetCode刷题100道，让你滚瓜烂熟拿下SQL

LeetCode刷题100道，让你滚瓜烂熟拿下SQL

网友: 文档中心 2022-05-16 07:37:48

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

  • 前言
  • 1.SQL 入门
    • 595.大的国家
    • 1757. 可回收且低脂的产品
    • 584. 寻找用户推荐人
    • 183. 从不订购的客户
  • 2 排序 & 修改
    • 1873. 计算特殊奖金
    • 627. 变更性别
    • 196. 删除重复的电子邮箱
  • 精选数据库70题
    • 511. 游戏玩法分析 I
    • 586. 订单最多的客户
    • 607. 销售员
    • 608. 树节点
    • 1050. 合作过至少三次的演员和导演
    • 1084. 销售分析III
    • 1141.查询近30天活跃用户数
    • 1148.文章浏览I
    • 1158.市场分析 I
    • 175. 组合两个表
    • 176. 第二高的薪水

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

1.SQL 入门

595.大的国家

🚀 World 表：+-------------+---------+| Column Name | Type    |+-------------+---------+| name | varchar || continent   | varchar || area | int     || population  | int     || gdp  | int     |+-------------+---------+name 是这张表的主键。这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。🚀 需求如果一个国家满足下述两个条件之一，则认为该国是大国 ：面积至少为 300 平方公里（即，3000000 km2），或者人口至少为 2500 万（即 25000000）编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。按 任意顺序 返回结果表。查询结果格式如下例所示。🚀 示例：输入：World 表：+-------------+-----------+---------+------------+--------------+| name | continent | area    | population | gdp   |+-------------+-----------+---------+------------+--------------+| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  || Albania     | Europe    | 28748   | 2831741    | 12960000000  || Algeria     | Africa    | 2381741 | 37100000   | 188681000000 || Andorra     | Europe    | 468     | 78115      | 3712000000   || Angola      | Africa    | 1246700 | 20609294   | 100990000000 |+-------------+-----------+---------+------------+--------------+输出：+-------------+------------+---------+| name | population | area    |+-------------+------------+---------+| Afghanistan | 25500100   | 652230  || Algeria     | 37100000   | 2381741 |+-------------+------------+---------+🐴🐴 答案# Write your MySQL query statement belowselect name,population,area from Worldwhere area>=3000000or population >=25000000/* Write your T-SQL query statement below */select name,population,area from Worldwhere area>=3000000or population >=25000000/* Write your PL/SQL query statement below */select name "name",population "population",area "area"from Worldwhere area>=3000000or population >=25000000

1757. 可回收且低脂的产品

🚀 表：Products+-------------+---------+| Column Name | Type    |+-------------+---------+| product_id  | int     || low_fats    | enum    || recyclable  | enum    |+-------------+---------+product_id 是这个表的主键。low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。🚀 需求写出 SQL 语句，查找既是低脂又是可回收的产品编号。返回结果 无顺序要求 。查询结果格式如下例所示：Products 表：+-------------+----------+------------+| product_id  | low_fats | recyclable |+-------------+----------+------------+| 0    | Y | N   || 1    | Y | Y   || 2    | N | Y   || 3    | Y | Y   || 4    | N | N   |+-------------+----------+------------+Result 表：+-------------+| product_id  |+-------------+| 1    || 3    |+-------------+只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。🐴🐴 答案# Write your MySQL query statement belowselect product_id  from Productswhere low_fats = 'Y'and recyclable ='Y'/* Write your T-SQL query statement below */select product_id  from Productswhere low_fats = 'Y'and recyclable ='Y'/* Write your PL/SQL query statement below */select product_id "product_id" from Productswhere low_fats = 'Y'and recyclable ='Y'

584. 寻找用户推荐人

🚀 给定表 customer ，里面保存了所有客户信息和他们的推荐人。+------+------+-----------+| id   | name | referee_id|+------+------+-----------+|    1 | Will |      NULL ||    2 | Jane |      NULL ||    3 | Alex |  2 ||    4 | Bill |      NULL ||    5 | Zack |  1 ||    6 | Mark |  2 |+------+------+-----------+🚀 需求写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是2。对于上面的示例数据，结果为：+------+| name |+------+| Will || Jane || Bill || Zack |+------+🐴🐴 答案# Write your MySQL query statement belowselect name  from customerwhere IFNULL(referee_id,0)  2--mysql判断非空的函数ISNULL(expr)如果expr为null返回值1，否则返回值为0IFNULL(expr1,expr2)如果expr1值为null返回expr2的值，否则返回expr1的值/* Write your T-SQL query statement below */select name  from customerwhere referee_id  2 OR referee_id IS NULL/* Write your PL/SQL query statement below */select name "name"  from customerwhere nvl(referee_id,0)  2

183. 从不订购的客户

🚀 某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。Customers 表：+----+-------+| Id | Name  |+----+-------+| 1  | Joe   || 2  | Henry || 3  | Sam   || 4  | Max   |+----+-------+Orders 表：+----+------------+| Id | CustomerId |+----+------------+| 1  | 3   || 2  | 1   |+----+------------+🚀 需求例如给定上述表格，你的查询应返回：+-----------+| Customers |+-----------+| Henry     || Max|+-----------+🐴🐴 答案# Write your MySQL query statement belowselect Name "Customers" from Customerswhere id not in (select CustomerId  from Orders)/* Write your T-SQL query statement below */select Name "Customers" from Customerswhere id not in (select CustomerId  from Orders)/* Write your PL/SQL query statement below */select Name "Customers" from Customers awhere not exists (select 1  from Orders b where a.Id = b.CustomerId)order by 1

2 排序 & 修改

1873. 计算特殊奖金

🚀 表: Employees+-------------+---------+| 列名 | 类型     |+-------------+---------+| employee_id | int     || name | varchar || salary      | int     |+-------------+---------+employee_id 是这个表的主键。此表的每一行给出了雇员id ，名字和薪水。 🚀 需求写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。Return the result table ordered by employee_id.返回的结果集请按照employee_id排序。查询结果格式如下面的例子所示。 示例 1:输入：Employees 表:+-------------+---------+--------+| employee_id | name    | salary |+-------------+---------+--------+| 2    | Meir    | 3000   || 3    | Michael | 3800   || 7    | Addilyn | 7400   || 8    | Juan    | 6100   || 9    | Kannon  | 7700   |+-------------+---------+--------+输出：+-------------+-------+| employee_id | bonus |+-------------+-------+| 2    | 0     || 3    | 0     || 7    | 7400  || 8    | 0     || 9    | 7700  |+-------------+-------+解释：因为雇员id是偶数，所以雇员id 是2和8的两个雇员得到的奖金是0。雇员id为3的因为他的名字以'M'开头，所以，奖金是0。其他的雇员得到了百分之百的奖金。🐴🐴 答案# Write your MySQL query statement belowselect employee_id,case when mod(employee_id,2)=1 and LEFT(name,1)!='M' then salaryelse 0 end bonusfrom Employeesorder by employee_id/* Write your T-SQL query statement below */select employee_id,case when employee_id%2=1 and SUBSTRING(name,1,1)!='M' then salaryelse 0 end bonusfrom Employeesorder by employee_id/* Write your PL/SQL query statement below */select employee_id "employee_id",case when mod(employee_id,2)=1 and substr(name,1,1)!='M' then salaryelse 0 end  "bonus"from Employeesorder by 1

627. 变更性别

🚀 Salary 表：+-------------+----------+| Column Name | Type     |+-------------+----------+| id   | int      || name | varchar  || sex  | ENUM     || salary      | int      |+-------------+----------+id 是这个表的主键。sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。本表包含公司雇员的信息。🚀 需求请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。查询结果如下例所示。示例 1:输入：Salary 表：+----+------+-----+--------+| id | name | sex | salary |+----+------+-----+--------+| 1  | A    | m   | 2500   || 2  | B    | f   | 1500   || 3  | C    | m   | 5500   || 4  | D    | f   | 500    |+----+------+-----+--------+输出：+----+------+-----+--------+| id | name | sex | salary |+----+------+-----+--------+| 1  | A    | f   | 2500   || 2  | B    | m   | 1500   || 3  | C    | f   | 5500   || 4  | D    | m   | 500    |+----+------+-----+--------+解释：(1, A) 和 (3, C) 从 'm' 变为 'f' 。(2, B) 和 (4, D) 从 'f' 变为 'm' 。🐴🐴 答案# Write your MySQL query statement belowupdate Salary set sex=case sex when 'm' then 'f'when 'f' then 'm'end/* Write your T-SQL query statement below */update Salary set sex=case sex when 'm' then 'f'when 'f' then 'm'end/* Write your PL/SQL query statement below */update Salary set sex=decode(sex,'m','f','f','m')

196. 删除重复的电子邮箱

🚀 表: Person+-------------+---------+| Column Name | Type    |+-------------+---------+| id   | int     || email| varchar |+-------------+---------+id是该表的主键列。该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。🚀 需求编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。以任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询）查询结果格式如下所示。示例 1:输入: Person 表:+----+------------------+| id | email     |+----+------------------+| 1  | john@example.com || 2  | bob@example.com  || 3  | john@example.com |+----+------------------+输出: +----+------------------+| id | email     |+----+------------------+| 1  | john@example.com || 2  | bob@example.com  |+----+------------------+解释: john@example.com重复两次。我们保留最小的Id = 1。🐴🐴 答案# Please write a DELETE statement and DO NOT write a SELECT statement.# Write your MySQL query statement belowDELETE p1 FROM Person p1, Person p2WHERE p1.email = p2.email AND p1.id > p2.id/*  Please write a DELETE statement and DO NOT write a SELECT statement. Write your T-SQL query statement below */DELETE p1 FROM Person p1, Person p2WHERE p1.email = p2.email AND p1.id > p2.id/* Please write a DELETE statement and DO NOT write a SELECT statement. Write your PL/SQL query statement below */delete from Person where id in (select e1.id from Person e1,Person e2 where e1.email = e2.email and e1.id>e2.id)delete from Person e1 where exists (select 1 from Person e2 where e1.email = e2.email and e1.id>e2.id)

精选数据库70题

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 belowselect player_id,min(event_date) first_login  from Activitygroup by player_id/* Write your T-SQL query statement below */select player_id,min(event_date) first_login  from Activitygroup 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) dkfrom Activity) where dk =1order by 1

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 belowselect customer_number from Ordersgroup by customer_numberorder by count(*) desc limit 1/* Write your T-SQL query statement below */select top 1 customer_number from Ordersgroup by customer_numberorder by count(*) desc /* Write your PL/SQL query statement below */select * from (select customer_number "customer_number" from Ordersgroup by customer_numberorder by count(*) desc) where rownum =1

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 belowselect a.name from SalesPerson a where not exists (select n.name from Orders m,Company nwhere m.com_id  = n.com_idand 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 nwhere m.com_id  = n.com_idand 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 nwhere m.com_id  = n.com_idand n.name = 'RED'and m.sales_id = a.sales_id)

608. 树节点

🚀 给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。+----+------+| id | p_id |+----+------+| 1  | null || 2  | 1    || 3  | 1    || 4  | 2    || 5  | 2    |+----+------+树中每个节点属于以下三种类型之一：叶子：如果这个节点没有任何孩子节点。根：如果这个节点是整棵树的根，即没有父节点。内部节点：如果这个节点既不是叶子节点也不是根节点。🚀 需求写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：+----+------+| id | Type |+----+------+| 1  | Root || 2  | Inner|| 3  | Leaf || 4  | Leaf || 5  | Leaf |+----+------+解释节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。样例中树的形态如下：  1/   \ 23      /   \    45注意如果树中只有一个节点，你只需要输出它的根属性。🐴🐴 答案# Write your MySQL query statement belowselect id,case when p_id is null then 'Root'when id in (select p_id from tree) then 'Inner'else 'Leaf' end as Typefrom tree/* Write your T-SQL query statement below */select id,case when p_id is null then 'Root'when id in (select p_id from tree) then 'Inner'else 'Leaf' end as Typefrom tree/* Write your PL/SQL query statement below */select id "id",case when p_id is null then 'Root'when id in (select p_id from tree) then 'Inner'else 'Leaf' end as "Type"from tree

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 belowselect actor_id,director_idfrom ActorDirectorgroup by actor_id,director_idhaving count(timestamp)>=3/* Write your T-SQL query statement below */select actor_id,director_idfrom ActorDirectorgroup by actor_id,director_idhaving count(timestamp)>=3/* Write your PL/SQL query statement below */select actor_id "actor_id",director_id "director_id"from ActorDirectorgroup by actor_id,director_idhaving count(*) > =3

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 belowselect p.product_id, p.product_namefrom Product p, Sales swhere p.product_id = s.product_idgroup by p.product_id, p.product_namehaving(sum(sale_date between '2019-01-01' and '2019-03-31') = count(*))/* Write your T-SQL query statement below */SELECT s.product_id , product_nameFROM Sales s JOIN Product pON s.product_id = p.product_idGROUP BY s.product_id,product_nameHAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) = '2019-01-01' AND MAX(sale_date) <= '2019-03-31'

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 belowselect date_sub(str_to_date('2019-07-27', '%Y-%m-%d')select datediff('2019-07-27', activity_date)select activity_date day, count(distinct user_id) active_usersfrom activitywhere activity_date > date_sub('2019-07-27', interval 30 day)and activity_date  CONVERT(varchar(100), dateadd(dd,-30,'2019-07-27'), 23)and activity_date to_date('20190727','yyyymmdd')-30and to_char(activity_date,'yyyy-mm-dd') < '2019-07-27'group by to_char(activity_date,'yyyy-mm-dd')

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 belowselect distinct author_id idfrom Viewswhere author_id = viewer_idorder by id/* Write your T-SQL query statement below */select distinct author_id idfrom Viewswhere author_id = viewer_idorder by id/* Write your PL/SQL query statement below */select distinct author_id "id"from Viewswhere author_id = viewer_idorder by 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 belowSELECT user_id AS buyer_id, join_date, IFNULL(Buy.cnt, 0) AS orders_in_2019FROM UsersLEFT JOIN (    SELECT buyer_id, COUNT(order_id) AS cnt     FROM Orders    WHERE Year(order_date)='2019'    GROUP BY buyer_id) AS BuyON 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_2019FROM UsersLEFT JOIN (    SELECT buyer_id, COUNT(order_id) AS cnt     FROM Orders    WHERE Year(order_date)='2019'    GROUP BY buyer_id)  BuyON 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_idelse null end) "orders_in_2019"from Users a,Orders bwhere a.user_id = b. buyer_idgroup by buyer_idorder by 1

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 belowselect a.firstName,a.lastName,b.city,b.statefrom Person a left join Address bon a.PersonId = b.PersonId/* Write your T-SQL query statement below */select a.firstName,a.lastName,b.city,b.statefrom Person a left join Address bon 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 bwhere a.PersonId = b.PersonId(+)order by 1

176. 第二高的薪水

🚀 Employee 表：+-------------+------+| Column Name | Type |+-------------+------+| id   | int  || salary      | int  |+-------------+------+id 是这个表的主键。表的每一行包含员工的工资信息。🚀 需求编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。查询结果如下例所示。示例 1：输入：Employee 表：+----+--------+| id | salary |+----+--------+| 1  | 100    || 2  | 200    || 3  | 300    |+----+--------+输出：+---------------------+| SecondHighestSalary |+---------------------+| 200   |+---------------------+示例 2：输入：Employee 表：+----+--------+| id | salary |+----+--------+| 1  | 100    |+----+--------+输出：+---------------------+| SecondHighestSalary |+---------------------+| null  |+---------------------+🐴🐴 答案# Write your MySQL query statement belowSELECT    IFNULL(      (SELECT DISTINCT SalaryFROM EmployeeORDER BY Salary DESC LIMIT 1 OFFSET 1),    NULL) AS SecondHighestSalary/* Write your T-SQL query statement below */SELECT MAX(Salary) SecondHighestSalary FROM EmployeeWhere Salary  E1.Salary);

体系化学习SQL，请关注CSDN博客
https://blog.csdn.net/weixin_41645135/category_11653817.html

在这里插入图片描述

网络标签：

相关问题