MySQL

最新推荐文章于 2023-04-06 13:44:50 发布

枪枪枪

最新推荐文章于 2023-04-06 13:44:50 发布

阅读量516

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/az9996/article/details/112154458

版权

SQL 专栏收录该内容

8 篇文章 2 订阅

订阅专栏

文章目录

6.过滤数据

where

在这里插入图片描述

595. 大的国家

select name,population,area from World where (area>='3000000') or (population>='25000000');

9. 使用正则表达式进行搜索

REGEXP

语法：

SELECT * FROM table WHERE field_name REGEXP '表达式'

在这里插入图片描述

13.分组数据

对分组进行sum

1179. 重新格式化部门表

select id,
	sum(case when month='Jan' then revenue end) as Jan_Revenue,
    sum(case when month='Feb' then revenue end) as Feb_Revenue,
    sum(case when month='Mar' then revenue end) as Mar_Revenue,
    sum(case when month='Apr' then revenue end) as Apr_Revenue,
    sum(case when month='May' then revenue end) as May_Revenue,
    sum(case when month='Jun' then revenue end) as Jun_Revenue,
    sum(case when month='Jul' then revenue end) as Jul_Revenue,
    sum(case when month='Aug' then revenue end) as Aug_Revenue, 
    sum(case when month='Sep' then revenue end) as Sep_Revenue, 
    sum(case when month='Oct' then revenue end) as Oct_Revenue, 
    sum(case when month='Nov' then revenue end) as Nov_Revenue, 
    sum(case when month='Dec' then revenue end) as Dec_Revenue
 from department 
 group by id 
 order by id;

过滤分组：having子句

where过滤行
having过滤分组

having支持所有where操作符

优先级：where > group by > having > order by

资料：
https://www.cnblogs.com/xuchao0506/p/9766234.html

182. 查找重复的电子邮箱

select Email from Person group by (Email) having count(Email)>1;

15. 联结

所有连接方式都会先生成临时笛卡尔积表

笛卡尔积
在笛卡尔积的基础上添加条件筛选
左连接、右连接、内连接

内连接（inner join）：分步骤理解时，可以看作先对两个表进行交叉连接，再通过加上限制条件（SQL中通过关键字on）剔除不符合条件的行的子集，得到的结果就是内连接
外连接（left outer join 、right outer join）:可以使连接表的一方或双方不必遵守on后面的连接限制条件

join

资料:
http://www.360doc.com/content/14/1229/21/7635_436727229.shtml

181. 超过经理收入的员工

select a.name as Employee from employee as a  join employee as b on a.ManagerId=b.Id and a.Salary > b.Salary;

17.组合查询

union

连接多个查询语句

select name,population,area from world where area>='3000000'
union
select name,population,area from world where population>='25000000';

595. 大的国家

select name,population,area from world where area>='3000000'
union
select name,population,area from world where population>='25000000';

183. 从不订购的客户

在联结表的基础上再使用where对行进行筛选

select a.name as Customers from Customers as a left join Orders as b on a.id=b.CustomerId where b.CustomerId is null;

20.更新和删除

196. 删除重复的电子邮箱

思路：使用group by根据Email对数据进行分组，使用MIN函数得到每个email分组中id最小的一行，删除这些id之外的所有id

delete from Person as b 
where b.Id not in (
select c.Id from 
	(select Min(a.Id) as Id from Person  as a group by a.Email having count(a.Email)>0) as c
)

这里对c表又查询一次是为了避免You can't specify target table 'b' for update in FROM clause错误
错误原因：不能在select 某个表的同时直接 update 这个表

资料：
关于MySQL中删除满足子查询结果数据的操作
https://www.cnblogs.com/wing7319/p/10458765.html
MySQL之You can’t specify target table for update in FROM clause解决办法
https://blog.csdn.net/u012767761/article/details/84997962
https://blog.csdn.net/poetssociety/article/details/82391523

if 表达式

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

资料：
https://blog.csdn.net/wenxuechaozhe/article/details/51815326#

627. 变更性别

