文章目录
哈喽！大家好，我是【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. 第二高的薪水
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 below select name,population,area from World where area>=3000000 or population >=25000000 select name,population,area from World where area>=3000000 or population >=25000000 select name "name", population "population", area "area" from World where area>=3000000 or population >=250000001757. 可回收且低脂的产品
表：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 below
select product_id from Products
where low_fats = 'Y'
and recyclable ='Y'
select product_id from Products
where low_fats = 'Y'
and recyclable ='Y'
select product_id "product_id" from Products
where 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 below select name from customer where IFNULL(referee_id,0) <> 2 --mysql判断非空的函数 ISNULL(expr) 如果expr为null返回值1，否则返回值为0 IFNULL(expr1,expr2) 如果expr1值为null返回expr2的值，否则返回expr1的值 select name from customer where referee_id <> 2 OR referee_id IS NULL select name "name" from customer where nvl(referee_id,0) <> 2183. 从不订购的客户
某网站包含两个表，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 below select Name "Customers" from Customers where id not in (select CustomerId from Orders) select Name "Customers" from Customers where id not in (select CustomerId from Orders) select Name "Customers" from Customers a where not exists (select 1 from Orders b where a.Id = b.CustomerId) order by 12 排序 & 修改 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 below select employee_id, case when mod(employee_id,2)=1 and LEFt(name,1)!='M' then salary else 0 end bonus from Employees order by employee_id select employee_id, case when employee_id%2=1 and SUBSTRINg(name,1,1)!='M' then salary else 0 end bonus from Employees order by employee_id select employee_id "employee_id", case when mod(employee_id,2)=1 and substr(name,1,1)!='M' then salary else 0 end "bonus" from Employees order by 1627. 变更性别
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 below
update Salary
set sex=
case sex
when 'm' then 'f'
when 'f' then 'm'
end
update Salary
set sex=
case sex
when 'm' then 'f'
when 'f' then 'm'
end
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 below DELETE p1 FROM Person p1, Person p2 WHERe p1.email = p2.email AND p1.id > p2.id DELETe p1 FROM Person p1, Person p2 WHERe p1.email = p2.email AND p1.id > p2.id 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 below select player_id,min(event_date) first_login from Activity group by player_id select player_id,min(event_date) first_login from Activity group by player_id 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 1586. 订单最多的客户
表: 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 select top 1 customer_number from Orders group by customer_number order by count(*) desc select * from ( select customer_number "customer_number" from Orders group by customer_number order by count(*) desc ) where rownum =1607. 销售员
表: 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) 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) 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)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
/
2 3
/
4 5
注意
如果树中只有一个节点，你只需要输出它的根属性。
答案
# Write your MySQL 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 Type
from tree
select 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
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 below select actor_id,director_id from ActorDirector group by actor_id,director_id having count(timestamp)>=3 select actor_id,director_id from ActorDirector group by actor_id,director_id having count(timestamp)>=3 select actor_id "actor_id", director_id "director_id" from ActorDirector group by actor_id,director_id having count(*) > =31084. 销售分析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(*)) 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' 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'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 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_users
from activity
where activity_date > date_sub('2019-07-27', interval 30 day)
and activity_date <= '2019-07-27'
group by activity_date
CONVERT(varchar(100), GETDATE(), 23) --获取当前日期格式，日期转字符串 2019-07-27
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
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')
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 select distinct author_id id from Views where author_id = viewer_id order by id select distinct author_id "id" from Views where author_id = viewer_id order by 11158.市场分析 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
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
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
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 select a.firstName, a.lastName, b.city, b.state from Person a left join Address b on a.PersonId = b.PersonId 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 1176. 第二高的薪水
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 below
SELECt
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
SELECt MAX(Salary) SecondHighestSalary FROM Employee
Where Salary <
(SELECt MAX(Salary) FROM Employee);
SELECt MAX(Salary) "SecondHighestSalary" FROM Employee E1
WHERe 1 =
(SELECt COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERe E2.Salary > E1.Salary);
体系化学习SQL，请关注CSDN博客
https://blog.csdn.net/weixin_41645135/category_11653817.html
