【LeetCode】学习计划——SQL入门

非虹

Day1 选择

595. 大的国家

World表：

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | name        | varchar |
5. | continent   | varchar |
6. | area        | int     |
7. | population  | int     |
8. | gdp         | int     |
9. +-------------+---------+
10. name 是这张表的主键。
11. 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值

复制代码

选择出：

• 面积至少为 300 万平方公里（即，\(3000000\ km^2\)），或者
  
• 人口至少为 2500 万（即 \(25000000\)）
  

方法一

两个条件一起查询：

1. select name, population, area
2. from World
3. where area >= 3000000 or population >= 25000000;

复制代码

方法二

使用union连接两个查询条件：

1. select name, population, area
2. from world
3. where area >= 3000000
4. union
5. select name, population, area
6. from world
7. where population >= 25000000;

复制代码

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序； 即：去重+排序
Union All：对两个结果集进行并集操作，包括重复行，不进行排序； 即：不去重+不排序
1757. 可回收且低脂的产品

Products表：

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | product_id  | int     |
5. | low_fats    | enum    |
6. | recyclable  | enum    |
7. +-------------+---------+
8. product_id 是这个表的主键。
9. low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
10. recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。

复制代码

1. select product_id
2. from products
3. where low_fats = 'Y' and recyclable = 'Y';

复制代码

584. 寻找用户推荐人

1. +------+------+-----------+
2. | id   | name | referee_id|
3. +------+------+-----------+
4. |    1 | Will |      NULL |
5. |    2 | Jane |      NULL |
6. |    3 | Alex |         2 |
7. |    4 | Bill |      NULL |
8. |    5 | Zack |         1 |
9. |    6 | Mark |         2 |
10. +------+------+-----------+

复制代码

MySQL有三个逻辑值：TRUE, FALSE, NULL
所以这个题如果直接选择 referee_id != 2，则会导致 referee_id = NULL的数据没有被选择出来，所以要加上 referee_id is null

1. select name
2. from customer
3. where referee_id != 2 or referee_id is null;

复制代码

还有一种方法是先选出来编号为2的元素然后取反：
运算符相当于封装了= 和 is ，既可以判断 非NULL值，也可以用来判断NULL值。只能在MySQL中使用

1. select name
2. from customer
3. where not referee_id <=> 2;

复制代码

或者使用 not in：

1. select name
2. from customer
3. where id not in (select id from customer where referee_id = 2);
4. # id是主键，所以选择referee_id等于2的id然后取反

复制代码

183. 从不订购的客户

Customers 表：

1. +----+-------+
2. | Id | Name  |
3. +----+-------+
4. | 1  | Joe   |
5. | 2  | Henry |
6. | 3  | Sam   |
7. | 4  | Max   |
8. +----+-------+

复制代码

Orders 表：

1. +----+------------+
2. | Id | CustomerId |
3. +----+------------+
4. | 1  | 3          |
5. | 2  | 1          |
6. +----+------------+

复制代码

注意要对name重命名为Customers

1. select customers.name as 'Customers'
2. from customers
3. where customers.id not in(select CustomerId from orders);

复制代码

使用左连接

1. select Customers.name as 'Customers'
2. from Customers
3. left join orders
4. on Customers.id = orders.CustomerId
5. where orders.CustomerId is null;

复制代码

Day2 排序&修改

1873. 计算特殊奖金

Employees表：

1. +-------------+---------+
2. | 列名        | 类型     |
3. +-------------+---------+
4. | employee_id | int     |
5. | name        | varchar |
6. | salary      | int     |
7. +-------------+---------+
8. employee_id 是这个表的主键。
9. 此表的每一行给出了雇员id ，名字和薪水。

复制代码

使用CASE

case配合when，then
when后面是条件，then后面是返回的结果

1. select employee_id,
2. (
3.     case
4.         when mod(employee_id, 2) != 0 and left(name, 1) != 'M' then salary
5.         else 0
6.     end
7. ) as bonus
8. from Employees
9. order by employee_id;