update salary set sex=if(sex='m','f','m');

关键词

DISTINCT

去除重复的值
语法

SELECT DISTINCT 列名称 FROM 表名称

https://www.w3school.com.cn/sql/sql_distinct.asp

LIMIT

语法：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

结合offset使用

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

176. 第二高的薪水

select (select distinct Salary from Employee order by Salary desc limit 1 offset 1) as SecondHighestSalary;

原生函数

mod 取模运算

620. 有趣的电影

select id,movie,description,rating from cinema where mod(cinema.id,2)=1 and description!='boring' order by rating desc

DATEDIFF() 函数

DATEDIFF() 函数返回两个日期之间的天数

DATEDIFF(date1,date2)

197. 上升的温度

自联结，自己和自己表中进行比较时使用。

select a.id as 'id' from Weather as a join Weather as b on DATEDIFF(a.recordDate , b.recordDate ) = 1 and a.temperature>b.temperature;

困难

601. 体育馆的人流量

select distinct a.*
from Stadium as a,Stadium as b,Stadium as c
where (
    (a.id=b.id-1 and b.id+1=c.id)
    or (a.id-1=b.id and a.id+1 = c.id)
    or (a.id-1=c.id and c.id-1 = b.id)
)
and (a.people>=100 and b.people>=100 and c.people>=100) order by a.id;

with t1 as (
select
    id,
    visit_date,
    people,
#求出差值，因为id一定不会相同，所以使用最熟悉的rank就好
    id-rank() over(order by id) rk
from stadium
where people >= 100
)
select
    id,
    visit_date,
    people
from t1
#where条件过滤出条数大于3的
where rk in (
select rk from t1 group by rk having count(1) >= 3);

作者：bryce-28
链接：https://leetcode-cn.com/problems/human-traffic-of-stadium/solution/da-shu-ju-fang-xiang-xia-ci-ti-jie-ti-si-lu-by-bry/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

关于with … as …

生成一个可复用的临时表

临时表 t1,表中内容为select 的结果

with t1 as (
select
    id,
    visit_date,
    people,
#求出差值，因为id一定不会相同，所以使用最熟悉的rank就好
    id-rank() over(order by id) rk
from stadium
where people >= 100
)

oralce函数：LAG()和LEAD()

在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、rightjoin等自连接相比，效率更高，SQL更简洁。
资料来源：
https://blog.csdn.net/pelifymeng2/article/details/70313943

函数语法

lag(exp_str,offset,defval) over(partion by ..order by …)

lead(exp_str,offset,defval) over(partion by ..order by …)

Hive 窗口及分析函数 (rank、dense_rank、row_number)

窗口函数应用场景：

用于分区排序
动态Group By
Top N
累计计算
层次查询

资料：
https://blog.csdn.net/helloxiaozhe/article/details/88043904

Hive（七）Hive分析窗口函数
https://www.cnblogs.com/ZackSun/p/9713435.html

力扣-数据库

1607. 没有卖出的卖家

写一个SQL语句, 报告所有在2020年度没有任何卖出的卖家的名字.

返回结果按照 seller_name 升序排列.

思路：

先查询出2020年度有卖出的卖家id
在Seller中查询第1步中以外的卖家id，就是题目要求的

select seller_name 
from seller
where seller_id not in (
    select seller_id from orders where year(sale_date)=2020
)
order by seller_name;

思路2：
3. 卖家表左连接订单表
4. 联结条件为卖家id相同并且订单日期在2020年
5. 对联结表进行查询，条件为卖家id为null

select s.seller_name
from Seller as s  left join Orders as o 
on o.seller_id=s.seller_id and year(o.sale_date)=2020
where o.seller_id is null
order by s.seller_name;

“2020年”的11种写法
YEAR(sale_date) = 2020
sale_date LIKE '2020%'
sale_date REGEXP '^2020'
LEFT(sale_date,4) = '2020'
MID(sale_date,1,4) = '2020'
SUBSTR(sale_date,1,4) = '2020'
DATE_FORMAT(sale_date,'%Y') = 2020
EXTRACT(YEAR FROM sale_date) = 2020
sale_date BETWEEN '2020-01-01' AND '2020-12-31'
sale_date > '2019-12-31' AND sale_date < '2021-01-01'
sale_date >= '2020-01-01' AND sale_date <= '2020-12-31'

