sql练习及理解_1
本帖最后由 junkaiw 于 2020-12-6 04:01 编辑static/image/hrline/1.gifstatic/image/hrline/1.gif
目录：
1. 查询表中第n大数据
2. 查询表中不重复信息
3. 足球队对战组合问题
static/image/hrline/1.gifstatic/image/hrline/1.gif
1. 查询表中第n大数据
查询表中第n大的数据可分为两个情况：
* 同时查询出多组第二大值 -- 可以用窗口函数
示例表：
https://xxx.ilovefishc.com/forum/202012/05/031135nc3b0iz6o5zcog36.png
select * from(
select id, name, salary, rank()over(PARTITION by departmentid order by salary desc) mm
from employee_df ) a
where a.mm = 2
查询结果如下：
https://xxx.ilovefishc.com/forum/202012/05/031216xdktvjf3j37d0mjj.png
说明：
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，则返回第二个参数的备用值。
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
2. 查询表中不重复信息
数据表示例：
https://xxx.ilovefishc.com/forum/202012/05/085636kqx98k7j9ajj117x.png
1.用row_number函数来区分一下相同数据的不同，便于观察
select *, ROW_NUMBER()over() as row_num
from student_04
https://xxx.ilovefishc.com/forum/202012/05/085723owfmw7tfto1tzfvo.png
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）
https://xxx.ilovefishc.com/forum/202012/05/085703njxzz0m7ugx0y00i.png
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
3. 足球队对战组合问题
题目：
一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.
数据表示例：
https://xxx.ilovefishc.com/album/202012/05/095630p4oy01q01881y1y3.png
首先自连接表，运用mysql gbk编码的形式，可以对字母排序，即a<b，获取可能出现对战的组合
select *
from soccerteam a
cross join soccerteam b
where a.name < b.name
https://xxx.ilovefishc.com/album/202012/05/095648zg59zgm3q1960cvq.png
延伸及思考
1. 通过cross join的自连接后，不可以通过a.name <> b.name来操作，因为会出现重复组合，即a和b对战以及b和a对战都会被记录下来。
2. 如果不是abcd这样的字母没有办法进行排序的时候，可以通过使用窗口函数增加row_number列来进行排列组合。
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
页:
[1]