复制代码

使用IF

IF有三个参数，第一个是判断条件，第二个是条件成立的返回值，第三个是条件不成立的返回值

1. select employee_id,
2. if(mod(employee_id, 2) != 0 and left(name, 1) != 'M', salary, 0) as bonus
3. from Employees
4. order by employee_id;

复制代码

使用LIKE

使用LIKE进行匹配：

1. '%a'        //以a结尾的数据
2. 'a%'        //以a开头的数据
3. '%a%'        //含有a的数据
4. '_a_'        //三位且中间字母是a的
5. '_a'        //两位且结尾字母是a的
6. 'a_'        //两位且开头字母是a的

复制代码

1. select employee_id,
2. if(mod(employee_id, 2) = 0 or name like 'M%', 0, salary) as bonus
3. from Employees
4. order by employee_id;

复制代码

627. 变更性别

要求只使用单个 update 语句 ，且不产生中间临时表。
Salary 表：

1. +-------------+----------+
2. | Column Name | Type     |
3. +-------------+----------+
4. | id          | int      |
5. | name        | varchar  |
6. | sex         | ENUM     |
7. | salary      | int      |
8. +-------------+----------+
9. id 是这个表的主键。
10. sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
11. 本表包含公司雇员的信息。

复制代码

使用IF

1. update Salary
2. set sex = if(sex = 'f', 'm', 'f');

复制代码

使用CASE

1. update Salary
2. set sex =
3. case
4.     when sex = 'f' then 'm'
5.     else 'f'
6. end;

复制代码

196. 删除重复的电子邮箱

题目要求不使用SELECT语句
Person表：

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | id          | int     |
5. | email       | varchar |
6. +-------------+---------+
7. id是该表的主键列。
8. 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

复制代码

题解链接
delete p1表示从p1表中删除满足where条件的记录

1. # Please write a DELETE statement and DO NOT write a SELECT statement.
2. # Write your MySQL query statement below
3. delete p1
4. from Person p1, Person p2
5. where p1.email = p2.email and p1.id > p2.id;

复制代码

使用SELECT和GROUP BY：

1. delete from Person
2. where id not in(
3.     select * from(select min(id) from Person group by email) t
4. );

复制代码

Day3 字符串处理函数/正则

1667. 修复表中的名字

Users表：

1. +----------------+---------+
2. | Column Name    | Type    |
3. +----------------+---------+
4. | user_id        | int     |
5. | name           | varchar |
6. +----------------+---------+
7. user_id 是该表的主键。
8. 该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

复制代码

CONCAT函数用来拼接两个字符串，使用 UPPER和 LOWER来对name进行变换，然后拼接起来

1. select user_id, concat(upper(left(name, 1)), lower(substring(name, 2))) as name
2. from Users
3. order by user_id;

复制代码

1484. 按日期分组销售产品

Activities表：

1. +-------------+---------+
2. | 列名         | 类型    |
3. +-------------+---------+
4. | sell_date   | date    |
5. | product     | varchar |
6. +-------------+---------+
7. 此表没有主键，它可能包含重复项。
8. 此表的每一行都包含产品名称和在市场上销售的日期。

复制代码

用 group by sell_date将产品按日期统计起来，然后使用 count进行计数，使用 group_concat将产品名拼接起来

1. select sell_date,
2. count(distinct(product)) as num_sold,
3. group_concat(distinct product order by product asc separator ',') as products
4. from Activities
5. group by sell_date;

复制代码

1527. 患某种疾病的患者

患者信息表： Patients

1. +--------------+---------+
2. | Column Name  | Type    |
3. +--------------+---------+
4. | patient_id   | int     |
5. | patient_name | varchar |
6. | conditions   | varchar |
7. +--------------+---------+
8. patient_id （患者 ID）是该表的主键。
9. 'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
10. 这个表包含医院中患者的信息。

复制代码

用like匹配，注意两种情况：

• DIAB1在第一个，这时候用 DIAN1%匹配
  
