626.换座位
表:
Seat
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | student | varchar | +-------------+---------+id
是该表的主键(唯一值)列。 该表的每一行都表示学生的姓名和 ID。 id 是一个连续的增量。编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数,则最后一个学生的id不交换。
按
id
升序 返回结果表。查询结果格式如下所示。
示例 1:
输入: Seat 表: +----+---------+ | id | student | +----+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +----+---------+ 输出: +----+---------+ | id | student | +----+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +----+---------+ 解释: 请注意,如果学生人数为奇数,则不需要更换最后一名学生的座位。
思路,更换名字也可以从id出发, 如果id是偶数那就减一 也就是前进一位,如果是奇数并且不是最后一个那就让id +1 最后排序一下即可
select
(case when id % 2 = 0 then id-1
when id % 2 = 1 and id <(select count(id) from Seat) then id +1
else id end) as id ,student from seat order by id
627.变更性别
Salary
表:+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | name | varchar | | sex | ENUM | | salary | int | +-------------+----------+ id 是这个表的主键(具有唯一值的列)。 sex 这一列的值是 ENUM 类型,只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。请你编写一个解决方案来交换所有的
'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' 。
简单题
update salary set sex = if(sex='f','m','f')
1045.买下所有产品的客户
Customer
表:+-------------+---------+ | Column Name | Type | +-------------+---------+ | customer_id | int | | product_key | int | +-------------+---------+ 该表可能包含重复的行。 customer_id 不为 NULL。 product_key 是 Product 表的外键(reference 列)。
Product
表:+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_key | int | +-------------+---------+ product_key 是这张表的主键(具有唯一值的列)。编写解决方案,报告
Customer
表中购买了Product
表中所有产品的客户的 id。返回结果表 无顺序要求 。
返回结果格式如下所示。
示例 1:
输入: Customer 表: +-------------+-------------+ | customer_id | product_key | +-------------+-------------+ | 1 | 5 | | 2 | 6 | | 3 | 5 | | 3 | 6 | | 1 | 6 | +-------------+-------------+ Product 表: +-------------+ | product_key | +-------------+ | 5 | | 6 | +-------------+ 输出: +-------------+ | customer_id | +-------------+ | 1 | | 3 | +-------------+ 解释: 购买了所有产品(5 和 6)的客户的 id 是 1 和 3 。
SELECT
t1.customer_id
FROM
Customer AS t1
INNER JOIN Product AS t2 ON t1.product_key = t2.product_key
GROUP BY t1.customer_id
having count(distinct t1.product_key) = (select count(*) from product)
1050.合作过至少三次的演员和导演
ActorDirector
表:+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp 是这张表的主键(具有唯一值的列).编写解决方案找出合作过至少三次的演员和导演的 id 对
(actor_id, director_id)
示例 1:
输入: 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 | +-------------+-------------+-------------+ 输出: +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ 解释: 唯一的 id 对是 (1, 1),他们恰好合作了 3 次。
select actor_id,director_id
from actordirector
group by actor_id,director_id having count(*)>=3
1068.产品销售分析|
销售表
Sales
:+-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, year) 是销售表 Sales 的主键(具有唯一值的列的组合)。 product_id 是关联到产品表 Product 的外键(reference 列)。 该表的每一行显示 product_id 在某一年的销售情况。 注意: price 表示每单位价格。产品表
Product
:+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id 是表的主键(具有唯一值的列)。 该表的每一行表示每种产品的产品名称。编写解决方案,以获取
Sales
表中所有sale_id
对应的product_name
以及该产品的所有year
和price
。返回结果表 无顺序要求 。
结果格式示例如下。
示例 1:
输入: Sales
表: +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | +---------+------------+------+----------+-------+ | 1 | 100 | 2008 | 10 | 5000 | | 2 | 100 | 2009 | 12 | 5000 | | 7 | 200 | 2011 | 15 | 9000 | +---------+------------+------+----------+-------+ Product 表: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | +------------+--------------+ 输出: +--------------+-------+-------+ | product_name | year | price | +--------------+-------+-------+ | Nokia | 2008 | 5000 | | Nokia | 2009 | 5000 | | Apple | 2011 | 9000 | +--------------+-------+-------+
select p.product_name,s.year,s.price
from sales s
left join product p on p.product_id = s.product_id
1070.产品销售分析 三
销售表
Sales
:+-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, year) 是这张表的主键(具有唯一值的列的组合)。 product_id 是产品表的外键(reference 列)。 这张表的每一行都表示:编号 product_id 的产品在某一年的销售额。 请注意,价格是按每单位计的。产品表
Product
:+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id 是这张表的主键(具有唯一值的列)。 这张表的每一行都标识:每个产品的 id 和 产品名称。编写解决方案,选出每个售出过的产品 第一年 销售的 产品 id、年份、数量 和 价格。
结果表中的条目可以按 任意顺序 排列。
结果格式如下例所示:
示例 1:
输入: Sales 表: +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | +---------+------------+------+----------+-------+ | 1 | 100 | 2008 | 10 | 5000 | | 2 | 100 | 2009 | 12 | 5000 | | 7 | 200 | 2011 | 15 | 9000 | +---------+------------+------+----------+-------+ Product 表: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | +------------+--------------+ 输出: +------------+------------+----------+-------+ | product_id | first_year | quantity | price | +------------+------------+----------+-------+ | 100 | 2008 | 10 | 5000 | | 200 | 2011 | 15 | 9000 | +------------+------------+----------+-------+
select product_id,year as first_year,quantity,price from sales
where (product_id,year) in (select product_id,min(year) from sales
group by product_id)
1075.项目员工 一
项目表
Project
:+-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ 主键为 (project_id, employee_id)。 employee_id 是员工表Employee 表的外键。
这张表的每一行表示 employee_id 的员工正在 project_id 的项目上工作。员工表
Employee
:+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ 主键是 employee_id。数据保证 experience_years 非空。 这张表的每一行包含一个员工的信息。请写一个 SQL 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
以 任意 顺序返回结果表。
查询结果的格式如下。
示例 1:
输入: Project 表: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee 表: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+---------------+ | project_id | average_years | +-------------+---------------+ | 1 | 2.00 | | 2 | 2.50 | +-------------+---------------+ 解释:第一个项目中,员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00;第二个项目中,员工的平均工作年限是 (3 + 2) / 2 = 2.50
select p.project_id,
round(sum(e.experience_years)/count(p.project_id),2) as average_years
from project p left join employee e on
p.employee_id = e.employee_id
group by p.project_id
1084.销售分析三
表:
Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ product_id 是该表的主键(具有唯一值的列)。 该表的每一行显示每个产品的名称和价格。表:
Sales
+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+ 这个表可能有重复的行。 product_id 是 Product 表的外键(reference 列)。 该表的每一行包含关于一个销售的一些信息。编写解决方案,报告
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 年春季之后销售。 我们只返回 id 为 1 的产品,因为它是 2019 年春季才销售的产品。
select sales.product_id as product_id, product.product_name as product_name
from sales left join product on sales.product_id = product.product_id
group by product_id
having count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(*)
1141.查询近30天活跃用户数
表:
Activity
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | session_id | int | | activity_date | date | | activity_type | enum | +---------------+---------+ 该表没有包含重复数据。 activity_type 列是 ENUM(category) 类型, 从 ('open_session', 'end_session', 'scroll_down', 'send_message') 取值。 该表记录社交媒体网站的用户活动。 注意,每个会话只属于一个用户。编写解决方案,统计截至
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 as day,
count(distinct user_id) as active_users from activity
where activity_date between '2019-06-28' and '2019-07-27'
group by activity_date
1148.文章浏览|
Views
表:+---------------+---------+ | Column Name | Type | +---------------+---------+ | article_id | int | | author_id | int | | viewer_id | int | | view_date | date | +---------------+---------+ 此表可能会存在重复行。(换句话说,在 SQL 中这个表没有主键) 此表的每一行都表示某人在某天浏览了某位作者的某篇文章。 请注意,同一人的 author_id 和 viewer_id 是相同的。请查询出所有浏览过自己文章的作者
结果按照
id
升序排列。查询结果的格式如下所示:
示例 1:
输入: 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 | +------+
select distinct author_id as id
from views
where author_id = viewer_id
order by author_id
1158.市场分析|
表:
Users
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ user_id 是此表主键(具有唯一值的列)。 表中描述了购物网站的用户信息,用户可以在此网站上进行商品买卖。表:
Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ order_id 是此表主键(具有唯一值的列)。 item_id 是 Items 表的外键(reference 列)。 (buyer_id,seller_id)是 User 表的外键。表:
Items
+---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ item_id 是此表的主键(具有唯一值的列)。编写解决方案找出每个用户的注册日期和在
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 | +-----------+------------+----------------+
select user_id as buyer_id ,join_date,
count(if(year(order_date)=2019,1,null)) as orders_in_2019
from Users left join orders on users.user_id = orders.buyer_id
group by user_id