GAUSSDB-基础sql查询
【摘要】 排名函数rank【无并例排名】select * from(select cno,sno,row_number() OVER(partition by cno ORDER BY nvl(score,0) DESC) rumfrom gaussdb.student ) a where rum<=3;【有并例排名】select * from(select cno,sno,rank() OVER...
排名函数rank
【无并例排名】
select * from(
select cno,sno,row_number() OVER(partition by cno ORDER BY nvl(score,0) DESC) rum
from gaussdb.student
) a where rum<=3;
【有并例排名】
select * from(
select cno,sno,rank() OVER(partition by cno ORDER BY nvl(score,0) DESC) rum
from gaussdb.student
) a where rum<=3;
【有并例排名，不去重复】
select * from(
select cno,sno,dense_rank() OVER(partition by cno ORDER BY nvl(score,0) DESC) rum
from gaussdb.student
) a where rum<=3;
【有并例排名，并去重复】
select distinct cno,sno,rum from(
select cno,sno,dense_rank() OVER(partition by cno ORDER BY nvl(score,0) DESC) rum
from gaussdb.student
) a where rum<=3;
case when 语句
1、查看每门成绩是否大于每门平均成绩
【方法1】
select *,
case when math <= avg(math) over() then '不大于' else '大于' end as gt_avg_math_flag,
case when art <= avg(art) over() then '不大于' else '大于' end as gt_avg_art_flag,
case when phy <= avg(phy) over() then '不大于' else '大于' end as gt_avg_phy_flag
from gaussdb.stu;
【方法2】
select
,
b.math,
a.avg_math,
case when b.math > a.avg_math then '大于平均成绩' else '不大于平均成绩' end as gt_avg_math_flag,
,
a.avg_art,
case when > a.avg_art then '大于平均成绩' else '不大于平均成绩' end as gt_avg_art_flag,
b.phy,
a.avg_phy,
case when b.phy > a.avg_phy then '大于平均成绩' else '不大于平均成绩' end as gt_avg_phy_flag
from (
select
avg(math) avg_math,
avg(art) avg_art,
avg(phy) avg_phy
from gaussdb.stu
) a,gaussdb.stu b
2、id含’3’的同学，求总的绩点，返回绩点最大的ID和总绩点
[0~60) 0绩点，[60~70) 0.1绩点，[70~80) 0.2绩点，[80~90) 0.3绩点， [90~100] 0.4绩点
【方法1】
select id,
(case when math<=59 then 0
when math<=69 then 0.1
when math<=79 then 0.2
when math<=89 then 0.3
when math<=100 then 0.4 else 0 end)
+
(case when art<=59 then 0
when art<=69 then 0.1
when art<=79 then 0.2
when art<=89 then 0.3
when art<=100 then 0.4 else 0 end)
+
(case when phy<=59 then 0
when phy <=69 then 0.1
when phy<=79 then 0.2
when phy<=89 then 0.3
when phy<=100 then 0.4 else 0 end) as max_jidian
from gaussdb.stu where id like '%3%'
order by max_jidian desc limit 1;
【方法2】
SELECT id,p_sum FROM
(
SELECT id,
(CASE WHEN math<=59 THEN 0
WHEN math<=69 THEN 0.1
WHEN math<=79 THEN 0.2
WHEN math<=89 THEN 0.3
WHEN math<=100 THEN 0.4
ELSE 0
END )+(
CASE WHEN art<=59 THEN 0
WHEN art<=69 THEN 0.1
WHEN art<=79 THEN 0.2
WHEN art<=89 THEN 0.3
WHEN art<=100 THEN 0.4
ELSE 0
END)+(
CASE WHEN phy<=59 THEN 0
WHEN phy<=69 THEN 0.1
WHEN phy<=79 THEN 0.2
WHEN phy<=89 THEN 0.3
WHEN phy<=100 THEN 0.4
ELSE 0
END) AS p_sum,
DENSE_RANK() OVER(ORDER BY p_sum desc) AS p_rank
FROM gaussdb.stu WHERE id LIKE '%3%'
) WHERE p_rank=1;
3、求总绩点，返回绩点最大的ID和总绩点
【方法1】
select id,
(case when math<=59 then 0
when math<=69 then 0.1
when math<=79 then 0.2
when math<=89 then 0.3
when math<=100 then 0.4 else 0 end)
+
(case when art<=59 then 0
when art<=69 then 0.1
when art<=79 then 0.2
when art<=89 then 0.3
when art<=100 then 0.4 else 0 end)
+
(case when phy<=59 then 0
when phy <=69 then 0.1
when phy<=79 then 0.2
when phy<=89 then 0.3
when phy<=100 then 0.4 else 0 end) as max_jidian
from gaussdb.stu order by max_jidian desc limit 1;
【方法2】
SELECT id,p_sum FROM (
SELECT id,
(CASE WHEN math<=59 THEN 0
WHEN math<=69 THEN 0.1
WHEN math<=79 THEN 0.2
WHEN math<=89 THEN 0.3
WHEN math<=100 THEN 0.4
ELSE 0
END )+(
CASE WHEN art<=59 THEN 0
WHEN art<=69 THEN 0.1
WHEN art<=79 THEN 0.2
WHEN art<=89 THEN 0.3
WHEN art<=100 THEN 0.4
ELSE 0
END)+(
CASE WHEN phy<=59 THEN 0
WHEN phy<=69 THEN 0.1
WHEN phy<=79 THEN 0.2
WHEN phy<=89 THEN 0.3
WHEN phy<=100 THEN 0.4
ELSE 0
END) AS p_sum,
DENSE_RANK() OVER(ORDER BY p_sum desc) AS p_rank
FROM gaussdb.stu ) WHERE p_rank=1;
4、按照总绩点输出
select id,
(case when math<=59 then 0
when math<=69 then 0.1
when math<=79 then 0.2
when math<=89 then 0.3
when math<=100 then 0.4 else 0 end)
+
(case when art<=59 then 0
when art<=69 then 0.1
when art<=79 then 0.2
when art<=89 then 0.3
when art<=100 then 0.4 else 0 end)
+
(case when phy<=59 then 0
when phy <=69 then 0.1
when phy<=79 then 0.2
when phy<=89 then 0.3
when phy<=100 then 0.4 else 0 end) as totaljidian
from stu;
字符拼接
1、请查询姓名和姓氏，以姓名·姓氏的格式输出，要求首字母大写，姓名和姓氏直接使用”·”拼接
【方法1】
select initcap(firstname ||'·'||fmailyname ) from gaussdb.su;
【方法2】
select initcap(concat(firstname,'·',fmailyname )) from gaussdb.su;
2、插入一条新数据（1,’tom’,’jerry’,’tom’,’H’,63），当出现主键冲突时，将mark改为’F’
id列是PK，不允许重复，下面这个插入失败
insert into gaussdb.su values (1,'tom','jerry','tom','H',63) ;
--下面操作会成功，它并不是插入1条新记录，而是把表中现有的id=1的记录中，mark列的值改为F
insert into gaussdb.su values (1,'tom','jerry','tom','H',63) on duplicate key update mark = 'F';
3、查询表，检查姓名是否是sec开头，展示姓名，判断结果result。
select
firstname,
case when firstname like 'sec%' then 'True' else 'False' end as result
from gaussdb.su;
4、查询表中所有列的数据，按照成绩进行排序，并显示名次（position），名次为连续的。要求展示所有字段，名次字段为position。
select * ,dense_rank() over (order by score desc) as postition from gaussdb.su;
join查询
1、求math、phy总成绩以及art、m2的总成绩：
【求每个学生自己的，如果有多条，会分组累加】
SELECT student_id ,sum(math+phy) AS math_phy ,sum(art+m2) AS art_m2 FROM gaussdb.student GROUP BY student_id;
【求每行条记录自己的，如果student_id有重复，不会累加】
SELECT student_id,math+phy AS math_phy,art+m2 AS art_m2 FROM gaussdb.student;
【求所有人总的】
SELECT sum(math+phy) AS math_phy ,sum(art+m2) AS art_m2 FROM gaussdb.student;
2、根据维度表、安装两种加权算法算出每个学生的加权成绩，展示包含student_id,weitht_sum，单个学生加权成绩可以两行输出
【方法1】
SELECT student_id,(s.math*w.math+s.phy*w.phy+*+s.m2*w.m2) AS weitht_sum FROM student s,weight w;
【方法2】
SELECT student_id,s.math*w1.math+s.phy*w1.phy+*+s.m2*w1.m2 as weight_sum FROM student s,weight w1 WHERE w1.weight_no =1
UNION ALL
SELECT student_id,s.math*w2.math+s.phy*w2.phy+*+s.m2*w2.m2 as weight_sum FROM student s,weight w2 WHERE w2.weight_no =2
ORDER BY student_id;
3、根据维度表，安装两种加权算法计算出每个学生的加权成绩，展示包含student_id,weight_sum，单个学生加权成绩要求一行输出
【方法1】
SELECT student_id,
s.math*w1.math+s.phy*w1.phy+*+s.m2*w1.m2 AS weitht_sum1,
s.math*w2.math+s.phy*w2.phy+*+s.m2*w2.m2 AS weitht_sum2
FROM student s
inner JOIN weight w1 ON w1.weight_no =1
inner JOIN weight w2 ON w2.weight_no =2;
【方法2】
SELECT student_id,
s.math*w1.math+s.phy*w1.phy+*+s.m2*w1.m2 AS weitht_sum1,
s.math*w2.math+s.phy*w2.phy+*+s.m2*w2.m2 AS weitht_sum2
FROM student s,
(SELECT * FROM weight w WHERE w.weight_no=1 ) AS w1,
(SELECT * FROM weight w WHERE w.weight_no=2 ) AS w2;
4、对两种加权总成绩进行排序。要求输出格式student_id,weight_sum、rank1、weight2_sum、rank2
【方法1】
SELECT student_id,
s.math*w1.math+s.phy*w1.phy+*+s.m2*w1.m2 AS weitht_sum1,
dense_rank() OVER(ORDER BY weitht_sum1 DESC ) AS rank1,
s.math*w2.math+s.phy*w2.phy+*+s.m2*w2.m2 AS weitht_sum2,
dense_rank() OVER(ORDER BY weitht_sum2 DESC ) AS rank2
FROM student s
inner JOIN weight w1 ON w1.weight_no =1
inner JOIN weight w2 ON w2.weight_no =2;
【方法2】
SELECT student_id,
s.math*w1.math+s.phy*w1.phy+*+s.m2*w1.m2 AS weitht_sum1,
dense_rank() OVER(ORDER BY weitht_sum1 DESC ) AS rank1,
s.math*w2.math+s.phy*w2.phy+*+s.m2*w2.m2 AS weitht_sum2,
dense_rank() OVER(ORDER BY weitht_sum2 DESC ) AS rank2
FROM student s,
(SELECT * FROM weight w WHERE w.weight_no=1 ) AS w1,
(SELECT * FROM weight w WHERE w.weight_no=2 ) AS w2;
查询分组 group by
2、由于每栋楼各个年龄段的人都有，故按age年龄段分组[0-18）,[18-35),[35-55),[55-maxvalues)形成age_group，每个组命名group1,group2,group3,group4
select * ,
case
when age<18 then 'group1'
when age >= 18 and age<35 then 'group2'
when age >= 35 and age<55 then 'group3'
else 'group4'
end as age_group
from residents;
3、age_group按每组人数多少排序，查询出age_group、人数、最大年龄、最小年龄、平均年龄（平均年龄向下取整）
【方法1】
SELECT ag.age_group,
count(*) AS '人数',
max(ag.age) AS '最大年龄',
min(ag.age) AS '最小年龄',
floor(avg(ag.age)) AS '平均年龄'
FROM
(
SELECT age,sex,building,CASE
WHEN r.age<18 THEN 'group1'
WHEN r.age<35 THEN 'group2'
WHEN r.age<55 THEN 'group3'
WHEN r.age>=55 THEN 'group4'
END AS age_group
FROM RESIDENTS r ) AS ag GROUP BY age_group;
【方法2】
select
case
when age<18 then 'group1'
when age >= 18 and age<35 then 'group2'
when age >= 35 and age<55 then 'group3'
else 'group4'
end as age_group,
count(*) as "人数",
max(age) as "最大年龄",
min(age) as "最小年龄",
floor(avg(age)) as "平均年龄"
from residents
group by age_group
order by "人数" desc;
4、由于需要每天要送食物，增加如下每个年龄段所需食物营养价值表，需要统计出该小区每天总营养值
select
sum(
case
when age<18 then 5
when age >= 18 and age<35 then 7
when age >= 35 and age<55 then 6
else 5 end
) as "每天总营养值"
from
residents;
【如果需要计算每个人需要的总营养值如下】
select age,
case
when age<18 then 'group1'
when age >= 18 and age<35 then 'group2'
when age >= 35 and age<55 then 'group3'
else 'group4'
end as age_group,
case
when age_group = 'group1' then 5
when age_group = 'group2' then 7
when age_group = 'group3' then 6
when age_group = 'group4' then 5
end as nutrition_value
from
residents;
5、按楼栋求出每栋楼所需营养值
select building,
sum(
case
when age<18 then 5
when age >= 18 and age<35 then 7
when age >= 35 and age<55 then 6
else 5 end
) as "每栋楼所需营养价值"
from residents
group by building;
查询平均值比较等
1、输出每月月考总分都比学号为5的同学分数高的所有学生信息。
【方法1】
select a.*
from student a, (select month, score from student where sno=5) b
where a.month=b.month and coalesce(a.score,0) > coalesce(b.score,0) and a.sno<>5;
【方法2】
select a.*
from student a
inner join (select month,nvl(score,0) as fscore from student where sno=5) b
on a.month=b.month
where nvl(a.score,0) > b.fscore and a.sno <>5;
【方法3】
select a.*
from student a,
(select month,nvl(score,0) as fscore from student where sno=5) b
where a.month=b.month and nvl(a.score,0) > b.fscore and a.sno <>5;
【方法4】
with temp as (select month,nvl(score,0) as nscore from student where sno=5)
select s.*
from student s
inner join temp t on t.month=s.month
where nvl(s.score,0) > nscore and sno<>5;
【方法5】
select sno,sname,t1.month,allscore
from
(select month,sno,sname,sum(nvl(score,0)) allscore from student group by month,sno,sname) t1
join (select month,sum(nvl(score,0)) allscore2 from student where sno=5 group by month) t2 on t1.month=t2.month
where allscore>allscore2
order by sno;
【方法6】
SELECT DISTINCT sno,sname,cno
FROM student sst
WHERE sst.sno NOT IN
(
SELECT s.sno FROM student s LEFT JOIN
(
SELECT * FROM student t WHERE t.sno =5
) AS st_5 ON s.MONTH=st_5.MONTH WHERE NVL(st_5.score,0)>=NVL(s.score,0)
);
【方法7】
SELECT DISTINCT s1.sno, s1.sname, s1.cno
FROM student s1
WHERE s1.sno NOT IN (
SELECT s1.sno
FROM student s1
JOIN student s2
ON s2.sno = 5 AND s1.month = s2.month
WHERE nvl(s1.score, 0) <= nvl(s2.score, 0)
);
2、输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数。
select month,sname,cno,count(*) AS "缺考次数" from student where score is null group by month,sname,cno;
3、输出每次月考都和Tom同时缺考的所有学生信息，要求打印学号、姓名和月考总分。
【方法1】
select sno,sname,sum(nvl(score,0)) as total_score
from student
where sno in
(
select a.sno from student a
inner join (select sno,month from student where sname='Tom' and score is null) b
on a.month=b.month and a.sno <> b.sno
where a.score is null
)
group by sno,sname;
【方法2】
select sno,sname,sum(nvl(score, 0)) as total_score
from student
where (sno,sname) in
(
select sno,sname from student
where month in (select month from student where sname = 'Tom' and score is null)
and score is null
and sname <> 'Tom'
)
group by sno,sname;
【方法3】
select s.sno,s.sname,sum(nvl(score,0)) as total_score
from student s
where sno in
(
select a.sno from student a,
(select sno,month from student where sname = 'Tom' and score is null) b
where
a.month = b.month
and a.sno <> b.sno
and a.score is null
)
group by s.sno,s.sname;
4、输出全校月考中位数分数。
【方法1】
--GaussDB主备版支持median函数，分布式版不支持
select median(nvl(score,0)) from student;
【方法2】
select
round(avg(score),2)
from
(
select score,count(*) over() as count,row_number () over (order by nvl(score,0)) as rowno from student
)
where
case
when count%2 = 0 then rowno = count / 2 or rowno = count / 2 + 1
else rowno =(count + 1)/ 2
end;
【方法3】
select
round(avg(score),0)
from
(
select score from student
order by nvl(score,0)
limit (select ceil(count(*)/2) from student),
case
when (select count(*)%2 from student)= 0 then 2
else 1
end
);
【方法4】
SELECT avg(NVL(score,0))
FROM (
SELECT score,
ROW_NUMBER() OVER (ORDER BY NVL(score,0)) AS row_num,
COUNT(*) OVER () AS total_count
FROM student
) AS ranked_scores
WHERE row_num in (floor((ranked_scores.total_count+1)/2),floor((ranked_scores.total_count+2)/2));
【方法5】
按月分组统计
with temp as
(
select month,
nvl(score,0) as nscore,
row_number() over(partition by month order by nscore) as rn,
count(*) over(partition by month) as cn
from
student
)
select
month,
avg(nscore) as avg_score
from temp
where
(case
when cn%2 = 1 then rn =(cn + 1)/ 2
else rn = cn / 2 or rn = cn / 2 + 1
end)
group by month;
【方法6】
按月分组统计
select month,avg(mscore) as medianscore
from
(select month,mscore
from
(select month,
nvl(score,0) as mscore,
row_number() over(partition by month order by mscore) as cn,
count(*) over(partition by month) as scount
from
student) a
where
case
when scount%2 = 0 then (cn = scount / 2 or cn = scount / 2 + 1)
else cn =(scount + 1)/ 2
end ) b
group by month;
【方法7】
按月分组统计
SELECT month,avg(monthly_score) as median_score
FROM (
SELECT month,monthly_score
FROM (
SELECT month, sno, COALESCE(score, 0) monthly_score,
ROW_NUMBER() OVER (PARTITION BY month ORDER BY monthly_score) AS row_num,
COUNT(*) OVER (PARTITION BY month) AS total_students
FROM student
)
WHERE row_num = FLOOR(total_students / 2) + 1 OR row_num = FLOOR((total_students + 1) / 2)
)
GROUP BY month;
5、统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分数。
【方法1】
select cname,month,max(nvl(score,0))
from student,class
where student.cno=class.cno
group by cname,month
order by cname,month;
【方法2】
select cname,month,max(nvl(score,0)) as max_score
from student s inner join class c on c.cno = s.cno
group by cname,month
order by cname,month;
【方法3】
select
a.cname,a.month,a.score
from
(
select
cname,
month,
score,
rank() over (partition by s.cno,month order by coalesce(score,0) desc) as ranking
from
student s join class c on s.cno = c.cno
) a
where a.ranking = 1;
【注意】:要考虑到NULL值的影响, 统计数据时应该将其视为0
6、查看每个班级(cno)月考总分(score)前三名，其中要求分数相同的人具有相同的编号
【方法1】
select * from
(
select *,rank() over(partition by cno order by nvl(score,0) desc) as rn
from student
)
where rn <= 3;
【方法2】
select * from
(
select *,dense_rank() over(partition by cno order by nvl(score,0) desc) as drk
from student
)
where drk <= 3;
其他示例
1、输出class1班级中比class2班级每月月考最低分还低的学生信息，要求打印学号、姓名和月考总分。
select sno,sname,score from student s
inner join
class c on c.cno = s.cno
inner join
(
select month,min(score) as minscore from student s
inner join class c on c.cno = s.cno
where c.cname = 'class2'
group by month
) s2
on s.month = s2.month
where c.cname = 'class1' and score < s2.minscore;
2、打印月考总分平均分最高的学生信息，输出学号，姓名和月考总分平均分。
【方法1】
select sno,sname,avg(COALESCE(score, 0)) as avg_score
from student
group by sno,sname
order by avg_score desc limit 1;
【方法2】
select sno,sname,avgscore
from
(select sno,sname,
avg(nvl(score, 0)) as avgscore,rank() over(order by avgscore desc) as rank
from student
group by sno,sname) s
where rank = 1;
3、输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学号、姓名、月考平均分和差距分数。
【方法1】
select
sno,
sname,
avgscore,
maxscore-avgscore as diffscore
from
(select
sno,
sname,
round(avg(nvl(score, 0)),2) as avgscore,
max(avgscore) over() as maxscore
from student
group by sno,sname) t;
【方法2】
select sno,sname,
round(avg(nvl(score, 0)),2) avgscore,
(
select max(avgscore)
from
(select round(avg(nvl(score, 0)),2) avgscore from student group by sno)
)-avgscore m_score
from student
group by sno,sname;
【方法3】
SELECT sno,sname,avg(NVL(s.score,0)),
(select avgscore
from
(
select sno,sname,
avg(nvl(score, 0)) as avgscore,rank() over(order by avgscore desc) as rank
from student
group by sno,sname
) s
where rank = 1
)-avg(NVL(s.score,0)) AS '分数差'
FROM student s GROUP BY s.sno,s.sname
1、求各科分数是否大于平均分
SELECT student_id,
CASE WHEN g.math> avg(NVL(g.math,0)) over() THEN 't' ELSE 'f'END AS math ,
CASE WHEN g.phy> avg(NVL(g.phy,0)) over() THEN 't' ELSE 'f'END AS phy ,
CASE WHEN g.music> avg(NVL(g.music,0)) over() THEN 't' ELSE 'f'END AS music ,
CASE WHEN g.art> avg(NVL(g.art,0)) over() THEN 't' ELSE 'f'END AS art
FROM grades g;
2、求学号含3的学生的平均成绩，打印平均成绩最高的学号和平均成绩
select student_id,avg_score from (
select *,
(nvl(math,0)+nvl(phy,0)+nvl(music,0)+nvl(art,0))/4 avg_score,
row_number() over(order by avg_score desc) rn
from grades where student_id like '%3%'
) where rn=1;
3、求总绩点并按绩点排名
select student_id,
(case
when nvl(math,0)<60 then 0
when nvl(math,0)<70 then 0.1
when nvl(math,0)<80 then 0.2
when nvl(math,0)<90 then 0.3
else 0.4 end
+
case
when nvl(phy,0)<60 then 0
when nvl(phy,0)<70 then 0.1
when nvl(phy,0)<80 then 0.2
when nvl(phy,0)<90 then 0.3
else 0.4 end
+
case
when nvl(music,0)<60 then 0
when nvl(music,0)<70 then 0.1
when nvl(music,0)<80 then 0.2
when nvl(music,0)<90 then 0.3
else 0.4 end
+
case
when nvl(art,0)<60 then 0
when nvl(art,0)<70 then 0.1
when nvl(art,0)<80 then 0.2
when nvl(art,0)<90 then 0.3
else 0.4 end
) as total_point
from grades
order by total_point desc;
1、对学习math和pysical排名前十的学生，art加5分，求所有学生总成绩
【方法1】
with stu as
(
select
distinct student_id
from
(
select
student_id,
math + pysical as total_score,
rank() over(order by total_score desc) as rk
from student)
where
rk <= 10
)
select
student_id,
sum(math + pysical + arts + music)
from
(
select
s.student_id,
math,
pysical,
case
when s.student_id = stu.student_id then art + 5
else art
end as arts,
music
from
student s left join stu on stu.student_id = s.student_id
) group by student_id;
【方法2】
select
student_id,
sum(nmath + npysical + arts + nmusic) as tscore
from
(
select
s.student_id,
nvl(math,0) as nmath,
nvl(pysical,0) as npysical,
case
when s.student_id = mp.student_id then nvl(art,0)+ 5
else nvl(art,0)
end as arts,
nvl(music,0) as nmusic
from
student s left join
(
select distinct student_id
from
(
select
student_id,
nvl(math,0)+ nvl(pysical,0) as mpscore,
row_number() over(order by mpscore desc) as rn
from student
) where rn <= 10
) mp on s.student_id = mp.student_id
) group by student_id;
【方法3】
--每个学生sum(math+pysical)之和是前10的学生，每行记录art都+5
SELECT student_id,
sum(NVL(math,0)+NVL(pysical,0)+NVL(music,0)+NVL(art_5,0))
FROM
(
SELECT *,
CASE
WHEN s.student_id IN
(
SELECT DISTINCT student_id FROM
(
SELECT student_id,
sum(s.math+s.pysical) AS sum_mp,
DENSE_RANK() OVER(ORDER BY sum_mp DESC ) AS rank
FROM student s GROUP BY s.student_id
) mp_id WHERE mp_id.rank<=10
) THEN NVL(,0)+5
ELSE nvl(,0)
END AS art_5
FROM student s
) AS aa GROUP BY student_id;
【方法4】
--math与和pysical之和是前10的记录，前10中的每行记录art都+5
select student_id,
sum(case
when rn<=10 then math+pysical+art+music+5
else math+pysical+art+music
end) total_score from
(
select
*,
row_number() over(order by (math+pysical) desc) rn
from student
) ss
group by student_id
2、获取art和music排名前十，同时math和pysical在art和music前十名的学生信息
【方法1】
--不考虑排名并列
select s1.*,s2.mscore
from
(
select *,(art + music) ascore from student order by ascore desc limit 10
) s1 join
(
select *,(math + pysical) mscore from student order by mscore desc limit 10
) s2
on s1.student_id = s2.student_id;
【方法2】
--考虑排名并列
select
distinct s.*
from
student s
inner join
(
select student_id from
(
select
student_id,
nvl(math,0)+ nvl(pysical,0) as mpscore,
row_number() over(order by mpscore desc) as rn
from student
)
where rn <= 10
) mp on s.student_id = mp.student_id
inner join
(
select student_id from
(
select
student_id,
nvl(art,0)+ nvl(music,0) as mpscore,
row_number() over(order by mpscore desc) as rn
from student
)
where rn <= 10
) am on s.student_id = am.student_id
order by student_id;
【方法3】
--每个学生sum(math+pysical)之和是前10的学生，同时 每个学生sum(art+music)之和是前10的学生
SELECT distinct s.*
FROM student s
WHERE s.student_id IN
(
SELECT a.student_id FROM
(
SELECT DISTINCT student_id FROM
(
SELECT student_id,
sum(NVL(,0)+NVL(s.music,0)) AS sum_am,
DENSE_RANK() OVER(ORDER BY sum_am DESC ) AS rank
FROM student s GROUP BY s.student_id
) am_id WHERE am_id.rank<=10
) a inner JOIN
(
SELECT DISTINCT student_id FROM
(
SELECT student_id,
sum(NVL(s.math,0)+NVL(s.pysical,0)) AS sum_mp,
DENSE_RANK() OVER(ORDER BY sum_mp DESC ) AS rank
FROM student s GROUP BY s.student_id
) mp_id WHERE mp_id.rank<=10
) b ON a.student_id=b.student_id
);
【方法4】
--math与和pysical之和是前10的记录，同时 art与和music之和是前10的记录
select s1.student_id,s1.math,s1.pysical,,s1.music from
(select
*,
row_number() over(order by (math+pysical) desc) rn
from student
) s1 ,
(
select
*,
row_number() over(order by (art+music) desc) rn
from student
) s2
where s1.student_id=s2.student_id and s1.rn<=10 and s2.rn<=10
2、查询202202班级同一科目成绩比202201班级最高分高的同学
【方法1】
select * from test t1 where t1.class=202202 and grade >
(
select max(grade) from test t2
where t2.class=202201 and t2.kemu=t1.kemu
);
【方法2】
select * from test t1 join
(
select kemu,max(grade) as t2_max_grade
from test
where class = 202201
group by kemu
) t2
on t1.kemu = t2.kemu
and t1.grade > t2.t2_max_grade
and t1. class = 202202;
1、查202201班级和202202班级所有人语文成绩前10的记录，第一个查询要使用union
【方法1】
select * from
(
select * from score1 where kemu='chinese'
union
select * from score2 where kemu='chinese'
) t order by Grade desc limit 10;
【方法2】
select * from
(
select *,row_number() over(order by Grade desc) rn from
(
select * from score1 where kemu='chinese'
union
select * from score2 where kemu='chinese'
)
) t where rn<=10;
【方法3】
select * from
(
select *,rank() over(order by Grade desc) rn from
(
select * from score1 where kemu='chinese'
union
select * from score2 where kemu='chinese'
)
) t where rn<=10;
【方法4】
select * from
(
select *,dense_rank() over(order by Grade desc) rn from
(
select * from score1 where kemu='chinese'
union
select * from score2 where kemu='chinese'
)
) t where rn<=10;
2、查看两个班级相同的科目，202201班在score2中不存在的成绩，要求使用not in
【判断某一科目】
select * from score1 where kemu='chinese' and grade not in (select grade from score2 where kemu='chinese');
【判断所有科目】
select * from score1 s1 where grade not in (select grade from score2 s2 where s2.kemu=s1.kemu);
3、对以上SQL语句进行改写为not exits
【判断某一科目】
select * from score1 t1 where t1.kemu='chinese' and not exists (select grade from score2 t2 where t2.kemu='chinese' and t1.grade=t2.grade) ;
【判断所有科目】
select * from score1 t1 where not exists (select grade from score2 t2 where t2.kemu=t1.kemu and t1.grade=t2.grade);
4、查询202201班级的学生的成绩比202202班级的学生最高成绩还要大的学生信息
【方法1】
select sno ,totalscore1
from
(select sno ,Grade as totalscore1 from score1) s1,
(select max(Grade) as totalscore2 from score2) s2
where totalscore1>totalscore2;
【方法2】
select * from score1 where Grade>(
select max(Grade) as totalscore2 from score2
);
1、查询2020学年，语文的平均成绩大于80的班级，打印班级名称及平均成绩，要求where条件里有两个非相关子查询
【方法1】
select c_name,
avg(score) as avgscore
from scclassore s,
class c
where s.cno = c.cno
and course_no in (select course_no from course where course_name = '语文')
and s.cno in (select cno from class where xuenian = '2020')
group by c_name
having avgscore >80;
【方法2】
select c.c_name,
avg(s.score) as avg_score
from scclassore s
inner join class c
on c.cno = s.cno
where s.cno in (select cno from class where xuenian = '2020')
and s.course_no in (select course_no from course where course_name = '语文')
group by c.c_name
having avg_score>80;
2、优化上一步的语句，将where条件中的非相关子查询 改为from后边的范围表
【方法1】
select c_name ,avg(score) avg_score
from scclassore ss,
(select cno,c_name from class where xuenian='2020') cl,
(select course_no from course where course_name='语文') co
where cl.cno=ss.cno and ss.course_no =co.course_no
group by c_name
having avg_score>80
【方法2】
select c_name,avg_score
from class t3 ,
(
select t1.cno,avg(t1.score) as avg_score
from scclassore t1
where
t1.course_no in (select course_no from course t2 where t2.course_name = '语文')
and t1.cno in (select cno from class where xuenian = '2020')
group by t1.cno
having avg_score>80
) t4
where t3.cno = t4.cno;
【方法3】
select e.c_name,d.avg
from class e,
(
select t1.cno,avg(t1.score) avg
from scclassore t1,course t2,
(select cno from class where xuenian = '2020') t3,
(select course_no from course where course_name = '语文') t4
where t1.course_no = t2.course_no
and t1.cno = t3.cno
and t2.course_no = t4.course_no
group by t1.cno
having avg(t1.score)>80
) d
where e.cno = d.cno;
3、优化上一步的语句，将from后边的子查询优化为父表的关联查询
【方法1】
select
c_name,
avg(score) as avgscore
from
scclassore s,
class c,
course cs
where
s.cno = c.cno
and s.course_no = cs.course_no
and course_name = '语文'
and xuenian = '2020'
group by c_name
having avgscore >80;
【方法2】
select
c_name,
avg(score) as avgscore
from scclassore s
inner join class c on s.cno = c.cno
inner join course cs on s.course_no = cs.course_no
where course_name = '语文' and xuenian = '2020'
group by c_name
having avgscore >80;
1、查找202401班同科目的分数不在202402班级中，显示202401班级的所有成绩信息
【方法1】
select * from score1 s1 where grade not in (
select grade from score1 s2 where s1.course_name=s2.course_name
)
【方法2】
select s1.* from score1 s1,student st
where s1.student_id=st.student_id and st.cno=202401 and s1.grade not in (
select grade
from score2 s2,student st
where s2.student_id=st.student_id and st.cno=202402 and s1.course_name = s2.course_name);
2、在不改变上题结果的情况下，对sql进行改写
select s1.* from score1 s1,student st
where s1.student_id=st.student_id and st.cno=202401 and not exists (
select grade
from score2 s2,student st
where s2.student_id=st.student_id and st.cno=202402 and s1.course_name = s2.course_name and s1.grade = s2.grade);
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）