做穿越火线的网站资料,做360网站优化,福田附近网站开发公司,视频网站开发前景如何文章目录 175. 组合两个表181. 超过经理收入的员工182. 查找重复的电子邮箱COUNT(*)COUNT(*) 与 COUNT(column) 的区别 where和vaing之间的区别用法 183.从不订购的客户196.删除重复的电子邮箱197.上升的温度511.游戏玩法分析I512.游戏玩法分析II577.员工奖金584.寻找用户推荐人… 文章目录 175. 组合两个表181. 超过经理收入的员工182. 查找重复的电子邮箱COUNT(*)COUNT(*) 与 COUNT(column) 的区别 where和vaing之间的区别用法 183.从不订购的客户196.删除重复的电子邮箱197.上升的温度511.游戏玩法分析I512.游戏玩法分析II577.员工奖金584.寻找用户推荐人586.订单最多的客户595.大的国家596.超过5名学生的课603.连续空余座位(没做出来)607.销售员610.判断三角形613.直线上的最近距离619.只出现一次的最大数字(做出一半)620.有趣的电影627.变更性别1050.合作过至少三次的演员和导演1068.产品销售分析I1069.产品销售分析II1075.项目员工I1076.项目员工II(做出一半)1082.销售分析I1083.销售分析II1084.销售分析III 175. 组合两个表
表: Person
----------------------
| 列名 | 类型 |
----------------------
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
----------------------personId 是该表的主键具有唯一值的列。该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
----------------------
| 列名 | 类型 |
----------------------
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
----------------------addressId 是该表的主键具有唯一值的列。该表的每一行都包含一个 ID PersonId 的人的城市和州的信息。
**编写解决方案报告 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 的地址信息。
解答如下
select firstName, lastName,city, state from Person left join Address onPerson.personId Address.personId;主要考察的是left join的使用
181. 超过经理收入的员工
表Employee
----------------------
| Column Name | Type |
----------------------
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
----------------------id 是该表的主键具有唯一值的列。该表的每一行都表示雇员的ID、姓名、工资和经理的ID。编写解决方案找出收入比经理高的员工。以 任意顺序 返回结果表。结果格式如下所示。
示例 1:输入: Employee 表:
------------------------------
| id | name | salary | managerId |
------------------------------
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
------------------------------输出:
----------
| Employee |
----------
| Joe |
----------解释: Joe 是唯一挣得比经理多的雇员。
解答如下
select e1.name as Employee from Employee e1,Employee e2 where e1.ManagerId e2.idand e1.Salary e2.Salary;考察笛卡尔积的用法
SELECT * FROM Employee e1, Employee e2;生成的结果是 笛卡尔积也叫 交叉连接 或 Cartesian Join。这是两张表之间的所有组合行的集合。
182. 查找重复的电子邮箱
表: Person
----------------------
| Column Name | Type |
----------------------
| id | int |
| email | varchar |
----------------------id 是该表的主键具有唯一值的列。此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。 编写解决方案来报告所有重复的电子邮件。 请注意可以保证电子邮件字段不为 NULL。以 任意顺序 返回结果表。
结果格式如下例。
示例 1:输入: Person 表:
-------------
| id | email |
-------------
| 1 | ab.com |
| 2 | cd.com |
| 3 | ab.com |
-------------输出:
---------
| Email |
---------
| ab.com |
---------解释: ab.com 出现了两次。
解答如下
select email as Email from Person group by email having count(*) 1;考察对于count(*)和having的用法
COUNT(*)
COUNT(*) 会统计表中符合查询条件的所有行的数量包括 NULL 值的行。也就是说它会对查询结果中的每一行进行计数而不管这一行的具体列值是什么。
COUNT(*) 与 COUNT(column) 的区别
COUNT(*)计算所有行数不论列值是否为 NULL。 COUNT(column)只计算某一列中非 NULL 值的行数。NULL 值不会被计算在内。
where和vaing之间的区别用法
1. WHERE 与 HAVING 的区别 WHERE用于过滤原始数据行在 GROUP BY 之前执行也就是说WHERE 子句无法使用聚合函数如 COUNT()、SUM() 等。它只能应用在数据的原始列上不能用于过滤基于分组后的结果。 HAVINGfont color“red”用于过滤 GROUP BY 分组后的结果允许你基于聚合函数的结果进行筛选。HAVING 在 GROUP BY 之后执行因此你可以使用 COUNT() 这样的聚合函数。
183.从不订购的客户
Customers 表
----------------------
| Column Name | Type |
----------------------
| id | int |
| name | varchar |
----------------------在 SQL 中id 是该表的主键。该表的每一行都表示客户的 ID 和名称。 Orders 表
-------------------
| Column Name | Type |
-------------------
| id | int |
| customerId | int |
-------------------在 SQL 中id 是该表的主键。customerId 是 Customers 表中 ID 的外键( Pandas 中的连接键)。该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。找出所有从不点任何东西的顾客。以 任意顺序 返回结果表。结果格式如下所示。
示例 1输入Customers table:
-----------
| id | name |
-----------
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
-----------Orders table:
----------------
| id | customerId |
----------------
| 1 | 3 |
| 2 | 1 |
----------------输出
-----------
| Customers |
-----------
| Henry |
| Max |
-----------解答如下
select name as Customers from customers left join Orders on customers.id Orders.customerid where Orders.customerid is NULL;本题考察 对于空值的用法 在 MySQL 中要筛选某一列为空 (NULL) 的值可以使用 IS NULL 来进行筛选。
NULL 与空字符串或零不同NULL 表示没有值而空字符串 (‘’) 或零 (0) 是有效的值IS NULL 只会筛选出那些确实没有值的记录。 IS NULL 与 的区别在 MySQL 中NULL 是特殊的不能直接用 比较。因此不能使用 WHERE column_name NULL而是要使用 WHERE column_name IS NULL。
196.删除重复的电子邮箱
表: Person
----------------------
| Column Name | Type |
----------------------
| id | int |
| email | varchar |
----------------------id 是该表的主键列(具有唯一值的列)。该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。 编写解决方案 删除 所有重复的电子邮件只保留一个具有最小 id 的唯一电子邮件。 对于 SQL 用户请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。 对于 Pandas 用户请注意你应该直接修改 Person 表。 运行脚本后显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段然后再显示 Person 表。Person 表的最终顺序 无关紧要 。 返回结果格式如下示例所示。
示例 1:输入: Person 表:
----------------------
| id | email |
----------------------
| 1 | johnexample.com |
| 2 | bobexample.com |
| 3 | johnexample.com |
----------------------输出:
----------------------
| id | email |
----------------------
| 1 | johnexample.com |
| 2 | bobexample.com |
----------------------解释: johnexample.com重复两次。我们保留最小的Id 1。
解答如下
delete from Person where id not in (select * from (select min(id) from Person GROUP BY email) t);考察在delete中进行查询以及派生表的用法
在 DELETE 或 UPDATE 操作中你不能同时对一个表进行修改并在子查询中查询该表.换句话说你不能在 DELETE 语句中修改 Person 表同时在 FROM 子查询中查询 Person 表。
我们需要用一个额外的嵌套子查询也就是派生表来规避这个限制t 是派生表子查询结果的别名虽然这个别名在查询中没有进一步的使用但它是 MySQL 的一个要求每个子查询都必须有别名。通过使用派生表可以避免 MySQL 中同时修改和查询同一个表时产生的错误。
197.上升的温度
表 Weather
------------------------
| Column Name | Type |
------------------------
| id | int |
| recordDate | date |
| temperature | int |
------------------------id 是该表具有唯一值的列。没有具有相同 recordDate 的不同行。该表包含特定日期的温度信息 编写解决方案找出与之前昨天的日期相比温度更高的所有日期的 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
结果输出
select w1.id from weather w1,weather w2 where DATEDIFF(w1.recordDate,w2.recordDate) 1 and w1.temperature w2.temperature;考察笛卡尔积和DATADIFF函数用法
DATEDIFF() 是 MySQL 中的一个函数用于计算两个日期之间的差值以天数表示。
语法
DATEDIFF(date1, date2)date1 和 date2 是两个日期表达式。DATEDIFF 函数会计算 date1 - date2并返回相差的天数。结果为整数正数表示 date1 晚于 date2负数表示 date1 早于 date2。
示例 1.计算两个具体日期之间的差值
SELECT DATEDIFF(2024-09-17, 2024-09-10) AS days_diff;sqldays_diff
72.计算某个日期与当前日期之间的差值
SELECT DATEDIFF(CURDATE(), 2023-01-01) AS days_diff;假设当前日期为 2024-09-17则输出结果为
days_diff
6253.在表中使用 DATEDIFF 进行筛选 假设有一个表 orders其中有一列 order_date你可以查询所有距离当前日期超过 30 天的订单
SELECT *
FROM orders
WHERE DATEDIFF(CURDATE(), order_date) 30;注意 DATEDIFF() 返回结果只计算天数不考虑时间时、分、秒。如果想要精确到时间可以使用 TIMESTAMPDIFF() 函数。
511.游戏玩法分析I
活动表 Activity
-----------------------
| Column Name | Type |
-----------------------
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
-----------------------在 SQL 中表的主键是 (player_id, event_date)。这张表展示了一些游戏玩家在游戏平台上的行为活动。 每行数据记录了一名玩家在退出平台之前当天使用同一台设备登录平台后打开的游戏的数目可能是 0 个。
查询每位玩家 第一次登录平台的日期。查询结果的格式如下所示 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 |
------------------------结果如下
select player_id,min(event_date) as first_login from activity GROUP BY player_id;考察日期之间比较使用min或者max方法
512.游戏玩法分析II
able: Activity
-----------------------
| Column Name | Type |
-----------------------
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
-----------------------(player_id, event_date) 是这个表的两个主键(具有唯一值的列的组合) 这个表显示的是某些游戏玩家的游戏活动情况每一行是在某天使用某个设备登出之前登录并玩多个游戏可能为0的玩家的记录。请编写解决方案描述每一个玩家首次登陆的设备名称 返回结果格式如以下示例 示例 1输入Activity table:
------------------------------------------------
| 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 |
------------------------------------------------输出
----------------------
| player_id | device_id |
----------------------
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
----------------------结果如下
select t.player_id, activity.device_id from (select player_id,min(event_date) as early_date from activity GROUP BY player_id) as t left join activity on t.player_id activity.player_id and t.early_date activity.event_date ;考察子查询语句嵌套查询联合查询
别人做法联合查询
select player_id, device_id from activitywhere (player_id, event_date) in (select player_id, min(event_date) from activity group by player_id)577.员工奖金
表Employee
----------------------
| Column Name | Type |
----------------------
| empId | int |
| name | varchar |
| supervisor | int |
| salary | int |
----------------------empId 是该表中具有唯一值的列。 该表的每一行都表示员工的姓名和 id以及他们的工资和经理的 id。 表Bonus
-------------------
| Column Name | Type |
-------------------
| empId | int |
| bonus | int |
-------------------empId 是该表具有唯一值的列。empId 是 Employee 表中 empId 的外键(reference 列)。 该表的每一行都包含一个员工的 id 和他们各自的奖金。编写解决方案报告每个奖金 少于 1000 的员工的姓名和奖金数额。以 任意顺序 返回结果表。结果格式如下所示。
示例 1输入Employee table:
-----------------------------------
| empId | name | supervisor | salary |
-----------------------------------
| 3 | Brad | null | 4000 |
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 4 | Thomas | 3 | 4000 |
-----------------------------------Bonus table:
--------------
| empId | bonus |
--------------
| 2 | 500 |
| 4 | 2000 |
--------------输出
-------------
| name | bonus |
-------------
| Brad | null |
| John | null |
| Dan | 500 |
-------------结果如下
select name,bonus from employee e1 LEFT JOIN bonus on e1.empId bonus.empId where bonus is null or bonus 1000;考察表连接和null用法
584.寻找用户推荐人
表: Customer
----------------------
| Column Name | Type |
----------------------
| id | int |
| name | varchar |
| referee_id | int |
----------------------在 SQL 中id 是该表的主键列。该表的每一行表示一个客户的 id、姓名以及推荐他们的客户的 id。找出那些 没有被 id 2 的客户 推荐 的客户的姓名。以 任意顺序 返回结果表。结果格式如下所示。 示例 1输入 Customer 表:
----------------------
| id | name | referee_id |
----------------------
| 1 | Will | null |
| 2 | Jane | null |
| 3 | Alex | 2 |
| 4 | Bill | null |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
----------------------输出
------
| name |
------
| Will |
| Jane |
| Bill |
| Zack |
------结果如下
select name from customer where referee_id !2 or referee_id is null;这道题的考察点在于sql里面的不等于不包含null。
586.订单最多的客户
表: Orders
---------------------------
| Column Name | Type |
---------------------------
| order_number | int |
| customer_number | int |
---------------------------在 SQL 中Order_number是该表的主键。此表包含关于订单ID和客户ID的信息。查找下了 最多订单 的客户的 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 。 结果如下
select customer_number from (select customer_number,count(*) as count from orders GROUP BY customer_number ORDER BY count desc) as t LIMIT 1;595.大的国家
World 表
----------------------
| Column Name | Type |
----------------------
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | bigint |
----------------------name 是该表的主键具有唯一值的列。这张表的每一行提供国家名称、所属大陆、面积、人口和 GDP 值。 如果一个国家满足下述两个条件之一则认为该国是 大国 面积至少为 300 万平方公里即3000000 km2或者人口至少为 2500 万即 25000000编写解决方案找出 大国 的国家名称、人口和面积。 按 任意顺序 返回结果表。返回结果格式如下例所示。
示例输入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 |
----------------------------------结果如下
select name ,population, area
from World
where population 25000000 or area 3000000596.超过5名学生的课
表: Courses
----------------------
| Column Name | Type |
----------------------
| student | varchar |
| class | varchar |
----------------------(student, class)是该表的主键不同值的列的组合。该表的每一行表示学生的名字和他们注册的班级。 查询 至少有 5 个学生 的所有班级。以 任意顺序 返回结果表。结果格式如下所示。
示例 1:输入: Courses table:
-------------------
| student | class |
-------------------
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
-------------------输出:
---------
| class |
---------
| Math |
---------解释: -数学课有 6 个学生所以我们包括它。 -英语课有 1 名学生所以我们不包括它。 -生物课有 1 名学生所以我们不包括它。 -计算机课有 1 个学生所以我们不包括它。
结果如下
select class from courses GROUP BY class having count(*) 5;603.连续空余座位(没做出来)
表: Cinema
-------------------
| Column Name | Type |
-------------------
| seat_id | int |
| free | bool |
-------------------Seat_id 是该表的自动递增主键列。在 PostgreSQL 中free 存储为整数。请使用 ::boolean 将其转换为布尔格式。该表的每一行表示第 i 个座位是否空闲。1 表示空闲0 表示被占用。查找电影院所有连续可用的座位。 返回按 seat_id 升序排序 的结果表。测试用例的生成使得两个以上的座位连续可用。结果表格式如下所示。
示例 1:输入: Cinema 表:
---------------
| seat_id | free |
---------------
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
---------------输出:
---------
| seat_id |
---------
| 3 |
| 4 |
| 5 |
---------解题如下
select DISTINCT c1.seat_id from cinema c1,cinema c2 where abs(c2.seat_id - c1.seat_id) 1 ANDc1.free 1 and c2.free 1 ORDER BY c1.seat_id;考察笛卡尔积用法
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 的外键reference 列。 sales_id 是来自销售员表 sales_id 的外键reference 列。该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。 编写解决方案找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。以 任意顺序 返回结果表。返回结果格式如下所示。
结果如下
select name from SalesPerson where sales_id not in (select sales_id from Orders where com_id in (select com_id from company where name RED));610.判断三角形
表: Triangle
-------------------
| Column Name | Type |
-------------------
| x | int |
| y | int |
| z | int |
-------------------在 SQL 中(x, y, z)是该表的主键列。该表的每一行包含三个线段的长度。对每三个线段报告它们是否可以形成一个三角形。以 任意顺序 返回结果表。查询结果格式如下所示。
示例 1:输入: Triangle 表:
------------
| x | y | z |
------------
| 13 | 15 | 30 |
| 10 | 20 | 15 |
------------输出:
----------------------
| x | y | z | triangle |
----------------------
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |
----------------------结果如下
select x,y,z,CASE WHEN xy z and abs(x-y) z THEN YesELSE NOEND AS trianglefrom triangle;或者
select x,y,z, if(xyz and abs(x-y)z) as triangle from Triangle;考察点
不知道如何生成新的一列if或者CASE都可以
613.直线上的最近距离
表 Point
-------------------
| Column Name | Type |
-------------------
| x | int |
-------------------在SQL中x是该表的主键列。该表的每一行表示X轴上一个点的位置。找到 Point 表中任意两点之间的最短距离。返回结果格式如下例所示。
示例 1输入Point 表:
----
| x |
----
| -1 |
| 0 |
| 2 |
----输出
----------
| shortest |
----------
| 1 |
----------解释点 -1 和 0 之间的最短距离为 |(-1) - 0| 1。
结果如下
select abs(p1.x-p2.x) as shortest from point p1,point p2 where abs(p1.x-p2.x) 0 ORDER BY shortest limit 1;考察点大杀器笛卡尔积
619.只出现一次的最大数字(做出一半)
MyNumbers 表
-------------------
| Column Name | Type |
-------------------
| num | int |
-------------------该表可能包含重复项换句话说在SQL中该表没有主键。这张表的每一行都含有一个整数。 单一数字 是在 MyNumbers 表中只出现一次的数字。找出最大的 单一数字 。如果不存在 单一数字 则返回 null 。查询结果如下例所示。
示例 1输入MyNumbers 表
-----
| num |
-----
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
-----输出
-----
| num |
-----
| 6 |
-----解释单一数字有 1、4、5 和 6 。6 是最大的单一数字返回 6 。 示例 2输入MyNumbers table:
-----
| num |
-----
| 8 |
| 8 |
| 7 |
| 7 |
| 3 |
| 3 |
| 3 |
-----输出
------
| num |
------
| null |
------解释输入的表中不存在单一数字所以返回 null 。 结果如下
select (select num from mynumbers group by num having count(num) 1 order by num desc limit 1) num ;考察如何对NULL进行处理。利用选中空表返回null的特性
620.有趣的电影
表cinema
--------------------------
| Column Name | Type |
--------------------------
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |
--------------------------id 是该表的主键(具有唯一值的列)。每行包含有关电影名称、类型和评级的信息。评级为 [0,10] 范围内的小数点后 2 位浮点数。
编写解决方案找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。返回结果按 rating 降序排列。结果格式如下示例。
示例 1输入
---------------------------------------------
| id | movie | description | rating |
---------------------------------------------
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
---------------------------------------------输出
---------------------------------------------
| id | movie | description | rating |
---------------------------------------------
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
---------------------------------------------解释 我们有三部电影它们的 id 是奇数:1、3 和 5。id 3 的电影是 boring 的所以我们不把它包括在答案中。
结果如下
select * from cinema where description ! boring and id % 2 ! 0 ORDER BY rating desc;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 salaryset sex case when sexm then fwhen sexf then m
end考察点
UPDATE语句使用
UPDATE 语句的基本格式如下
UPDATE table_name
SET column1 value1, column2 value2, ...
WHERE condition;CASE语句用法
在 MySQL 中CASE 语句用于在 SELECT、UPDATE、INSERT、DELETE 语句中根据条件返回不同的结果。它有两种常用的语法形式简单 CASE 和 搜索 CASE。
1. 简单 CASE 语法
简单 CASE 语句根据表达式的值进行匹配。
语法
CASE expressionWHEN value1 THEN result1WHEN value2 THEN result2...ELSE default_result
ENDexpression 是要比较的值。WHEN 是条件语句比较 expression 和 value。THEN 后面是 WHEN 条件为真时返回的结果。ELSE 提供一个默认的返回值可选。END 是 CASE 语句的结束。
示例 假设有一张名为 students 的表包含 id 和 score 列。你想要根据 score 返回等级。
SELECT id, score, CASE scoreWHEN 90 THEN AWHEN 80 THEN BWHEN 70 THEN CELSE FEND AS grade
FROM students;这个查询将根据 score 列的值返回相应的等级。
2. 搜索 CASE 语法 搜索 CASE 语句基于布尔表达式进行条件判断。
CASE WHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE default_result
ENDWHEN 后面的 condition 是布尔表达式。THEN 后面是条件为真时返回的结果。ELSE 提供一个默认的返回值可选。END 是 CASE 语句的结束。
示例
假设有一个员工表 employees你想根据员工的工资水平来分类
SELECT id, name, salary,CASE WHEN salary 10000 THEN HighWHEN salary 5000 THEN MediumELSE LowEND AS salary_level
FROM employees;这个查询会根据员工的 salary 来分类为 ‘High’、‘Medium’ 或 ‘Low’。
3. 在 UPDATE 语句中使用 CASE 你也可以在 UPDATE 语句中使用 CASE根据条件更新不同的值。
示例 更新员工表根据不同的职位更新不同的工资
UPDATE employees
SET salary CASE WHEN position Manager THEN 8000WHEN position Developer THEN 6000ELSE 4000END;这条语句将根据员工的职位更新不同的工资。CASE 语句可以用于条件查询和更新。它有两种形式简单 CASE根据值进行匹配和 搜索 CASE根据条件进行匹配。CASE 语句可以在 SELECT、UPDATE 等多种 SQL 操作中使用。
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;考点group by用法
1068.产品销售分析I
销售表 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 product.product_name,sales.year,sales.price from sales LEFT JOIN product on sales.product_id product.product_id;1069.产品销售分析II
销售表Sales
--------------------
| Column Name | Type |
--------------------
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
--------------------sale_id 是这个表的主键具有唯一值的列。product_id 是 Product 表的外键reference 列。该表的每一行显示产品product_id在某一年的销售情况。请注意价格是每单位的。 产品表Product
-----------------------
| Column Name | Type |
-----------------------
| product_id | int |
| product_name | varchar |
-----------------------product_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 | total_quantity |
------------------------------
| 100 | 22 |
| 200 | 15 |
------------------------------结果如下
select sales.product_id,sum(quantity) as total_quantity from sales LEFT JOIN product on sales.product_id product.product_id GROUP BY product_id;1075.项目员工I
项目表 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 project_id,round(AVG(experience_years),2) as average_years from Project LEFT JOIN Employee on Project.employee_id employee.employee_id GROUP BY project_id;1076.项目员工II(做出一半)
表Project
----------------------
| Column Name | Type |
----------------------
| project_id | int |
| employee_id | int |
----------------------(project_id, employee_id) 是该表的主键(具有唯一值的列的组合)。employee_id 是该表的外键(reference 列)。 该表的每一行都表明 employee_id 的雇员正在处理 Project 表中 project_id 的项目。 表Employee
---------------------------
| Column Name | Type |
---------------------------
| employee_id | int |
| name | varchar |
| experience_years | int |
---------------------------employee_id 是该表的主键(具有唯一值的列)。该表的每一行都包含一名雇员的信息。 编写一个解决方案来报告所有拥有最多员工的 项目。以 任意顺序 返回结果表。返回结果格式如下所示。
示例 1输入Project table:
--------------------------
| project_id | employee_id |
--------------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
--------------------------Employee table:
---------------------------------------
| employee_id | name | experience_years |
---------------------------------------
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
---------------------------------------输出
-------------
| project_id |
-------------
| 1 |
-------------解释第一个项目有3名员工第二个项目有2名员工。
难点在于最多的项目重复值可能有多个
解答1如下考察公共表达式用法
with t as (
select project_id,count(*) as count from Project LEFT JOIN Employee on Project.employee_id employee.employee_id GROUP BY project_id ORDER BY count desc )select project_id from t where count (select max(count) from t);解释
CTE (WITH t AS)定义了一个公共表表达式 t其计算每个 project_id 的员工数量并使用 LEFT JOIN 来处理没有员工的项目。MAX(count) 子查询在外层查询中通过一个子查询计算出 t 中 count 列的最大值。外层查询外层查询将 count 等于最大值的 project_id 选择出来。
MySQL 处理方式
MySQL 会先处理 CTE t生成一个临时表包含每个 project_id 及其员工数量。然后通过子查询计算出 count 的最大值最终从 CTE t 中选择 count 等于最大值的项目。
注意点
WITH 语句从 MySQL 8.0 开始支持。如果有多个 project_id 的员工数相同且是最大值都会被选中。
解答如2考察ALL的用法
select project_id from Project GROUP BY project_id HAVING COUNT(project_id) ALL(select count(project_id) from project GROUP BY project_id);在 MySQL 中ALL 运算符用于比较某个表达式与子查询返回的所有值。
ALL 的典型用法是用于与 , , , , , ! 等比较运算符结合使用以确保某个值与子查询返回的每一个值进行比较。
ALL 的语法
expression comparison_operator ALL (subquery)expression要比较的值或表达式。comparison_operator比较运算符比如 , , , , 等。ALL表示与子查询返回的每一个值进行比较。subquery返回一组值的子查询。
ALL 的工作原理
如果所有的比较都返回 TRUE则最终返回 TRUE。如果任意一个比较返回 FALSE则最终返回 FALSE。
常见用法示例
查找大于子查询返回的所有值
假设有一个名为 employees 的表你想查找工资比所有部门平均工资都高的员工。你可以使用 ALL 结合子查询来完成。
SELECT employee_name, salary
FROM employees
WHERE salary ALL (SELECT AVG(salary)FROM employeesGROUP BY department_id
);子查询返回每个部门的平均工资。ALL 确保 salary 比所有部门的平均工资都高。
查找小于子查询返回的所有值
假设你有一个产品表 products你想找到价格比所有同类产品的最高价格都低的产品。
SELECT product_name, price
FROM products
WHERE price ALL (SELECT MAX(price)FROM productsGROUP BY category_id
);子查询返回每个类别中最高的价格。ALL 确保 price 小于所有类别中的最高价格。
等于所有子查询返回的值
你可以用 ALL 来确保一个值与子查询返回的每一个值相等这通常不常用更多情况下使用 IN。
SELECT employee_name
FROM employees
WHERE department_id ALL (SELECT department_idFROM departmentsWHERE location New York
);这将返回在每个 New York 地点的部门工作的员工但这种情况相对较少使用因为一般会用 IN 或者其他方式来完成。
1082.销售分析I
产品表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 列)。该表的每一行包含关于一个销售的一些信息。 编写解决方案找出总销售额最高的销售者如果有并列的就都展示出来。以 任意顺序 返回结果表。返回结果格式如下所示。
示例 1:输入Product 表
--------------------------------------
| product_id | product_name | unit_price |
--------------------------------------
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
--------------------------------------Sales 表
--------------------------------------------------------------
| 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 |
--------------------------------------------------------------输出
-------------
| seller_id |
-------------
| 1 |
| 3 |
-------------解释Id 为 1 和 3 的销售者销售总金额都为最高的 2800。
解答如下
with t as (select seller_id,sum(price) as sum_prices from Sales GROUP BY seller_id ORDER BY sum_prices desc)select seller_id from t where sum_prices (select max(sum_prices) from t);逐步建表进行分解得出答案。
另一种写法后期题目中如果考虑最低或者最高关键词出现的时候可以使用ALL
select seller_id from Sales GROUP BY seller_id having sum(price) ALL(select sum(price) from Sales GROUP BY seller_id);1083.销售分析II
表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 列)。buyer_id 永远不会是 NULL。sale_date 永远不会是 NULL。该表的每一行都包含一次销售的一些信息。
编写一个解决方案报告那些买了 S8 而没有买 iPhone 的 买家。注意S8 和 iPhone 是 Product 表中显示的产品。以 任意顺序 返回结果表。 结果格式如下所示。
示例 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 | 1 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 3 | 2019-05-13 | 2 | 2800 |
--------------------------------------------------------------输出
-------------
| buyer_id |
-------------
| 1 |
-------------解释 id 为 1 的买家购买了一部 S8但是却没有购买 iPhone而 id 为 3 的买家却同时购买了这 2 部手机。
解答如下
with t as (select s.buyer_id,p.product_name from product p LEFT JOIN Sales s on p.product_id s.product_id where p.product_name S8),t2 as (select s.buyer_id,p.product_name from product p LEFT JOIN Sales s on p.product_id s.product_id where p.product_name Iphone)select distinct t.buyer_id from t LEFT JOIN t2 on t.buyer_id t2.buyer_id where t2.buyer_id is null;很巧妙的做法
select s.buyer_idfrom product p, sales swhere p.product_id s.product_idgroup by s.buyer_idhaving sum(p.product_nameS8) 0 and sum(p.product_nameiphone) 1;1084.销售分析III
表 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 p.product_id as product_id,product_name from product p join sales son p.product_ids.product_idgroup by product_id,product_namehaving max(s.sale_date )2019-03-31 and min(s.sale_date) 2019-01-01;