作者：richard-95
链接：https://leetcode-cn.com/problems/sellers-with-no-sales/solution/3chong-jie-fa-not-in-is-null-group_concat-by-richa/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

1495. 上月播放的儿童适宜电影

表: TVProgram
表: Content

写一个 SQL 语句, 报告在 2020 年 6 月份播放的儿童适宜电影的去重电影名.

返回的结果表单没有顺序要求.

思路：
题目要求得到电影名
电影名在表: Content中
条件为 2020年6月份、电影、儿童适宜

联结两张表：

外连接时被联结一侧表中某个字段不存在会被填充为null

select distinct a.title as TITLE
from Content as a left join TVProgram as b 
on a.content_id=b.content_id
where a.content_type='Movies' and a.kids_content='Y' and b.program_date regexp '^2020-06'

1113. 报告的记录

动作表：Actions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    | 
| action        | enum    |
| extra         | varchar |
+---------------+---------+
此表没有主键，所以可能会有重复的行。
action 字段是 ENUM 类型的，包含:('view', 'like', 'reaction', 'comment', 'report', 'share')
extra 字段是可选的信息（可能为 null），其中的信息例如有：1.报告理由(a reason for report) 2.反应类型(a type of reaction)

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/reported-posts
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

编写一条SQL，查询每种报告理由（report reason）在昨天的不同报告数量（post_id）。假设今天是 2019-07-05。

根据报告理由进行分组，从分组后的数据进行筛选
筛选条件：
- 筛选出昨天的的数据行：datediff('2019-07-05',action_date)=1
- extra 不为空：extra is not null
- action =‘report’
使用count统计post.id的数量

select extra as report_reason,count(distinct a.post_id) as report_count 
from Actions as a 
where datediff('2019-07-05',action_date)=1
    and extra is not null
    and action ='report'
group by report_reason;

1084. 销售分析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 表的外键.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

需要的字段出自两个表，需要用到联结
从连接表中取出product_id相同的行

依据product.id进行分组
依据条件2019-01-01、2019-03-31（含）进行最后的筛选

select s.product_id,product_name 
from Sales as  S 
join Product as P
on S.product_id=P.product_id
group by S.product_id
having MIN(sale_date) >='2019-01-01' and max(sale_date)<='2019-03-31'

512. 游戏玩法分析 II

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) 是这个表的两个主键
这个表显示的是某些游戏玩家的游戏活动情况
每一行是在某天使用某个设备登出之前登录并玩多个游戏（可能为0）的玩家的记录
请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：

问题中的条件在一张表中
min(event_date)代表第一次登录时间
问题中要求得到每一个玩家的信息，所以可以依据player_id对表中的行进行分组
从分组后的数据中得到player_id、第一次登陆时间min(event_date)
将4的结果作为条件得到问题要求的player_id，device_id

select player_id,device_id
from Activity
where (player_id,event_date)
in
(
select  player_id,min(event_date )
from Activity
group by player_id
);

1083. 销售分析 II

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 表的外键.
编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：

问题中问的条件出自两张表，所以需要用到join
联结的条件为Sales.product_id=Product.product_id和product_name为S8、iPhone的行
依据buyer_id对联结表进行分组
从分组后的行中取product_name=S8的行
从这些行中得到buyer_id

select buyer_id
from Sales as S join Product as P
on S.product_id=P.product_id and P.product_name in ('S8','iPhone')
group by S.buyer_id 
having group_concat(distinct P.product_name)='S8'

ps:绿茶确实有提神的效果✔

1280. 学生们参加各科测试的次数

学生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
主键为 student_id（学生ID），该表内的每一行都记录有学校一名学生的信息。
 

科目表: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
主键为 subject_name（科目名称），每一行记录学校的一门科目名称。
 