• DIAB1不在第一个，此时要在用 % DIAB1%匹配，注意前面有个空格
  

1. select patient_id, patient_name, conditions
2. from Patients
3. where conditions like 'DIAB1%' or conditions like '% DIAB1%';

复制代码

Day4 组合查询 & 指定选取

1965. 丢失信息的雇员

表: Employees

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | employee_id | int     |
5. | name        | varchar |
6. +-------------+---------+
7. employee_id 是这个表的主键。
8. 每一行表示雇员的id 和他的姓名。

复制代码

表: Salaries

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | employee_id | int     |
5. | salary      | int     |
6. +-------------+---------+
7. employee_id is 这个表的主键。
8. 每一行表示雇员的id 和他的薪水。

复制代码

使用 union all来连接两个查询结果，通过 group by进行将employee_id进行聚合，使用 having count()选择仅出现一次的id
UNION和 UNION ALL的区别：前者会在连接后进行去重操作；后者不会去重，把查询出来的所有结果一起返回

1. select employee_id
2. from(
3.     select employee_id from Employees
4.     union all
5.     select employee_id from Salaries
6. ) as t
7. group by employee_id
8. having count(*) = 1
9. order by employee_id asc;

复制代码

1795. 每个产品在不同商店的价格

表：Products

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | product_id  | int     |
5. | store1      | int     |
6. | store2      | int     |
7. | store3      | int     |
8. +-------------+---------+
9. 这张表的主键是product_id（产品Id）。
10. 每行存储了这一产品在不同商店store1, store2, store3的价格。
11. 如果这一产品在商店里没有出售，则值将为null。

复制代码

将查询出来的 store{1,2,3}都重命名为 store，然后使用 union将三个查询连接起来

1. select product_id, 'store1' as store, store1 price from Products where store1 is not null
2. union
3. select product_id, 'store2' as store, store2 price from Products where store2 is not null
4. union
5. select product_id, 'store3' as store, store3 price from Products where store3 is not null;

复制代码

608. 树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

1. +----+------+
2. | id | p_id |
3. +----+------+
4. | 1  | null |
5. | 2  | 1    |
6. | 3  | 1    |
7. | 4  | 2    |
8. | 5  | 2    |
9. +----+------+

复制代码

使用CASE

如果 p_id为null，则节点为根节点
如果 id在 p_id里出现过，则为内部节点
没出现过的为叶子

1. select t.id, (
2.     case
3.     when t.p_id is null then 'Root'
4.     when (
5.         select count(*)
6.         from tree t1 where t1.p_id = t.id
7.     ) > 0 then 'Inner'
8.     else 'Leaf'
9.     end
10. ) as type
11. from tree as t;

复制代码

使用LEFT JOIN

将 id与 p_id进行左连接
如果 t1.p_id是空，则该节点是根节点
如果 t2.p_id是空，则说明 id没有在 p_id中出现过，即该节点是叶子
否则，是内部节点

1. select distinct t1.id, (
2.     if(isnull(t1.p_id), 'Root', if(isnull(t2.p_id), 'Leaf', 'Inner'))
3. ) as type
4. from tree t1
5. left join
6. tree t2 on t1.id = t2.p_id;

复制代码

176. 第二高的薪水

Employee 表：

1. +-------------+------+
2. | Column Name | Type |
3. +-------------+------+
4. | id          | int  |
5. | salary      | int  |
6. +-------------+------+
7. id 是这个表的主键。
8. 表的每一行包含员工的工资信息。

复制代码

方法二和方法三注意使用 DISTINCT去重，因为最高的薪水可能不止一个
方法一

从去除掉最大薪水的剩余表中查询最大薪水

1. select max(salary) as SecondHighestSalary
2. from Employee
3. where salary not in (select max(salary) from Employee);

复制代码

方法二

使用 limit 和 offset
offset表示要跳过的数据的数量
如果查询到的数据为空，用 ifnull将空数据变为null

