sql练习及理解_1

junkaiw · 发表于 2020-12-5 15:54:20

马上注册，结交更多好友，享用更多功能^_^

您需要登录才可以下载或查看，没有账号？立即注册

x

本帖最后由 junkaiw 于 2020-12-6 04:01 编辑

登录/注册后可看大图

目录：
1. 查询表中第n大数据
2. 查询表中不重复信息
3. 足球队对战组合问题

登录/注册后可看大图

1. 查询表中第n大数据

查询表中第n大的数据可分为两个情况：
* 同时查询出多组第二大值 -- 可以用窗口函数
示例表：

登录/注册后可看大图

select * from(
select id, name, salary, rank()over(PARTITION by departmentid order by salary desc) mm
from employee_df ) a
where a.mm = 2

复制代码

查询结果如下：

登录/注册后可看大图

说明：
1. 如果某组数据不存在第二的数据，则不会显示出来
2. 使用rank()over(...）窗口函数，因为rank函数的特性，如果两条数据排名相同，则会同时被查出来。

* 单独查询某个组第二大值，如果不存在则展示null
运用ifnull函数还实现返回null

select ifnull( (select * from table1 order by x desc limit 1 offset 1), null)

复制代码

重点知识整理：

limit 2, 1 和 limit 2 offset 1 的区别是什么？

limit 2, 1为跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据；
limit 2 offset 1从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据， offset后面是从第1条开始读取，即读取第2,3条。

判断是否为null——ifnull函数：

IFNULL(expression, alt_value) 如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。

登录/注册后可看大图

2. 查询表中不重复信息
数据表示例：

登录/注册后可看大图

1.用row_number函数来区分一下相同数据的不同，便于观察

select *, ROW_NUMBER()over() as row_num
from student_04

复制代码

登录/注册后可看大图

2. 使用min聚合函数将groupby后各id最小的数据筛选出来，以外的数据就都是重复的数据

select min(id), a.row_num from(
select *, ROW_NUMBER()over() as row_num
from student_04) a
GROUP BY id,name,course,score）

复制代码

登录/注册后可看大图

3. 足球队对战组合问题
题目：
一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.
数据表示例：

登录/注册后可看大图

首先自连接表，运用mysql gbk编码的形式，可以对字母排序，即a<b，获取可能出现对战的组合

select *
from soccerteam a
cross join soccerteam b
where a.name < b.name

复制代码

登录/注册后可看大图

延伸及思考
1. 通过cross join的自连接后，不可以通过a.name <> b.name来操作，因为会出现重复组合，即a和b对战以及b和a对战都会被记录下来。
2. 如果不是abcd这样的字母没有办法进行排序的时候，可以通过使用窗口函数增加row_number列来进行排列组合。

登录/注册后可看大图

账号		自动登录	找回密码
密码			立即注册

[见证历程] sql练习及理解_1

马上注册，结交更多好友，享用更多功能^_^