职业生涯规划大赛心得,黄冈网站优化公司哪家好,东营招标信息网官网首页,做wps的网站赚钱文章目录1. 排序2. 分页查询3. 聚合查询3.1 分组聚合 GROUP BY练习 LeetCode 176. 第二高的薪水练习 LeetCode 177. 第N高的薪水练习 LeetCode 182. 查找重复的电子邮箱练习 LeetCode 620. 有趣的电影练习 LeetCode 183. 从不订购的客户练习 LeetCode 596. 超过5名学生的课练习…
文章目录1. 排序2. 分页查询3. 聚合查询3.1 分组聚合 GROUP BY练习 LeetCode 176. 第二高的薪水练习 LeetCode 177. 第N高的薪水练习 LeetCode 182. 查找重复的电子邮箱练习 LeetCode 620. 有趣的电影练习 LeetCode 183. 从不订购的客户练习 LeetCode 596. 超过5名学生的课练习 LeetCode 586. 订单最多的客户练习 LeetCode 1082. 销售分析 I练习 LeetCode 1050. 合作过至少三次的演员和导演练习 LeetCode 1148. 文章浏览 I练习 LeetCode 511. 游戏玩法分析 I练习 LeetCode 1485. 按日期分组销售产品练习 LeetCode 1407. 排名靠前的旅行者学习自 廖雪峰的官方网站
1. 排序
SELECT查询时是根据主键排序
根据其他条件排序可以加上ORDER BY子句默认升序 ASC可省略
SELECT id, name, gender, score FROM students ORDER BY score;降序DESC
ORDER BY 列名 DESC # 降序排列SELECT id, name, gender, score FROM students ORDER BY score DESC;多条件排序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
# 先根据分数降序然后根据性别如果有WHERE子句那么ORDER BY子句要放到WHERE子句后面
SELECT id, name, gender, score
FROM students
WHERE class_id 1
ORDER BY score DESC;2. 分页查询
查询时如果结果集数据量很大分页显示
可以通过LIMIT M OFFSET N子句实现。每次显示最多 M 条从第 N 条记录开始算
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; # 每页3条记录从第0条开始OFFSET超过了查询的最大数量不会报错得到一个空集
OFFSET是可选的如果只写LIMIT 15 LIMIT 15 OFFSET 0
在MySQL中LIMIT 15 OFFSET 30 LIMIT 30, 15
使用LIMIT M OFFSET N分页时随着N越来越大查询效率也会越来越低
3. 聚合查询
SQL内置的COUNT()函数查询行数
SELECT COUNT(*) FROM students; # 返回一个二维表 一行一列共有10条记录 设置别名
SELECT COUNT(*) num FROM students;函数说明SUM计算某一列的合计值该列必须为数值类型AVG计算某一列的平均值该列必须为数值类型MAX计算某一列的最大值可以对字符串排序MIN计算某一列的最小值可以对字符串排序
SELECT AVG(score) average FROM students WHERE gender M;特别注意WHERE没有匹配到任何行COUNT()会返回0而SUM()、AVG()、MAX()和MIN()会返回 NULL
3.1 分组聚合 GROUP BY
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;使用多个列进行分组。例如统计各班的男女人数
SELECT class_id, gender, COUNT(*) num FROM students
GROUP BY class_id, gender;每个班级的平均分
SELECT class_id, AVG(score) avgsc FROM students
GROUP BY class_id ORDER BY avgsc DESC;每个班级男生和女生的平均分
SELECT class_id, gender, AVG(score) avgsc FROM students
GROUP BY class_id, gender ORDER BY avgsc DESC;练习 LeetCode 176. 第二高的薪水
题目
编写一个 SQL 查询获取 Employee 表中第二高的薪水Salary 。
------------
| Id | Salary |
------------
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
------------例如上述 Employee 表SQL查询应该返回 200 作为第二高的薪水。 如果不存在第二高的薪水那么查询应返回 null。
---------------------
| SecondHighestSalary |
---------------------
| 200 |
---------------------来源力扣LeetCode 链接https://leetcode-cn.com/problems/second-highest-salary 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
DISTINCT去重题目要求不能并列200,200的话第二高为NULL
# Write your MySQL query statement below
SELECT
(SELECT DISTINCT SalaryFROM Employee ORDER BY Salary DESCLIMIT 1 OFFSET 1
)
SecondHighestSalary194 ms
练习 LeetCode 177. 第N高的薪水
题目 编写一个 SQL 查询获取 Employee 表中第 n 高的薪水Salary。
------------
| Id | Salary |
------------
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
------------例如上述 Employee 表n 2 时应返回第二高的薪水 200。 如果不存在第 n 高的薪水那么查询应返回 null。
------------------------
| getNthHighestSalary(2) |
------------------------
| 200 |
------------------------来源力扣LeetCode 链接https://leetcode-cn.com/problems/nth-highest-salary 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
跟上题一样注意提前设置 N-1的值不支持 OFFSET N-1写法
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGINset n N-1;RETURN (# Write your MySQL query statement below.SELECT DISTINCT SalaryFROM Employee ORDER BY Salary DESCLIMIT 1 OFFSET n);
END246 ms
练习 LeetCode 182. 查找重复的电子邮箱
题目
编写一个 SQL 查询查找 Person 表中所有重复的电子邮箱。
Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values (1, ab.com)
insert into Person (Id, Email) values (2, cd.com)
insert into Person (Id, Email) values (3, ab.com)示例
-------------
| Id | Email |
-------------
| 1 | ab.com |
| 2 | cd.com |
| 3 | ab.com |
-------------
根据以上输入你的查询应返回以下结果---------
| Email |
---------
| ab.com |
---------
说明所有电子邮箱都是小写字母。来源力扣LeetCode 链接https://leetcode-cn.com/problems/duplicate-emails 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
使用 HAVING 关键字
# Write your MySQL query statement below
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)1;265 ms
练习 LeetCode 620. 有趣的电影
题目
某城市开了一家新的电影院吸引了很多人过来看电影。 该电影院特别注意用户体验专门有个 LED显示板做电影推荐上面公布着影评和相关电影描述。
作为该电影院的信息部主管您需要编写一个 SQL查询找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片结果请按等级 rating 排列。
例如下表 cinema:
---------------------------------------------
| 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 |
---------------------------------------------来源力扣LeetCode 链接https://leetcode-cn.com/problems/not-boring-movies 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
SELECT * FROM cinema
WHERE description ! boring AND id%2 1
ORDER BY rating DESC或者用 mod(id,2) 1
!也可以用
198 ms
练习 LeetCode 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 |
-----------来源力扣LeetCode 链接https://leetcode-cn.com/problems/customers-who-never-order 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
NOT IN 关键字
# Write your MySQL query statement below
SELECT C.Name Customers
FROM Customers C
WHERE C.Id NOT IN
(SELECT CustomerId FROM Orders
)或者
# Write your MySQL query statement below
SELECT C.Name Customers
FROM Customers C
LEFT OUTER JOIN Orders O
ON C.Id O.CustomerId
WHERE O.CustomerId is null363 ms
练习 LeetCode 596. 超过5名学生的课
题目
有一个courses 表 有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
---------------------
| student | class |
---------------------
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
---------------------
应该输出:
---------
| class |
---------
| Math |
---------
Note:
学生在每个课中不应被重复计算。有课程中重复出现2次A只算一次来源力扣LeetCode 链接https://leetcode-cn.com/problems/classes-more-than-5-students 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
SELECT class FROM courses GROUP BY class
HAVING COUNT(DISTINCT student) 5;206 ms
练习 LeetCode 586. 订单最多的客户
在表 orders 中找到订单数最多客户对应的 customer_number 。
数据保证订单数最多的顾客恰好只有一位。
表 orders 定义如下
| Column | Type |
|-------------------|-----------|
| order_number (PK) | int |
| customer_number | int |
| order_date | date |
| required_date | date |
| shipped_date | date |
| status | char(15) |
| comment | char(200) |样例输入
| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | |
| 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | |
| 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | |
| 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed | |
样例输出| customer_number |
|-----------------|
| 3 |解释
customer_number 为 ‘3’ 的顾客有两个订单比顾客 ‘1’ 或者 ‘2’ 都要多因为他们只有一个订单 所以结果是该顾客的 customer_number 也就是 3 。
进阶 如果有多位顾客订单数并列最多你能找到他们所有的 customer_number 吗 来源力扣LeetCode 链接https://leetcode-cn.com/problems/customer-placing-the-largest-number-of-orders 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
select t.customer_number customer_number from
(select customer_number, count(customer_number) amount from ordersgroup by customer_numberorder by amount desclimit 1 offset 0
) tor
# Write your MySQL query statement below
select customer_number from orders
group by customer_number
order by count(customer_number) desc
limit 1练习 LeetCode 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 表的外键.
编写一个 SQL 查询查询总销售额最高的销售者如果有并列的就都展示出来。
查询结果格式如下所示
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 |
--------------------------------------------------------------Result 表
-------------
| seller_id |
-------------
| 1 |
| 3 |
-------------
Id 为 1 和 3 的销售者销售总金额都为最高的 2800。来源力扣LeetCode 链接https://leetcode-cn.com/problems/sales-analysis-i 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
select seller_id from Sales
group by seller_id
having sum(price) (select sum(price) as totalincome from Salesgroup by seller_idorder by totalincome desclimit 1)or
all 函数所有的都要满足
# Write your MySQL query statement below
select seller_id from Sales
group by seller_id
having sum(price) all(select sum(price) from Salesgroup by seller_id)练习 LeetCode 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 次。来源力扣LeetCode 链接https://leetcode-cn.com/problems/actors-and-directors-who-cooperated-at-least-three-times 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
select actor_id, director_id from ActorDirector
group by actor_id, director_id
having count(*) 3练习 LeetCode 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 |
------来源力扣LeetCode 链接https://leetcode-cn.com/problems/article-views-i 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
distinct 去重
# Write your MySQL query statement below
select distinct author_id as id from Views
where viewer_id author_id
order by id练习 LeetCode 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 |
------------------------来源力扣LeetCode 链接https://leetcode-cn.com/problems/game-play-analysis-i 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
select player_id, min(event_date) first_login from Activity
group by player_id练习 LeetCode 1485. 按日期分组销售产品
表 Activities
----------------------
| 列名 | 类型 |
----------------------
| sell_date | date |
| product | varchar |
----------------------此表没有主键它可能包含重复项。 此表的每一行都包含产品名称和在市场上销售的日期。
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。 每个日期的销售产品名称应按词典序排列。 返回按 sell_date 排序的结果表。
查询结果格式如下例所示。
Activities 表
-------------------------
| sell_date | product |
-------------------------
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
-------------------------Result 表
----------------------------------------------------
| sell_date | num_sold | products |
----------------------------------------------------
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
----------------------------------------------------
对于2020-05-30出售的物品是 (Headphone, Basketball, T-shirt)按词典序排列并用逗号 , 分隔。
对于2020-06-01出售的物品是 (Pencil, Bible)按词典序排列并用逗号分隔。
对于2020-06-02出售的物品是 (Mask)只需返回该物品名。来源力扣LeetCode 链接https://leetcode-cn.com/problems/group-sold-products-by-the-date 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
group_concat() group by 产生的同一个分组中的值连接起来返回一个字符串结果。语法group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator 分隔符] )distinct可以去重order by子句 排序separator是一个字符串值缺省为一个逗号
# Write your MySQL query statement below
select sell_date, count(distinct product) num_sold, group_concat(distinct product order by product separator ,) products from Activities
group by sell_date
order by sell_date练习 LeetCode 1407. 排名靠前的旅行者
表单: Users
------------------------
| Column Name | Type |
------------------------
| id | int |
| name | varchar |
------------------------
id 是该表单主键.
name 是用户名字.表单: Rides
------------------------
| Column Name | Type |
------------------------
| id | int |
| user_id | int |
| distance | int |
------------------------
id 是该表单主键.
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance.写一段 SQL , 报告每个用户的旅行距离.
返回的结果表单, 以 travelled_distance 降序排列, 如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列.
查询结果格式, 如下例所示.
Users 表单:
-----------------
| id | name |
-----------------
| 1 | Alice |
| 2 | Bob |
| 3 | Alex |
| 4 | Donald |
| 7 | Lee |
| 13 | Jonathan |
| 19 | Elvis |
-----------------Rides 表单:
--------------------------
| id | user_id | distance |
--------------------------
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 13 | 312 |
| 6 | 19 | 50 |
| 7 | 7 | 120 |
| 8 | 19 | 400 |
| 9 | 7 | 230 |
--------------------------Result 表单:
------------------------------
| name | travelled_distance |
------------------------------
| Elvis | 450 |
| Lee | 450 |
| Bob | 317 |
| Jonathan | 312 |
| Alex | 222 |
| Alice | 120 |
| Donald | 0 |
------------------------------
Elvis 和 Lee 旅行了 450 英里, Elvis 是排名靠前的旅行者,
因为他的名字在字母表上的排序比 Lee 更小.
Bob, Jonathan, Alex 和 Alice 只有一次行程,
我们只按此次行程的全部距离对他们排序.
Donald 没有任何行程, 他的旅行距离为 0.来源力扣LeetCode 链接https://leetcode-cn.com/problems/top-travellers 著作权归领扣网络所有。商业转载请联系官方授权非商业转载请注明出处。 解题
# Write your MySQL query statement below
select name, ifnull(dis,0) travelled_distance from
(select user_id id, sum(distance) disfrom Ridesgroup by user_id
) t right join Users
using(id)
order by travelled_distance desc, nameor
# Write your MySQL query statement below
select name, ifnull(sum(distance),0) travelled_distance from
Users left join Rides
on Users.id Rides.user_id
group by Users.id
order by travelled_distance desc, name