1. select ifnull
2. (
3.     (
4.         select distinct salary
5.         from Employee
6.         order by salary desc
7.         limit 1 offset 1
8.     ), null
9. ) as SecondHighestSalary;

复制代码

方法三

使用临时表解决没有第二高工资的情况，对临时表进行选择，如果临时表是空表的话会返回null

1. select(
2.     select distinct salary
3.     from Employee
4.     order by salary desc
5.     limit 1 offset 1
6. ) as SecondHighestSalary;

复制代码

Day5 合并

LEFT JOIN从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
语法示例：

1. SELECT column_name(s)
2. FROM table1
3. LEFT JOIN table2
4. ON table1.column_name=table2.column_name;

复制代码

175. 组合两个表

表: Person

1. +-------------+---------+
2. | 列名         | 类型     |
3. +-------------+---------+
4. | PersonId    | int     |
5. | FirstName   | varchar |
6. | LastName    | varchar |
7. +-------------+---------+
8. personId 是该表的主键列。
9. 该表包含一些人的 ID 和他们的姓和名的信息。

复制代码

表: Address

1. +-------------+---------+
2. | 列名         | 类型    |
3. +-------------+---------+
4. | AddressId   | int     |
5. | PersonId    | int     |
6. | City        | varchar |
7. | State       | varchar |
8. +-------------+---------+
9. addressId 是该表的主键列。
10. 该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。

复制代码

直接使用左连接即可

1. select firstName, lastName, city, state
2. from Person
3. left join Address
4. on Person.personId = Address.personId;

复制代码

1581. 进店却未进行过交易的顾客

表：Visits

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | visit_id    | int     |
5. | customer_id | int     |
6. +-------------+---------+
7. visit_id 是该表的主键。
8. 该表包含有关光临过购物中心的顾客的信息。

复制代码

表：Transactions

1. +----------------+---------+
2. | Column Name    | Type    |
3. +----------------+---------+
4. | transaction_id | int     |
5. | visit_id       | int     |
6. | amount         | int     |
7. +----------------+---------+
8. transaction_id 是此表的主键。
9. 此表包含 visit_id 期间进行的交易的信息。

复制代码

使用左连接将 Visits表和 Transactions表连接，然后查询连接后的表里有多少个null

1. select customer_id, count(*) count_no_trans
2. from Visits v
3. left join
4. Transactions t on v.visit_id = t.visit_id
5. where amount is null
6. group by customer_id;

复制代码

1148. 文章浏览 I

Views 表：

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | article_id    | int     |
5. | author_id     | int     |
6. | viewer_id     | int     |
7. | view_date     | date    |
8. +---------------+---------+
9. 此表无主键，因此可能会存在重复行。
10. 此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
11. 请注意，同一人的 author_id 和 viewer_id 是相同的。

复制代码

使用 DISTINCT或 GROUP BY均可

1. select distinct author_id as id
2. from Views
3. where author_id = viewer_id
4. # group by id
5. order by id asc;

复制代码

Day6 合并

197. 上升的温度

表： Weather

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | id            | int     |
5. | recordDate    | date    |
6. | temperature   | int     |
7. +---------------+---------+
8. id 是这个表的主键
9. 该表包含特定日期的温度信息

复制代码

使用 DATEDIFF函数来判断两个日期的差值
可以使用 INNER JOIN连接，也可以直接select两个表：

1. select today.id
2. from
3. Weather today,
4. Weather yesterday
5. # 或者：
6. # Weather today
7. # inner join Weather yesterday
8. where datediff(today.recordDate, yesterday.recordDate) = 1 and today.Temperature > yesterday.Temperature;

复制代码

607. 销售员

表: SalesPerson

1. +-----------------+---------+
2. | Column Name     | Type    |
3. +-----------------+---------+
4. | sales_id        | int     |
5. | name            | varchar |
6. | salary          | int     |
7. | commission_rate | int     |
8. | hire_date       | date    |
9. +-----------------+---------+
10. sales_id 是该表的主键列。
11. 该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。

复制代码

使用 WHERE一直嵌套

