postgres FILTER函数实现多重过滤和窗口函数实现分组topn

最新推荐文章于 2024-06-18 14:33:05 发布

Ethan3014

最新推荐文章于 2024-06-18 14:33:05 发布

阅读量1.5k

点赞数

分类专栏：每天一篇技术博客文章标签： sql postgresql

本文链接：https://blog.csdn.net/weixin_45594127/article/details/120809410

版权

背景

假设有一张学生成绩表

create table student_scores (
  id BIGSERIAL PRIMARY KEY,
  name varchar,
  subject varchar,
  exam varchar,
  score int
);

DO $DO$ 
DECLARE 
	name_var student_scores.name%type;
	subject_var student_scores.subject%type;
	exam_var student_scores.exam%type;
	score_var student_scores.score%type;
BEGIN
	FOR name_iter IN 1..10 LOOP 
		FOR subject_iter IN 1..10 LOOP 
			FOR exam_iter IN 1..10 LOOP 
				name_var = 'student_' || name_iter;
				subject_var = 'subject_' || subject_iter;
				exam_var = 'exam_' || exam_i