1 填坑
之前写过一篇文章:SQL | 窗口函数 row number + partition by 排序
在文章的最后,有个代码当时没调出来:
为什么当时不行呢?今天在公司内部的数据平台上建了个临时表试验了一波,并且请教了带我的leader,终于发现了原因!
首先,建立临时表有一些总结的地方:
- 注意加前缀tmp 因为是临时表
- 其次,列名必须是英文的
- 最后,如果txt数据导入的时候有中文的话,得先转为utf-8格式的,否则导入成中文就会乱码!
言归正传,为啥不行呢?因为上面写的SQL语句有一个小问题!什么问题呢?!
新生成的rank列无法直接当时就调用where来筛选!
所以正确的代码应该是这样:
select *
from
(
select *,
row_number() over (partition by name order by time desc) rank
from IELTS
) a
where rank <= 1
这样就ok了,那这个和上面的代码有什么区别么?必须有啊!
就是新生成rank列之后得把这个包起来,新生成一个表,然后再调用这个表!否则刚新建rank列之后马上用where筛选时rank还不在表里!
所以原始数据长这个样子:
通过上述SQL代码跑通之后的结果为:
成功达到了我们想要的目的!完美!
2 再举个例子
如果对上述例子还有疑惑的话,我们再举个简单的例子!要说明的核心问题就是SQL新生成一列之后不能马上用where过滤 可以在外面套一层select语句即可,同时注意区别改变一列名称和新生成一列的区别。
2.1 数据
现在有一个score表:
2.2 需求
新生成一列,id+grade(忽略实际意义,这都是啥价啥嘛…但明白原理是最重要的)并且筛选出新列值小于990的所有记录。
2.3 实现
一开始的代码就是这样:
select *, (stu_id + grade) as new_col
from score
where new_col <= 990```
那能出结果吗?遗憾的是,报错了!
报错的信息大家都看懂了,就是没有找到新列:new_col 所以也就无法进行where筛选操作了!
所以就得按照上述填坑的方式重写下代码,也简单,就是外面套一层select!
select * FROM
(
select *, (stu_id + grade) as new_col
from score
)a
where new_col <= 990
结果是啥样的呢?
大功告成,新列也符合条件!撒花庆祝!
2.4 注意
有时候我们也会看到这种语句:
select c_name, max(grade) as max_gra from score
group by c_name ORDER BY max_gra DESC
诶,我就会问,你看,这个max_gra不就是新生成的么,咋还马上就用到了后续的操作(order by)?有返回结果吗?
首先结果肯定是有的:
但这里值得注意的就是,并不是在原有的表score中新生成一列,而是相当于一个查询语句,出一个查询结果!所以这就无所谓了,而且也可以理解为重命名grade这一列!本来是grade 变为了max_grade 只不过是取最大了而已并且groupby+order
今天分享就到这了~4月,一起加油!