Loading... # Day1 选择 ## [595. 大的国家](https://leetcode.cn/problems/big-countries/) World表: ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | int | +-------------+---------+ name 是这张表的主键。 这张表的每一行提供:国家名称、所属大陆、面积、人口和 GDP 值 ``` 选择出: 1. 面积至少为 300 万平方公里(即,$3000000\ km^2$),或者 2. 人口至少为 2500 万(即 $25000000$) ### 方法一 两个条件一起查询: ```mysql select name, population, area from World where area >= 3000000 or population >= 25000000; ``` ### 方法二 使用union连接两个查询条件: ```mysql select name, population, area from world where area >= 3000000 union select name, population, area from world where population >= 25000000; ``` Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序; 即:去重+排序 Union All:对两个结果集进行并集操作,包括重复行,不进行排序; 即:不去重+不排序 ## [1757. 可回收且低脂的产品](https://leetcode.cn/problems/recyclable-and-low-fat-products/) 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' 表示不可回收。 ``` ```mysql select product_id from products where low_fats = 'Y' and recyclable = 'Y'; ``` ## [584. 寻找用户推荐人](https://leetcode.cn/problems/find-customer-referee/) ``` +------+------+-----------+ | id | name | referee_id| +------+------+-----------+ | 1 | Will | NULL | | 2 | Jane | NULL | | 3 | Alex | 2 | | 4 | Bill | NULL | | 5 | Zack | 1 | | 6 | Mark | 2 | +------+------+-----------+ ``` MySQL有三个逻辑值:TRUE, FALSE, NULL 所以这个题如果直接选择 `referee_id != 2`,则会导致 `referee_id = NULL`的数据没有被选择出来,所以要加上 `referee_id is null` ```mysql select name from customer where referee_id != 2 or referee_id is null; ``` 还有一种方法是先选出来编号为2的元素然后取反: `<=>`运算符相当于封装了= 和 is ,既可以判断 非NULL值,也可以用来判断NULL值。只能在MySQL中使用 ```mysql select name from customer where not referee_id <=> 2; ``` 或者使用 `not in`: ```mysql select name from customer where id not in (select id from customer where referee_id = 2); # id是主键,所以选择referee_id等于2的id然后取反 ``` ## [183. 从不订购的客户](https://leetcode.cn/problems/customers-who-never-order/) Customers 表: ``` +----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ ``` Orders 表: ``` +----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ ``` 注意要对name重命名为Customers ```mysql select customers.name as 'Customers' from customers where customers.id not in(select CustomerId from orders); ``` ### 使用左连接 ```mysql select Customers.name as 'Customers' from Customers left join orders on Customers.id = orders.CustomerId where orders.CustomerId is null; ``` # Day2 排序&修改 ## [1873. 计算特殊奖金](https://leetcode.cn/problems/calculate-special-bonus/) Employees表: ``` +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | employee_id | int | | name | varchar | | salary | int | +-------------+---------+ employee_id 是这个表的主键。 此表的每一行给出了雇员id ,名字和薪水。 ``` ### 使用CASE case配合when,then when后面是条件,then后面是返回的结果 ```mysql select employee_id, ( case when mod(employee_id, 2) != 0 and left(name, 1) != 'M' then salary else 0 end ) as bonus from Employees order by employee_id; ``` ### 使用IF IF有三个参数,第一个是判断条件,第二个是条件成立的返回值,第三个是条件不成立的返回值 ```mysql select employee_id, if(mod(employee_id, 2) != 0 and left(name, 1) != 'M', salary, 0) as bonus from Employees order by employee_id; ``` ### 使用LIKE 使用LIKE进行匹配: ``` '%a'//以a结尾的数据 'a%'//以a开头的数据 '%a%'//含有a的数据 '_a_'//三位且中间字母是a的 '_a'//两位且结尾字母是a的 'a_'//两位且开头字母是a的 ``` ```mysql select employee_id, if(mod(employee_id, 2) = 0 or name like 'M%', 0, salary) as bonus from Employees order by employee_id; ``` ## [627. 变更性别](https://leetcode.cn/problems/swap-salary/) 要求只使用**单个 update 语句** ,且不产生中间临时表。 Salary 表: ``` +-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | name | varchar | | sex | ENUM | | salary | int | +-------------+----------+ id 是这个表的主键。 sex 这一列的值是 ENUM 类型,只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。 ``` ### 使用IF ```mysql update Salary set sex = if(sex = 'f', 'm', 'f'); ``` ### 使用CASE ```mysql update Salary set sex = case when sex = 'f' then 'm' else 'f' end; ``` ## [196. 删除重复的电子邮箱](https://leetcode.cn/problems/delete-duplicate-emails/) 题目要求不使用SELECT语句 Person表: ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id是该表的主键列。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。 ``` [题解链接](https://leetcode.cn/problems/delete-duplicate-emails/solution/dui-guan-fang-ti-jie-zhong-delete-he-de-jie-shi-by/) `delete p1`表示从p1表中删除满足where条件的记录 ```mysql # 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; ``` 使用SELECT和GROUP BY: ```mysql delete from Person where id not in( select * from(select min(id) from Person group by email) t ); ``` # Day3 字符串处理函数/正则 ## [1667. 修复表中的名字](https://leetcode.cn/problems/fix-names-in-a-table/) Users表: ``` +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | name | varchar | +----------------+---------+ user_id 是该表的主键。 该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。 ``` `CONCAT`函数用来拼接两个字符串,使用 `UPPER`和 `LOWER`来对name进行变换,然后拼接起来 ```mysql select user_id, concat(upper(left(name, 1)), lower(substring(name, 2))) as name from Users order by user_id; ``` ## [1484. 按日期分组销售产品](https://leetcode.cn/problems/group-sold-products-by-the-date/) Activities表: ``` +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | sell_date | date | | product | varchar | +-------------+---------+ 此表没有主键,它可能包含重复项。 此表的每一行都包含产品名称和在市场上销售的日期。 ``` 用 `group by sell_date`将产品按日期统计起来,然后使用 `count`进行计数,使用 `group_concat`将产品名拼接起来 ```mysql select sell_date, count(distinct(product)) as num_sold, group_concat(distinct product order by product asc separator ',') as products from Activities group by sell_date; ``` ## [1527. 患某种疾病的患者](https://leetcode.cn/problems/patients-with-a-condition/) 患者信息表: Patients ``` +--------------+---------+ | Column Name | Type | +--------------+---------+ | patient_id | int | | patient_name | varchar | | conditions | varchar | +--------------+---------+ patient_id (患者 ID)是该表的主键。 'conditions' (疾病)包含 0 个或以上的疾病代码,以空格分隔。 这个表包含医院中患者的信息。 ``` 用like匹配,注意两种情况: 1. `DIAB1`在第一个,这时候用 `DIAN1%`匹配 2. `DIAB1`不在第一个,此时要在用 `% DIAB1%`匹配,注意前面有个空格 ```mysql select patient_id, patient_name, conditions from Patients where conditions like 'DIAB1%' or conditions like '% DIAB1%'; ``` # Day4 组合查询 & 指定选取 ## [1965. 丢失信息的雇员](https://leetcode.cn/problems/employees-with-missing-information/) 表: Employees ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id 是这个表的主键。 每一行表示雇员的id 和他的姓名。 ``` 表: Salaries ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | salary | int | +-------------+---------+ employee_id is 这个表的主键。 每一行表示雇员的id 和他的薪水。 ``` 使用 `union all`来连接两个查询结果,通过 `group by`进行将employee_id进行聚合,使用 `having count()`选择仅出现一次的id `UNION`和 `UNION ALL`的区别:前者会在连接后进行去重操作;后者不会去重,把查询出来的所有结果一起返回 ```mysql select employee_id from( select employee_id from Employees union all select employee_id from Salaries ) as t group by employee_id having count(*) = 1 order by employee_id asc; ``` ## [1795. 每个产品在不同商店的价格](https://leetcode.cn/problems/rearrange-products-table/) 表:Products ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | store1 | int | | store2 | int | | store3 | int | +-------------+---------+ 这张表的主键是product_id(产品Id)。 每行存储了这一产品在不同商店store1, store2, store3的价格。 如果这一产品在商店里没有出售,则值将为null。 ``` 将查询出来的 `store{1,2,3}`都重命名为 `store`,然后使用 `union`将三个查询连接起来 ```mysql select product_id, 'store1' as store, store1 price from Products where store1 is not null union select product_id, 'store2' as store, store2 price from Products where store2 is not null union select product_id, 'store3' as store, store3 price from Products where store3 is not null; ``` ## [608. 树节点](https://leetcode.cn/problems/tree-node/) 给定一个表 tree,id 是树节点的编号, p_id 是它父节点的 id 。 ``` +----+------+ | id | p_id | +----+------+ | 1 | null | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +----+------+ ``` ### 使用CASE 如果 `p_id`为null,则节点为根节点 如果 `id`在 `p_id`里出现过,则为内部节点 没出现过的为叶子 ```mysql select t.id, ( case when t.p_id is null then 'Root' when ( select count(*) from tree t1 where t1.p_id = t.id ) > 0 then 'Inner' else 'Leaf' end ) as type from tree as t; ``` ### 使用LEFT JOIN 将 `id`与 `p_id`进行左连接 如果 `t1.p_id`是空,则该节点是根节点 如果 `t2.p_id`是空,则说明 `id`没有在 `p_id`中出现过,即该节点是叶子 否则,是内部节点 ```mysql select distinct t1.id, ( if(isnull(t1.p_id), 'Root', if(isnull(t2.p_id), 'Leaf', 'Inner')) ) as type from tree t1 left join tree t2 on t1.id = t2.p_id; ``` ## [176. 第二高的薪水](https://leetcode.cn/problems/second-highest-salary/) Employee 表: ``` +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id 是这个表的主键。 表的每一行包含员工的工资信息。 ``` **方法二和方法三注意使用 `DISTINCT`去重,因为最高的薪水可能不止一个** ### 方法一 从去除掉最大薪水的剩余表中查询最大薪水 ```mysql select max(salary) as SecondHighestSalary from Employee where salary not in (select max(salary) from Employee); ``` ### 方法二 使用 `limit` 和 `offset` `offset`表示要跳过的数据的数量 如果查询到的数据为空,用 `ifnull`将空数据变为null ```mysql select ifnull ( ( select distinct salary from Employee order by salary desc limit 1 offset 1 ), null ) as SecondHighestSalary; ``` ### 方法三 使用临时表解决没有第二高工资的情况,对临时表进行选择,如果临时表是空表的话会返回null ```mysql select( select distinct salary from Employee order by salary desc limit 1 offset 1 ) as SecondHighestSalary; ``` # Day5 合并 `LEFT JOIN`从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。 语法示例: ```mysql SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name; ``` ## [175. 组合两个表](https://leetcode.cn/problems/combine-two-tables/) 表: Person ``` +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ personId 是该表的主键列。 该表包含一些人的 ID 和他们的姓和名的信息。 ``` 表: Address ``` +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ addressId 是该表的主键列。 该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。 ``` 直接使用左连接即可 ```mysql select firstName, lastName, city, state from Person left join Address on Person.personId = Address.personId; ``` ## [1581. 进店却未进行过交易的顾客](https://leetcode.cn/problems/customer-who-visited-but-did-not-make-any-transactions/) 表:Visits ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | visit_id | int | | customer_id | int | +-------------+---------+ visit_id 是该表的主键。 该表包含有关光临过购物中心的顾客的信息。 ``` 表:Transactions ``` +----------------+---------+ | Column Name | Type | +----------------+---------+ | transaction_id | int | | visit_id | int | | amount | int | +----------------+---------+ transaction_id 是此表的主键。 此表包含 visit_id 期间进行的交易的信息。 ``` 使用左连接将 `Visits`表和 `Transactions`表连接,然后查询连接后的表里有多少个null ```mysql select customer_id, count(*) count_no_trans from Visits v left join Transactions t on v.visit_id = t.visit_id where amount is null group by customer_id; ``` ## [1148. 文章浏览 I](https://leetcode.cn/problems/article-views-i/) Views 表: ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | article_id | int | | author_id | int | | viewer_id | int | | view_date | date | +---------------+---------+ 此表无主键,因此可能会存在重复行。 此表的每一行都表示某人在某天浏览了某位作者的某篇文章。 请注意,同一人的 author_id 和 viewer_id 是相同的。 ``` 使用 `DISTINCT`或 `GROUP BY`均可 ```mysql select distinct author_id as id from Views where author_id = viewer_id # group by id order by id asc; ``` # Day6 合并 ## [197. 上升的温度](https://leetcode.cn/problems/rising-temperature/) 表: Weather ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id 是这个表的主键 该表包含特定日期的温度信息 ``` 使用 `DATEDIFF`函数来判断两个日期的差值 可以使用 `INNER JOIN`连接,也可以直接select两个表: ```mysql select today.id from Weather today, Weather yesterday # 或者: # Weather today # inner join Weather yesterday where datediff(today.recordDate, yesterday.recordDate) = 1 and today.Temperature > yesterday.Temperature; ``` ## [607. 销售员](https://leetcode.cn/problems/sales-person/) 表: SalesPerson ``` +-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id 是该表的主键列。 该表的每一行都显示了销售人员的姓名和 ID ,以及他们的工资、佣金率和雇佣日期。 ``` 使用 `WHERE`一直嵌套 ```mysql select S.name as name from SalesPerson S where S.sales_id not in ( select O.sales_id from Orders O where O.com_id in ( select C.com_id from Company C where C.name = 'RED' ) ); ``` ## [1141. 查询近30天活跃用户数](https://leetcode.cn/problems/user-activity-for-the-past-30-days-i/) 活动记录表: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 只属于一个用户。 ``` 注意是 `distinct user_id`,因为 一个用户可能会对应多个 `session_id`,`datediff`的时候要注意不小于0 ```mysql select activity_date as day, count(distinct user_id) as active_users from Activity where datediff('2019-07-27', activity_date) < 30 and datediff('2019-07-27', activity_date) >= 0 group by activity_date; ``` # Day7 统计去重 ## [1141. 查询近30天活跃用户数](https://leetcode.cn/problems/user-activity-for-the-past-30-days-i/) 活动记录表: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 只属于一个用户。 ``` 注意是 `distinct user_id`,因为 一个用户可能会对应多个 `session_id`,`datediff`的时候要注意不小于0 ```mysql select activity_date as day, count(distinct user_id) as active_users from Activity where datediff('2019-07-27', activity_date) < 30 and datediff('2019-07-27', activity_date) >= 0 group by activity_date; ``` ## [1693. 每天的领导和合伙人](https://leetcode.cn/problems/daily-leads-and-partners/) 表:DailySales ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | date_id | date | | make_name | varchar | | lead_id | int | | partner_id | int | +-------------+---------+ 该表没有主键。 该表包含日期、产品的名称,以及售给的领导和合伙人的编号。 名称只包含小写英文字母。 ``` ```mysql select date_id, make_name, count(distinct lead_id) as unique_leads, count(distinct partner_id) as unique_partners from DailySales group by date_id, make_name; ``` ## [1729. 求关注者的数量](https://leetcode.cn/problems/find-followers-count/) 表: Followers ``` +-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | follower_id | int | +-------------+------+ (user_id, follower_id) 是这个表的主键。 该表包含一个关注关系中关注者和用户的编号,其中关注者关注用户。 ``` ```mysql select user_id, count(follower_id) as followers_count from Followers group by user_id order by user_id asc; ``` # Day8 计算函数 ## [586. 订单最多的客户](https://leetcode.cn/problems/customer-placing-the-largest-number-of-orders/) 表: Orders ``` +-----------------+----------+ | Column Name | Type | +-----------------+----------+ | order_number | int | | customer_number | int | +-----------------+----------+ Order_number是该表的主键。 此表包含关于订单ID和客户ID的信息。 ``` 降序排序后用 `limit 1`选择出来第一个值,就是订单最多的用户 ```mysql select customer_number from Orders group by customer_number order by count(*) desc limit 1; ``` ## [511. 游戏玩法分析 I](https://leetcode.cn/problems/game-play-analysis-i/) 活动表 Activity: ``` +--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ 表的主键是 (player_id, event_date)。 这张表展示了一些游戏玩家在游戏平台上的行为活动。 每行数据记录了一名玩家在退出平台之前,当天使用同一台设备登录平台后打开的游戏的数目(可能是 0 个)。 ``` 对 `player_id`进行分组,然后选择出每个id的最小的 `event_date` 使用排序后时间会比不用排序直接去最小值快将近100ms,chatgpt给出的解释是: > MySQL 中先排序再取最小值可能会变快的原因是,在数据表中有索引的情况下,如果在排序之前就取最小值,MySQL 的引擎会扫描整个表并在内存中对所有行进行排序,而如果先排序再取最小值,MySQL 的引擎只需要扫描索引并返回第一个索引值即可。 > > 这个表现差异更明显的是在排序字段上有索引的情况下. > 在这种情况下,MySQL 的引擎可以使用索引进行排序,而无需在内存中对所有行进行排序。因此查询速度会显著加快. ```mysql select player_id, min(event_date) as first_login from Activity group by player_id order by event_date asc; ``` ## [1890. 2020年最后一次登录](https://leetcode.cn/problems/the-latest-login-in-2020/) 表: Logins ``` +----------------+----------+ | 列名 | 类型 | +----------------+----------+ | user_id | int | | time_stamp | datetime | +----------------+----------+ (user_id, time_stamp) 是这个表的主键。 每一行包含的信息是user_id 这个用户的登录时间。 ``` 对 `user_id`进行分组,选出在2020年的最大登录时间 ```mysql select user_id, max(time_stamp) as last_stamp from Logins where time_stamp between '2020-01-01 0:0:0' and '2020-12-31 23:59:59' group by user_id; ``` ## [1741. 查找每个员工花费的总时间](https://leetcode.cn/problems/find-total-time-spent-by-each-employee/) 表: Employees ``` +-------------+------+ | Column Name | Type | +-------------+------+ | emp_id | int | | event_day | date | | in_time | int | | out_time | int | +-------------+------+ (emp_id, event_day, in_time) 是这个表的主键。 该表显示了员工在办公室的出入情况。 event_day 是此事件发生的日期,in_time 是员工进入办公室的时间,而 out_time 是他们离开办公室的时间。 in_time 和 out_time 的取值在1到1440之间。 题目保证同一天没有两个事件在时间上是相交的,并且保证 in_time 小于 out_time。 ``` ```mysql select event_day as day, emp_id, sum(out_time - in_time) as total_time from Employees group by emp_id, event_day; ``` # Day9 控制流 ## [1393. 股票的资本损益](https://leetcode.cn/problems/capital-gainloss/) Stocks 表: ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | stock_name | varchar | | operation | enum | | operation_day | int | | price | int | +---------------+---------+ (stock_name, day) 是这张表的主键 operation 列使用的是一种枚举类型,包括:('Sell','Buy') 此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。 保证股票的每次'Sell'操作前,都有相应的'Buy'操作。 ``` 用 `if`判断一下,用 `case`也可以 ```mysql select stock_name, sum( if(operation = 'Buy', -1 * price, price) ) as capital_gain_loss from Stocks group by stock_name; ``` ## [1407. 排名靠前的旅行者](https://leetcode.cn/problems/top-travellers/) 表:Users ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id 是该表单主键。 name 是用户名字。 ``` 用 `ifnull`来将null变为0,`order by`可以排序多个字段 ```mysql select name, ifnull(sum(distance), 0) as travelled_distance from Users left join Rides on Users.id = Rides.user_id group by user_id order by travelled_distance desc, name asc; ``` ## [1158. 市场分析 I](https://leetcode.cn/problems/market-analysis-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。 ``` `Items`表是没有用的。 首先从 `Orders`表中选出在2019年买过商品的 `buyer_id`,然后用 `group by`分组,统计出来每个人买的次数,然后和 `Users`表进行连接 ```mysql select user_id as buyer_id, join_date, ifnull(orders_in_2019, 0) as orders_in_2019 from Users as U left join( select buyer_id, count(*) as orders_in_2019 from Orders as O where O.order_date between '2019-01-01' and '2019-12-31' group by buyer_id ) as t on t.buyer_id = U.user_id; ``` # Day10 过滤 ## [182. 查找重复的电子邮箱](https://leetcode.cn/problems/duplicate-emails/) 编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。 示例: ``` +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ ``` ### 使用GROUP BY ```mysql select Email from( select Email, count(*) as cnt from Person group by Email ) as t where t.cnt > 1; ``` ### 使用GROUP BY和HAVING ```mysql select Email from Person group by Email having count(*) > 1; ``` ## [1050. 合作过至少三次的演员和导演](https://leetcode.cn/problems/actors-and-directors-who-cooperated-at-least-three-times/) ActorDirector 表: ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp 是这张表的主键. ``` 使用GROUP BY和HAVING ```mysql select actor_id, director_id from ActorDirector group by actor_id, director_id having count(*) >= 3; ``` ## [1587. 银行账户概要 II](https://leetcode.cn/problems/bank-account-summary-ii/) 表: Users ``` +--------------+---------+ | Column Name | Type | +--------------+---------+ | account | int | | name | varchar | +--------------+---------+ account 是该表的主键. 表中的每一行包含银行里中每一个用户的账号. ``` 表: Transactions ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | account | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id 是该表主键. 该表的每一行包含了所有账户的交易改变情况. 如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的. 所有账户的起始余额为 0. ``` 使用左连接将两个表连接起来,然后对 `account`进行分组,计算账户余额,最后用 `having`选出余额大于一万的账户 ```mysql select name,sum(amount) as balance from Users as U left join Transactions as T on U.account = T.account group by T.account having balance > 10000; ``` ## [1084. 销售分析III](https://leetcode.cn/problems/sales-analysis-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 表的外键。 该表的每一行包含关于一个销售的一些信息。 ``` 注意是产品的所有销售时间都在第一个季度,所以要判断销售时间的最大值和最小值均在第一季度 ```mysql select P.product_id, P.product_name from Product as P left join Sales as S on S.product_id = P.product_id group by S.product_id 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') ``` Last modification:January 12, 2023 © Allow specification reprint Support Appreciate the author AliPayWeChat Like 0 如果觉得我的文章对你有用,请随意赞赏