考试表: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
这张表压根没有主键，可能会有重复行。
学生表里的一个学生修读科目表里的每一门科目，而这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。
 

要求写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/students-and-examinations
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：

问题中需要的信息在三张表中，Students 、Subjects 建立全连接（笛卡尔积）命名为a，此时a表中包含每个学生的科目信息。以a表为准，联结Examinations，这里使用左连接。从联结表中筛选student_id、subject_name相等的行
依据a.student_id,a.student_name,a.subject_name三个字段进行分组
最后进行排序
得到每个学生参加每一门科目测试的次数，count(Exa.subject_name)

select a.student_id,a.student_name,a.subject_name,count(Exa.subject_name) as attended_exams
from (select * from Students as Stu cross join Subjects as Sub) as a 
left join Examinations as Exa 
on a.student_id=Exa.student_id and a.subject_name=Exa.subject_name
group by a.student_id,a.student_name,a.subject_name
order by a.student_id,a.subject_name

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

活动记录表：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 只属于一个用户。
 

请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/user-activity-for-the-past-30-days-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：

根据问题中“截至 2019-07-27（包含2019-07-27），近 30天”可知，可以使用datediff(‘2019-07-27’,activity_date)<30来进行筛选
“每日活跃用户数”，根据activity_date进行分组，使用count统计每个日期下用户的数量，因为可能含有重复数据，故先用distinct去除重复数据

select activity_date as day,count(distinct user_id) as active_users
from Activity as a
where datediff('2019-07-27',activity_date)<30
group by activity_date

1543. 产品名称格式修复

表：Sales

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| sale_id      | int     |
| product_name | varchar |
| sale_date    | date    |
+--------------+---------+
sale_id 是该表主键
该表的每一行包含了产品的名称及其销售日期
因为在 2000 年该表是手工填写的，product_name 可能包含前后空格，而且包含大小写。

写一个 SQL 语句报告：

product_name 是小写字母且不包含前后空格
sale_date 格式为 ('YYYY-MM') 
total 是产品在本月销售的次数
返回结果以 product_name 升序 排列，如果有排名相同, 再以 sale_date 升序 排列。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/fix-product-name-format
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

TRIM:取出空白字符

LEFT：从左侧取前n个字符

lower:大写字母转小写

select 
    lower(trim(product_name)) as PRODUCT_NAME,
    left(sale_date,7) as SALE_DATE,
    count(*) as TOTAL
from
    sales
group by 1,2
order by 1,2

1076. 项目员工II

Table: Project

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
主键是 employee_id。
 

编写一个SQL查询，报告所有雇员最多的项目。

查询结果格式如下所示：

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                |
+-------------+--------+------------------+

Result table:
+-------------+
| project_id  |
+-------------+
| 1           |
+-------------+
第一个项目有3名员工，第二个项目有2名员工。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/project-employees-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

查询结果中只要project_id

思路：
先查询最多雇员数
再查询最多雇员数对应的项目

select project_id
from Project
group by project_id
having 
    count(*) >= all(
        select
            count(*) amount
        from 
            Project
        group by project_id
)

IN、ALL、ANY、SOME

IN、ALL、ANY、SOME的解释
IN:在范围内的值，只要有就true
ALL: 与子查询返回的所有值比较为true 则返回true
ANY:与子查询返回的任何值比较为true 则返回true
SOME:是ANY的别称,很少用
注意：

ALL、ANY、SOME的使用必须要用到比较操作符

619. 只出现一次的最大数字

select 查询中
select * from 空表，会返回null
select * from table where 条件1，如果条件1没有对应的行，会返回空

问题：
你能写一个 SQL 查询语句，找到只出现过一次的数字中，最大的一个数字吗？
如果没有只出现一次的数字，输出 null 。

思路：依据num进行分组，统计每个分组下的行数，就是这个数字的重复次数
当不存在次数为一的情况是查询结果要返回null，所以用select对上面的结果再进行一次查询，得到最大的num值