1. select S.name as name
2. from SalesPerson S
3. where S.sales_id not in
4. (
5.     select O.sales_id
6.     from Orders O
7.     where O.com_id in
8.     (
9.         select C.com_id
10.         from Company C
11.         where C.name = 'RED'
12.     )
13. );

复制代码

1141. 查询近30天活跃用户数

活动记录表：Activity

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | user_id       | int     |
5. | session_id    | int     |
6. | activity_date | date    |
7. | activity_type | enum    |
8. +---------------+---------+
9. 该表是用户在社交网站的活动记录。
10. 该表没有主键，可能包含重复数据。
11. activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
12. 每个 session_id 只属于一个用户。

复制代码

注意是 distinct user_id，因为 一个用户可能会对应多个 session_id，datediff的时候要注意不小于0

1. select activity_date as day, count(distinct user_id) as active_users
2. from Activity
3. where datediff('2019-07-27', activity_date) < 30 and datediff('2019-07-27', activity_date) >= 0
4. group by activity_date;

复制代码

Day7 统计去重

1141. 查询近30天活跃用户数

活动记录表：Activity

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | user_id       | int     |
5. | session_id    | int     |
6. | activity_date | date    |
7. | activity_type | enum    |
8. +---------------+---------+
9. 该表是用户在社交网站的活动记录。
10. 该表没有主键，可能包含重复数据。
11. activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
12. 每个 session_id 只属于一个用户。

复制代码

注意是 distinct user_id，因为 一个用户可能会对应多个 session_id，datediff的时候要注意不小于0

1. select activity_date as day, count(distinct user_id) as active_users
2. from Activity
3. where datediff('2019-07-27', activity_date) < 30 and datediff('2019-07-27', activity_date) >= 0
4. group by activity_date;

复制代码

1693. 每天的领导和合伙人

表：DailySales

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | date_id     | date    |
5. | make_name   | varchar |
6. | lead_id     | int     |
7. | partner_id  | int     |
8. +-------------+---------+
9. 该表没有主键。
10. 该表包含日期、产品的名称，以及售给的领导和合伙人的编号。
11. 名称只包含小写英文字母。

复制代码

1. select date_id, make_name, count(distinct lead_id) as unique_leads, count(distinct partner_id) as unique_partners
2. from DailySales
3. group by date_id, make_name;

复制代码

1729. 求关注者的数量

表： Followers

1. +-------------+------+
2. | Column Name | Type |
3. +-------------+------+
4. | user_id     | int  |
5. | follower_id | int  |
6. +-------------+------+
7. (user_id, follower_id) 是这个表的主键。
8. 该表包含一个关注关系中关注者和用户的编号，其中关注者关注用户。

复制代码

1. select user_id, count(follower_id) as followers_count
2. from Followers
3. group by user_id
4. order by user_id asc;

复制代码

Day8 计算函数

586. 订单最多的客户

表: Orders

1. +-----------------+----------+
2. | Column Name     | Type     |
3. +-----------------+----------+
4. | order_number    | int      |
5. | customer_number | int      |
6. +-----------------+----------+
7. Order_number是该表的主键。
8. 此表包含关于订单ID和客户ID的信息。

复制代码

降序排序后用 limit 1选择出来第一个值，就是订单最多的用户

1. select customer_number
2. from Orders
3. group by customer_number
4. order by count(*) desc
5. limit 1;

复制代码

511. 游戏玩法分析 I

活动表 Activity：

1. +--------------+---------+
2. | Column Name  | Type    |
3. +--------------+---------+
4. | player_id    | int     |
5. | device_id    | int     |
6. | event_date   | date    |
7. | games_played | int     |
8. +--------------+---------+
9. 表的主键是 (player_id, event_date)。
10. 这张表展示了一些游戏玩家在游戏平台上的行为活动。
11. 每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。

复制代码

对 player_id进行分组，然后选择出每个id的最小的 event_date
使用排序后时间会比不用排序直接去最小值快将近100ms，chatgpt给出的解释是：