select max(num) as num 
from (select num
from my_numbers
group by num
having count(num)=1)as t1

表 my_numbers 的 num 字段包含很多数字，其中包括很多重复的数字。

你能写一个 SQL 查询语句，找到只出现过一次的数字中，最大的一个数字吗？

+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 | 
对于上面给出的样例数据，你的查询语句应该返回如下结果：

+---+
|num|
+---+
| 6 |
注意：

如果没有只出现一次的数字，输出 null 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/biggest-single-number
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

597. 好友申请 I：总体通过率

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。

 

表：FriendRequest

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| sender_id      | int     |
| send_to_id     | int     |
| request_date   | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。
表：RequestAccepted

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求通过的日期。
 

写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

提示：

通过的好友申请不一定都在表 friend_request 中。你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。
 

查询结果应该如下例所示：

FriendRequest 表：
+-----------+------------+--------------+
| sender_id | send_to_id | request_date |
+-----------+------------+--------------+
| 1         | 2          | 2016/06/01   |
| 1         | 3          | 2016/06/01   |
| 1         | 4          | 2016/06/01   |
| 2         | 3          | 2016/06/02   |
| 3         | 4          | 2016/06/09   |
+-----------+------------+--------------+

RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
| 3            | 4           | 2016/06/10  |
+--------------+-------------+-------------+

Result 表：
+-------------+
| accept_rate |
+-------------+
| 0.8         |
+-------------+
总共有 5 个请求，有 4 个不同的通过请求，所以通过率是 0.80
 

进阶:

你能写一个查询语句得到每个月的通过率吗？
你能求出每一天的累计通过率吗？

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/friend-requests-i-overall-acceptance-rate
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路
1. 得到总的申请被通过数
2. 得到总的申请数
3. ifnull(A,B),如果A为null，则输出B

ifnull、round的使用

select round(
    ifnull(
        (select count(*) from (
    select distinct requester_id,accepter_id 
    from RequestAccepted
) as a) 
        /
        (select count(*) from (
    select distinct sender_id,send_to_id from FriendRequest
) as b) 
    ,0
    )
    ,2
) as accept_rate

1142. 过去30天的用户活动 II

Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
该表没有主键，它可能有重复的行。
activity_type 列是 ENUM（“ open_session”，“ end_session”，“ scroll_down”，“ send_message”）中的某一类型。
该表显示了社交媒体网站的用户活动。
请注意，每个会话完全属于一个用户。
 

编写SQL查询以查找截至2019年7月27日（含）的30天内每个用户的平均会话数，四舍五入到小数点后两位。我们只统计那些会话期间用户至少进行一项活动的有效会话。

 

查询结果格式如下例所示：

Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 3       | 5          | 2019-07-21    | open_session  |
| 3       | 5          | 2019-07-21    | scroll_down   |
| 3       | 5          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+

Result table:
+---------------------------+ 
| average_sessions_per_user |
+---------------------------+ 
| 1.33                      |
+---------------------------+ 
User 1 和 2 在过去30天内各自进行了1次会话，而用户3进行了2次会话，因此平均值为（1 +1 + 2）/ 3 = 1.33。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/user-activity-for-the-past-30-days-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：
由题可知，平均会话数=总的会话数量/总用户数量，因为有可能重复，所以用distinct去重。
COUNT(distinct session_id) / COUNT(distinct user_id)
保留两位小数，round(XXX,2)
筛选条件为截至2019年7月27日（含）的30天内，datediff('2019-07-27',activity_date)<30

select ifnull(round(COUNT(distinct session_id) / COUNT(distinct user_id),2),0) as average_sessions_per_user
from Activity
where datediff('2019-07-27',activity_date)<30

枪枪枪

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL

分组数据过滤分组：having子句where过滤行having过滤分组having支持所有where操作符优先级：where > group by > having > order by182. 查找重复的电子邮箱select Email from Person group by (Email) having count(Email)>1;if 表达式IF(expr1,expr2,expr3) 如果 expr1 是TRUE (expr1 <> 0
复制链接

扫一扫

专栏目录