MySQL 中先排序再取最小值可能会变快的原因是，在数据表中有索引的情况下，如果在排序之前就取最小值，MySQL 的引擎会扫描整个表并在内存中对所有行进行排序，而如果先排序再取最小值，MySQL 的引擎只需要扫描索引并返回第一个索引值即可。
这个表现差异更明显的是在排序字段上有索引的情况下.
在这种情况下，MySQL 的引擎可以使用索引进行排序，而无需在内存中对所有行进行排序。因此查询速度会显著加快.

1. select player_id, min(event_date) as first_login
2. from Activity
3. group by player_id
4. order by event_date asc;

复制代码

1890. 2020年最后一次登录

表: Logins

1. +----------------+----------+
2. | 列名           | 类型      |
3. +----------------+----------+
4. | user_id        | int      |
5. | time_stamp     | datetime |
6. +----------------+----------+
7. (user_id, time_stamp) 是这个表的主键。
8. 每一行包含的信息是user_id 这个用户的登录时间。

复制代码

对 user_id进行分组，选出在2020年的最大登录时间

1. select user_id, max(time_stamp) as last_stamp
2. from Logins
3. where time_stamp between '2020-01-01 0:0:0' and '2020-12-31 23:59:59'
4. group by user_id;

复制代码

1741. 查找每个员工花费的总时间

表: Employees

1. +-------------+------+
2. | Column Name | Type |
3. +-------------+------+
4. | emp_id      | int  |
5. | event_day   | date |
6. | in_time     | int  |
7. | out_time    | int  |
8. +-------------+------+
9. (emp_id, event_day, in_time) 是这个表的主键。
10. 该表显示了员工在办公室的出入情况。
11. event_day 是此事件发生的日期，in_time 是员工进入办公室的时间，而 out_time 是他们离开办公室的时间。
12. in_time 和 out_time 的取值在1到1440之间。
13. 题目保证同一天没有两个事件在时间上是相交的，并且保证 in_time 小于 out_time。

复制代码

1. select event_day as day, emp_id, sum(out_time - in_time) as total_time
2. from Employees
3. group by emp_id, event_day;

复制代码

Day9 控制流

1393. 股票的资本损益

Stocks 表：

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | stock_name    | varchar |
5. | operation     | enum    |
6. | operation_day | int     |
7. | price         | int     |
8. +---------------+---------+
9. (stock_name, day) 是这张表的主键
10. operation 列使用的是一种枚举类型，包括：('Sell','Buy')
11. 此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
12. 保证股票的每次'Sell'操作前，都有相应的'Buy'操作。

复制代码

用 if判断一下，用 case也可以

1. select stock_name, sum(
2.     if(operation = 'Buy', -1 * price, price)
3. ) as capital_gain_loss
4. from Stocks
5. group by stock_name;

复制代码

1407. 排名靠前的旅行者

表：Users

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | id            | int     |
5. | name          | varchar |
6. +---------------+---------+
7. id 是该表单主键。
8. name 是用户名字。

复制代码

用 ifnull来将null变为0，order by可以排序多个字段

1. select name, ifnull(sum(distance), 0) as travelled_distance
2. from Users
3. left join
4. Rides on Users.id = Rides.user_id
5. group by user_id
6. order by travelled_distance desc, name asc;

复制代码

1158. 市场分析 I

Table: Users

1. +----------------+---------+
2. | Column Name    | Type    |
3. +----------------+---------+
4. | user_id        | int     |
5. | join_date      | date    |
6. | favorite_brand | varchar |
7. +----------------+---------+
8. 此表主键是 user_id。
9. 表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。

复制代码

Table: Orders

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | order_id      | int     |
5. | order_date    | date    |
6. | item_id       | int     |
7. | buyer_id      | int     |
8. | seller_id     | int     |
9. +---------------+---------+
10. 此表主键是 order_id。
11. 外键是 item_id 和（buyer_id，seller_id）。

复制代码

Table: Items

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | item_id       | int     |
5. | item_brand    | varchar |
6. +---------------+---------+
7. 此表主键是 item_id。

复制代码

Items表是没有用的。
首先从 Orders表中选出在2019年买过商品的 buyer_id，然后用 group by分组，统计出来每个人买的次数，然后和 Users表进行连接

1. select user_id as buyer_id, join_date, ifnull(orders_in_2019, 0) as orders_in_2019
2. from Users as U
3. left join(
4.     select buyer_id, count(*) as orders_in_2019
5.     from Orders as O
6.     where O.order_date between '2019-01-01' and '2019-12-31'
7.     group by buyer_id
8. ) as t
9. on t.buyer_id = U.user_id;

复制代码

Day10 过滤

182. 查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：

1. +----+---------+
2. | Id | Email   |
3. +----+---------+
4. | 1  | a@b.com |
5. | 2  | c@d.com |
6. | 3  | a@b.com |
7. +----+---------+

复制代码

使用GROUP BY

1. select Email
2. from(
3.     select Email, count(*) as cnt
4.     from Person
5.     group by Email
6. ) as t
7. where t.cnt > 1;

复制代码

使用GROUP BY和HAVING

1. select Email
2. from Person
3. group by Email
4. having count(*) > 1;

复制代码

1050. 合作过至少三次的演员和导演

ActorDirector 表：

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | actor_id    | int     |
5. | director_id | int     |
6. | timestamp   | int     |
7. +-------------+---------+
8. timestamp 是这张表的主键.

复制代码

使用GROUP BY和HAVING

1. select actor_id, director_id
2. from ActorDirector
3. group by actor_id, director_id
4. having count(*) >= 3;

复制代码

1587. 银行账户概要 II

表: Users

1. +--------------+---------+
2. | Column Name  | Type    |
3. +--------------+---------+
4. | account      | int     |
5. | name         | varchar |
6. +--------------+---------+
7. account 是该表的主键.
8. 表中的每一行包含银行里中每一个用户的账号.

复制代码

表: Transactions

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | trans_id      | int     |
5. | account       | int     |
6. | amount        | int     |
7. | transacted_on | date    |
8. +---------------+---------+
9. trans_id 是该表主键.
10. 该表的每一行包含了所有账户的交易改变情况.
11. 如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的.
12. 所有账户的起始余额为 0.

复制代码

使用左连接将两个表连接起来，然后对 account进行分组，计算账户余额，最后用 having选出余额大于一万的账户

1. select name,sum(amount) as balance
2. from Users as U
3. left join
4. Transactions as T
5. on U.account = T.account
6. group by T.account
7. having balance > 10000;

复制代码

1084. 销售分析III

Table: Product

1. +--------------+---------+
2. | Column Name  | Type    |
3. +--------------+---------+
4. | product_id   | int     |
5. | product_name | varchar |
6. | unit_price   | int     |
7. +--------------+---------+
8. Product_id是该表的主键。
9. 该表的每一行显示每个产品的名称和价格。

复制代码

Table: Sales

1. +-------------+---------+
2. | Column Name | Type    |
3. +-------------+---------+
4. | seller_id   | int     |
5. | product_id  | int     |
6. | buyer_id    | int     |
7. | sale_date   | date    |
8. | quantity    | int     |
9. | price       | int     |
10. +------ ------+---------+
11. 这个表没有主键，它可以有重复的行。
12. product_id 是 Product 表的外键。
13. 该表的每一行包含关于一个销售的一些信息。

复制代码

注意是产品的所有销售时间都在第一个季度，所以要判断销售时间的最大值和最小值均在第一季度

1. select P.product_id, P.product_name
2. from Product as P
3. left join
4. Sales as S on S.product_id = P.product_id
5. group by S.product_id
6. having (min(S.sale_date) between '2019-01-01' and '2019-03-31') and  (max(S.sale_date) between '2019-01-01' and '2019-03-31')

复制代码

来源:https://www.cnblogs.com/Friends-A/p/17046597.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！