-
前言：因為hive支持開窗函數(shù)的緣故，會在某些場景比mysql省很多事仇冯，該篇以網(wǎng)上流傳的50道SQL題為例畏浆，比較SQL和HQL的實現(xiàn)。21題難度最大。
附上萬能圖解：
Join圖
一垮斯、數(shù)據(jù)表介紹
1.學(xué)生表
student(sid,sname,sage,ssex)
--sid 學(xué)生編號,sname 學(xué)生姓名,sage 出生年月,ssex 學(xué)生性別
2.課程表
course(cid,cname,tid)
--cid課程編號,cname課程名稱,tid教師編號
3.教師表
teacher(tid,tname)
--tid 教師編號,tname 教師姓名
4.成績表
sc(sid,cid,score)
--sid學(xué)生編號,cid課程編號,score 分數(shù)
二郎仆、建表，插入數(shù)據(jù)
1.學(xué)生表 student
- mysql建表 / mysql插入數(shù)據(jù)：
DROP TABLE IF EXISTS student;
CREATE TABLE student(sid VARCHAR(10),sname VARCHAR(10),sage DATETIME,ssex VARCHAR(10));
INSERT INTO student VALUES('01' , '趙雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '錢電' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孫風(fēng)' , '1990-12-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-12-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吳蘭' , '1992-01-01' , '女');
INSERT INTO student VALUES('07' , '鄭竹' , '1989-01-01' , '女');
INSERT INTO student VALUES('08' , '吳八' , '2017-12-20' , '女');
INSERT INTO student VALUES('09' , '張三' , '2017-12-20' , '女');
INSERT INTO student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO student VALUES('11' , '李四' , '2012-06-06' , '女');
INSERT INTO student VALUES('12' , '趙六' , '2013-06-13' , '女');
INSERT INTO student VALUES('13' , '孫七' , '2014-06-01' , '女');
INSERT INTO student VALUES('14' , '十四' , '2014-06-01' , '男');
- hive建表 / hvie插入數(shù)據(jù)：
DROP TABLE IF EXISTS student;
CREATE TABLE student(
sid int,
sname string,
sage string,
ssex string
)
row format delimited fields terminated by '\t';
insert into table student values('01' , 'zhaolei' , '1990-01-01' , 'man'),('02' , 'qiandian' , '1990-12-21' , 'man'),('03' , 'sunfeng' , '1990-12-20' , 'man'),('04' , 'liyun' , '1990-12-06' , 'man'),('05' , 'zhoumei' , '1991-12-01' , 'female'),('06' , 'wulan' , '1992-01-01' , 'female'),('07' , 'zhenzhu' , '1989-01-01' , 'female'),('08' , 'wuba' , '1989-01-01' , 'female'),('09' , 'zhangsan' , '2017-12-20' , 'female'),('10' , 'lisi' , '2017-12-25' , 'female'),('11' , 'lisi' , '2012-06-06' , 'female'),('12' , 'zhaoliu' , '2013-06-13' , 'female'),('13' , 'sunqi' , '2014-06-01' , 'female'),('14' , 'shisi' , '2014-06-01' , 'man');
2.科目表 course
- mysql建表 / mysql插入數(shù)據(jù)：
CREATE TABLE course
(
cid VARCHAR(10),
cname VARCHAR(10),
tid VARCHAR(10)
);
INSERT INTO course VALUES('01' , '語文' , '02');
INSERT INTO course VALUES('02' , '數(shù)學(xué)' , '01');
INSERT INTO course VALUES('03' , '英語' , '03');
- hive建表 / hvie插入數(shù)據(jù)：
create table course(
cid int,
cname string,
tid int
)
row format delimited fields terminated by '\t';
INSERT INTO table course VALUES('01' , 'chinise' , '02');
INSERT INTO table course VALUES('02' , 'math' , '01');
INSERT INTO table course VALUES('03' , 'english' , '03');
上面這種方式插入有點慢,用下面這種方式
insert into table course values('01' , 'chinise' , '02'),('02' , 'math' , '01'),('03' , 'english' , '03');
3.教師表 teacher
- mysql建表 / mysql插入數(shù)據(jù)：
CREATE TABLE teacher(tid VARCHAR(10),tname VARCHAR(10));
INSERT INTO teacher VALUES('01' , '張三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');
- hive建表 / hvie插入數(shù)據(jù)：
create table teacher(
tid int,
tname string
)
row format delimited fields terminated by '\t';
insert into table Teacher values('01' , 'zhangsan'),('02' , 'lisi'),('03' , 'wangwu');
4.成績表 sc
- mysql建表 / mysql插入數(shù)據(jù)：
CREATE TABLE sc(sid VARCHAR(10),cid VARCHAR(10),score DECIMAL(18,1));
INSERT INTO sc VALUES('01' , '01' , 80);
INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 69);
INSERT INTO sc VALUES('07' , '02' , 90);
INSERT INTO sc VALUES('07' , '03' , 98);
INSERT INTO sc VALUES('08' , '01' , 65);
INSERT INTO sc VALUES('08' , '02' , null);
INSERT INTO sc VALUES('08' , '03' , 98);
- hive建表 / hvie插入數(shù)據(jù)：
create table sc(
sid int,
cid int,
score int
)
row format delimited fields terminated by '\t';
insert into table sc values('01' , '01' , 80),('01' , '02' , 90),('01' , '03' , 99),('02' , '01' , 70),('02' , '02' , 60),('02' , '03' , 80),('03' , '01' , 80),('03' , '02' , 80),('03' , '03' , 80),('04' , '01' , 50),('04' , '02' , 30),('04' , '03' , 20),('05' , '01' , 76),('05' , '02' , 87),('06' , '01' , 31),('06' , '03' , 69),('07' , '02' , 90),('07' , '03' , 98),('08' , '01' , 65),('08' , '02' , null),('08' , '03' , 98);
三兜蠕、題目
1.查詢" 01 "課程比" 02 "課程成績高的學(xué)生的信息及課程分數(shù)
2.查詢同時存在" 01 "課程和" 02 "課程的成績情況
3.查詢存在" 01 "課程但可能不存在" 02 "課程的成績情況(不存在時顯示為 null )
4.查詢不存在" 01 "課程但存在" 02 "課程的成績情況
5.查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
6.查詢在 SC 表存在成績的學(xué)生信息
7.查詢所有同學(xué)的學(xué)生編號扰肌、學(xué)生姓名、選課總數(shù)熊杨、所有課程的總成績(沒成績的顯示為 null )
8.查有成績的學(xué)生信息
9.查詢「李」姓老師的數(shù)量
10.查詢學(xué)過「張三」老師授課的同學(xué)的信息
11.查詢沒有學(xué)全所有課程的同學(xué)的信息
12.查詢至少有一門課與學(xué)號為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
13.查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程 完全相同的其他同學(xué)的信息
14.查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
15.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號曙旭，姓名及其平均成績
16.檢索" 01 "課程分數(shù)小于 60，按分數(shù)降序排列的學(xué)生信息
17.按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
18.查詢各科成績最高分晶府、最低分和平均分：
19.以如下形式顯示：課程 ID桂躏，課程 name，最高分川陆，最低分剂习，平均分，及格率较沪，中等率路鹰，優(yōu)良率粗仓，優(yōu)秀率
20.及格為>=60，中等為：70-80览爵，優(yōu)良為：80-90彪腔，優(yōu)秀為：>=90
21.要求輸出課程號和選修人數(shù)不恭，查詢結(jié)果按人數(shù)降序排列缠诅，若人數(shù)相同宿礁，按課程號升序排列
22.按各科成績進行排序，并顯示排名茬射， Score 重復(fù)時保留名次空缺
23.按各科成績進行排序鹦蠕，并顯示排名， Score 重復(fù)時合并名次
24.查詢學(xué)生的總成績躲株，并進行排名片部，總分重復(fù)時保留名次空缺
25.查詢學(xué)生的總成績镣衡，并進行排名霜定，總分重復(fù)時不保留名次空缺
26.統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱廊鸥，[100-85]望浩，[85-70]，[70-60]惰说，[60-0] 及所占百分比
27.查詢各科成績前三名的記錄
28.查詢每門課程被選修的學(xué)生數(shù)
29.查詢出只選修兩門課程的學(xué)生學(xué)號和姓名
30.查詢男生磨德、女生人數(shù)
31.查詢名字中含有「風(fēng)」字的學(xué)生信息
32.查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù)
33.查詢 1990 年出生的學(xué)生名單
34.查詢每門課程的平均成績，結(jié)果按平均成績降序排列典挑，平均成績相同時酥宴，按課程編號升序排列
35.查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號、姓名和平均成績
36.查詢課程名稱為「數(shù)學(xué)」您觉，且分數(shù)低于 60 的學(xué)生姓名和分數(shù)
37.查詢所有學(xué)生的課程及分數(shù)情況（存在學(xué)生沒成績拙寡，沒選課的情況）
38.查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分數(shù)
39.查詢不及格的課程
40.查詢課程編號為 01 且課程成績在 80 分以上的學(xué)生的學(xué)號和姓名
41.求每門課程的學(xué)生人數(shù)
42.成績不重復(fù)琳水，查詢選修「張三」老師所授課程的學(xué)生中肆糕，成績最高的學(xué)生信息及其成績
43.成績有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中在孝，成績最高的學(xué)生信息及其成績
44.查詢不同課程成績相同的學(xué)生的學(xué)生編號诚啃、課程編號、學(xué)生成績
45.查詢每門功成績最好的前兩名
46.統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計）私沮。
47.檢索至少選修兩門課程的學(xué)生學(xué)號
48.查詢選修了全部課程的學(xué)生信息
49.查詢各學(xué)生的年齡始赎，只按年份來算
50.按照出生日期來算，當(dāng)前月日 < 出生年月的月日則仔燕，年齡減一
51.查詢本周過生日的學(xué)生
52.查詢下周過生日的學(xué)生
53.查詢本月過生日的學(xué)生
54.查詢下月過生日的學(xué)生
四极阅、答案
答案不固定，各位如果有好的優(yōu)化方法可以在評論區(qū)貼上涨享。
1.查詢" 01 "課程比" 02 "課程成績高的學(xué)生的信息及課程分數(shù)
第一種寫法：
select a1.sid,
a1.sname,
a1.sage,
a1.ssex,
a2.score1,
a2.score2
from student a1
right join (
select t1.sid,
score1,
score2
from (select sid, score as score1 from sc where cid = '01') t1,
(select sid, score as score2 from sc where cid = '02') t2
where t1.sid = t2.sid
and t1.score1 > t2.score2
)a2
on a1.sid=a2.sid
;
去看一下MySql查詢select from 兩個表與left join on的區(qū)別
https://blog.csdn.net/weixin_33738555/article/details/91689609
第二種寫法
select a1.sid,
a1.sname,
a1.sage,
a1.ssex,
a2.score1,
a2.score2
from student a1
right join (
select t1.sid,
t1.score1 as score1,
t2.score2 as score2
from (
select sid,
score as score1
from sc
where cid = '01'
) t1
left join (
select sid,
score as score2
from sc
where cid = '02'
) t2
on t1.sid = t2.sid
where t1.score1 > t2.score2
) a2
on a1.sid = a2.sid
;
2.查詢同時存在" 01 "課程和" 02 "課程的成績情況
select t1.sid,
t1.score1,
t2.score2
from
(select sid,score as score1 from sc where cid = '01')t1,
(select sid,score as score2 from sc where cid = '02')t2
where t1.sid = t2.sid;
同時存在即:
select t1.sid,
t1.score1,
t2.score2
from
(select sid,score as score1 from sc where cid = '01')t1
join
(select sid,score as score2 from sc where cid = '02')t2
where t1.sid = t2.sid;
3.查詢存在" 01 "課程但可能不存在" 02 "課程的成績情況(不存在時顯示為 null )
即:
select t1.sid,
t1.cid,
t1.score
from sc t1
where t1.sid not in(
select sid from sc where cid = '02'
)
and t1.cid = '01'
;
select t1.sid,
t1.score1,
t2.score2
from
(select sid,score as score1 from sc where cid = '01')t1
left join
(select sid,score as score2 from sc where cid = '02')t2
where t1.sid = t2.sid;
4.查詢存在" 01 “課程但不存在” 02 "課程的成績情況
select *
from (select sid, score as score1 from sc where cid = '01') t1
left join
(select sid, score as score2 from sc where cid = '02') t2
on t1.sid = t2.sid
where t2.sid is null;
5.查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
mysql 寫法：
select t1.sid,
a1.sname,
t1.avg_score
from student a1
right join (
select sid,
sum(score) / count(sid) as avg_score
from sc
group by sid
having avg_score > 60
) t1
on a1.sid = t1.sid;
hive似乎無法使用having筋搏，一直報錯，有待考證
select t2.sid,
a1.sname,
t2.avg_score
from student a1
right join (
select t1.sid,
avg_score
from (
select sid,
sum(score) / count(sid) as avg_score
from sc
group by sid
) t1
where t1.avg_score > 60.0
) t2
on a1.sid = t2.sid;
6.查詢在 SC 表存在成績的學(xué)生信息
select distinct (a1.sid),
a2.sname,
a2.sage,
a2.ssex
from sc a1
left join student a2 on a1.sid = a2.sid;
7.查詢所有同學(xué)的學(xué)生編號厕隧、學(xué)生姓名奔脐、選課總數(shù)、所有課程的總成績(沒成績的顯示為 null )
select a1.sid,
a1.sname,
a2.c_cnt,
a2.s_cnt
from student a1
left join (
select t1.sid,
count(t1.cid) as c_cnt,
sum(score) as s_cnt
from sc t1
group by t1.sid
) a2
on a1.sid = a2.sid;
思考：
--笛卡爾積
select a1.sid, a1.sname, a1.sage, a1.ssex,
a2.cnt,a2.snt
from student a1,
(select t1.sid, count(t1.cid) as cnt, sum(score) snt from sc t1 group by t1.sid) a2;
--join
select a1.sid, a1.sname, a1.sage, a1.ssex,
a2.cnt,a2.snt
from student a1,
(select t1.sid, count(t1.cid) as cnt, sum(score) snt from sc t1 group by t1.sid) a2
where a1.sid = a2.sid;
--left join
select a1.sid,
a1.sname,
a2.c_cnt,
a2.s_cnt
from student a1
left join (
select t1.sid,
count(t1.cid) as c_cnt,
sum(score) as s_cnt
from sc t1
group by t1.sid
) a2
on a1.sid = a2.sid;
8.查有成績的學(xué)生信息
這一題涉及到in和exists的用法吁讨，在這種小表中髓迎，兩種方法的效率都差不多，但是請參考SQL查詢中in和exists的區(qū)別分析
當(dāng)表2的記錄數(shù)量非常大的時候建丧，選用exists比in要高效很多.
EXISTS用于檢查子查詢是否至少會返回一行數(shù)據(jù)排龄，該子查詢實際上并不返回任何數(shù)據(jù)，而是返回值True或False.
結(jié)論：IN()適合B表比A表數(shù)據(jù)小的情況
結(jié)論：EXISTS()適合B表比A表數(shù)據(jù)大的情況
--8.1 join
select a1.*
from student a1
join (
select distinct (t1.sid) as sid
from sc t1
) a2
on a1.sid = a2.sid;
-- 8.2 exists
select *
from student
where exists (select sc.sid from sc where student.sid = sc.sid);
-- 8.3 in
select *
from student
where student.sid in (select distinct (sid) from sc);
9.查詢「李」姓老師的數(shù)量
模糊查詢：
select count(*)
from teacher a1
where a1.tname like 'li%'
10.查詢學(xué)過「張三」老師授課的同學(xué)的信息
select t4.*
from student t4
right join (
select t3.sid
from sc t3
right join (
select t2.cid
from course t2
right join (
select t1.tid
from teacher t1
where t1.tname = 'zhangsan'
) a1
on t2.tid = a1.tid
) a2
on t3.cid = a2.cid
) a3
on t4.sid = a3.sid;
寫法2：
select *
from student a1,sc a2,teacher a3,course a4
where a1.sid = a2.sid
and a2.cid = a4.cid
and a4.tid = a3.tid
and a3.tname = 'zhangsan';
11.查詢沒有學(xué)全所有課程的同學(xué)的信息
- mysql寫法
select t3.*
from student t3
right join (
select t2.sid,
count(t2.cid) as cnt
from sc t2
group by t2.sid
having cnt < (
select count(distinct (t1.cid))
from course t1
)
)a1
on t3.sid = a1.sid
;
- hive 寫法
一直報錯...
12.查詢至少有一門課與學(xué)號為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
select t3.*
from student t3
right join (
select distinct t2.sid
from sc t2
where t2.cid in (
select t1.cid
from sc t1
where t1.sid = '01'
)
) a1
on t3.sid = a1.sid;
13.查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程 完全相同的其他同學(xué)的信息
思路:
1）查出"01"號學(xué)生的課程數(shù)目
2）查出"01"號學(xué)生的課程內(nèi)容
3）查出課程在"01"學(xué)生所學(xué)的課程內(nèi)容中并且課程數(shù)目和"01"學(xué)生所學(xué)課程數(shù)目相等的學(xué)生
mysql寫法:
select t4.*
from student t4
right join (
select a1.sid
from (
select t3.sid as sid,
t3.cid as cid
from sc t3
where t3.cid in (
select t2.cid
from sc t2
where t2.sid = '01'
)
) a1
group by sid
having count(sid) = (select count(distinct t1.cid)
from sc t1
where t1.sid = '01')
) a2
on t4.sid = a2.sid
;
14.查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
--mysql 寫法
select distinct t4.sname
from student t4
right join (
select t3.sid
from sc t3
where t3.cid not in (
select t2.cid
from course t2
where tid in (
select t1.tid
from teacher t1
where t1.tname = 'zhangsan'
)
)
)a1
on t4.sid = a1.sid;
15.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號翎朱，姓名及其平均成績
select a1.sid,
t2.sname
from student t2
right join (
select t1.sid,
sum(t1.score) / count(t1.sid)
from sc t1
where t1.score < 60
group by t1.sid
having count(t1.sid) > 2
) a1
on t2.sid = a1.sid;
16.檢索" 01 "課程分數(shù)小于 60橄维，按分數(shù)降序排列的學(xué)生信息
-- 寫法1
select t2.*,
a1.score
from student t2
right join (
select t1.sid,
t1.score as score
from sc t1
where t1.cid = '01'
and t1.score < 60
)a1
on a1.sid = t2.sid
order by a1.score desc;
--寫法2
select t1.*,
t2.score
from student t1
right join sc t2
on t1.sid = t2.sid
where t2.cid = '01'
and t2.score < 60
order by t2.score desc;
17.按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
-- mysql /hive 寫法
select a1.sid1,
a1.score,
a2.avg_score
from
(select t1.sid as sid1,t1.score as score from sc t1)a1,
(select t2.sid as sid2,avg(t2.score) as avg_score from sc t2 group by t2.sid) a2
where a1.sid1 = a2.sid2
order by a2.avg_score desc
;
--hive 寫法(開窗口）
select t1.sid,
t1.score,
avg(score) over (partition by t1.sid) as avg_score
from sc t1
order by avg_score desc
;
18.查詢各科成績最高分、最低分和平均分：
select t1.cid,
max(t1.score),
min(t1.score),
avg(t1.score)
from sc t1
group by t1.cid;
19.以如下形式顯示：課程 ID拴曲，課程 name争舞，最高分，最低分澈灼，平均分竞川，及格率店溢，中等率，優(yōu)良率委乌，優(yōu)秀率床牧；及格為>=60，中等為：70-80遭贸，優(yōu)良為：80-90叠赦，優(yōu)秀為：>=90；要求輸出課程號和選修人數(shù)革砸，查詢結(jié)果按人數(shù)降序排列除秀，若人數(shù)相同，按課程號升序排列
select t1.cid,
max(t1.score),
min(t1.score),
avg(t1.score),
count(*) as cnt,
sum(case when t1.score >= 60 then 1 else 0 end ) / count(*) ,
sum(case when t1.score > 60 and t1.score <= 70 then 1 else 0 end ) / count(*),
sum(case when t1.score > 70 and t1.score <= 80 then 1 else 0 end ) / count(*),
sum(case when t1.score > 80 and t1.score <= 90 then 1 else 0 end ) / count(*),
sum(case when t1.score >= 90 then 1 else 0 end ) / count(*)
from sc t1
group by t1.cid
order by cnt desc , t1.cid
;
20.按各科成績進行排序算利，并顯示排名册踩， score 重復(fù)時，合并（即排名：1,1,3....)
思路: 自交效拭，sc t1自己left join 自己t2暂吉，查詢t2表中在t1表當(dāng)前分數(shù)的高還有幾個
- mysql
--mysql
--思路: sc t1自己left join 自己t2，查詢t2表中在t1表當(dāng)前分數(shù)的高還有幾個
select t1.cid,t1.sid,t1.score,count(t2.score) + 1 as rank, t2.cid,t2.sid,t2.score
from sc t1
left join sc t2
on t1.score < t2.score and t1.cid = t2.cid
group by t1.cid, t1.sid, t1.score
order by t1.cid, rank asc
;
-- 或者
select a.sid,a.cid,a.score,count(b.cid)+1 as rank from sc a
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
order by a.cid, rank asc;
- hive
-- hive （rank排序打標記）
-- 排序相同時會重復(fù)缎患，總數(shù)不變 1,1,3...
select t1.cid,t1.sid,t1.score,
rank() over (partition by t1.cid order by t1.score desc) as rank
from sc t1;
-- 排序相同時會重復(fù)慕的，總數(shù)變少 1,1,3...
select t1.cid,t1.sid,t1.score,
dense_rank() over (partition by t1.cid order by t1.score desc) as rank
from sc t1;
-- 會根據(jù)順序計算, 1,2,3...
select t1.cid,t1.sid,t1.score,
row_number() over (partition by t1.cid order by t1.score desc) as rank
from sc t1;
21.按各科成績進行排序，并顯示排名挤渔， score 重復(fù)時肮街，不合并(即排名：1,2,3...)
- mysql
思路，用order by a,b 就能夠?qū)id字段進行分組判导，再對score進行排序嫉父。用兩個參數(shù),第一個字段記錄上一個cid，第二個字段用來標記當(dāng)前行cid是否為上一個cid眼刃，如果是就自加绕辖，不是就賦值為1。
set @rankid =0;
set @couse_id =0;
select a.*,
@rankid := if(@couse_id = cid,@rankid + 1, 1) rankid,
@couse_id := a.cid couse_id
from sc a
order by cid,score desc;
可參考：https://blog.csdn.net/qq_41902618/article/details/108514316
- hive
-- 會根據(jù)順序計算, 1,2,3...
select t1.cid,t1.sid,t1.score,
row_number() over (partition by t1.cid order by t1.score desc) as rank
from sc t1;
22.查詢學(xué)生的總成績擂红，并進行排名仪际，總分重復(fù)時,不合并(即排名：1,2,3...)
- mysql
mysql 變量：
mysql寫法1
set @crank = 0;
select a1.sid,
a1.total_score,
@crank := @crank + 1 as rank
from (
select t1.sid,
sum(t1.score) as total_score
from sc t1
group by t1.sid
order by total_score desc
) a1
;
23.查詢學(xué)生的總成績，并進行排名昵骤，總分重復(fù)時,合并（即排名：1,1,3....)
- mysql
思路：通過自交的方式树碱，求temp3中在temp2中大于當(dāng)前分數(shù)的個數(shù)
DROP TABLE IF EXISTS temp2;
create temporary table temp2
select t1.sid,
sum(t1.score) as total_score
from sc t1
group by t1.sid;
DROP TABLE IF EXISTS temp3;
create temporary table temp3
select t1.sid,
sum(t1.score) as total_score
from sc t1
group by t1.sid;
select t2.sid,
t2.total_score,
count(t3.total_score) + 1 rank
from temp2 t2 left join temp3 t3
on t2.total_score < t3.total_score
group by t2.sid, t2.total_score
order by rank asc;
- hive
--hive
select a1.sid,
a1.total_score,
rank() over (order by a1.total_score desc)
from (
select t1.sid,
sum(t1.score) as total_score
from sc t1
group by t1.sid
) a1
;
24.統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱涉茧，[100-85]赴恨，[85-70]疹娶，[70-60]伴栓，[60-0] 及所占百分比
select t2.cname,
a1.*
from course t2
right join (
select t1.cid as cid,
sum(case when t1.score <= 60 then 1 else 0 end) as p_per1,
sum(case when t1.score >= 60 and t1.score <= 70 then 1 else 0 end) as p_per2,
sum(case when t1.score >= 70 and t1.score <= 85 then 1 else 0 end) as p_per3,
sum(case when t1.score >= 85 and t1.score <= 100 then 1 else 0 end) as p_per4,
sum(case when t1.score <= 60 then 1 else 0 end) / count(*) as c_per1,
sum(case when t1.score >= 60 and t1.score <= 70 then 1 else 0 end) / count(*) as c_per2,
sum(case when t1.score >= 70 and t1.score <= 85 then 1 else 0 end) / count(*) as c_per3,
sum(case when t1.score >= 85 and t1.score <= 100 then 1 else 0 end) / count(*) as c_per4
from sc t1
group by t1.cid
) a1
on t2.cid = a1.cid
;
25.查詢各科成績前三名的記錄
- mysql
寫法1：計算比自己分數(shù)大的記錄有幾條，如果小于3 就select，因為對前三名來說不會有3個及以上的分數(shù)比自己大了钳垮，最后再對所有select到的結(jié)果按照分數(shù)和課程編號排名即可惑淳。
select *
from sc t1
where (
select count(*)
from sc t2
where t1.cid = t2.cid
and t1.score < t2.score
) < 3
and t1.score is not null
order by t1.cid, t1.score desc
;
寫法2：自身左交, rank小于3，就是前3
select a.sid,a.cid,a.score,count(b.cid)+1 as rank from sc a
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having rank <= 3
order by a.cid, rank asc;
寫法3：
set @rankid =0;
set @couse_id =0;
select a1.*
from (
select a.*,
@rankid := if(@couse_id = cid,@rankid + 1, 1) rankid,
@couse_id := a.cid couse_id
from sc a
order by cid, score desc
)a1
where a1.rankid <= 3
;
- hive
select *
from (
select t1.cid,
t1.sid,
t1.score,
row_number() over (partition by t1.cid order by t1.score desc) as rank
from sc t1
) a1
where a1.rank <= 3;
26.查詢每門課程被選修的學(xué)生數(shù)
select t1.cid,
count(t1.sid)
from sc t1
group by t1.cid;
27.查詢出只選修兩門課程的學(xué)生學(xué)號和姓名
select a1.sid,
t2.sname
from student t2
right join (
select t1.sid,
count(t1.cid) as cnt
from sc t1
group by t1.sid
having cnt = 2
)a1
on t2.sid = a1.sid;
28.查詢男生饺窿、女生人數(shù)
select t1.ssex,
count(t1.sid) as cnt
from student t1
group by t1.ssex;
29.查詢名字中含有「風(fēng)」字的學(xué)生信息
select *
from student t1
where t1.sname like '%風(fēng)%'
30.查詢同名同姓學(xué)生名單歧焦，并統(tǒng)計同名人數(shù)
select t1.sname,
count(sid) cnt
from student t1
group by t1.sname
having cnt >= 2;
31.查詢 1990 年出生的學(xué)生名單
select *
from student
where YEAR(student.sage)=1990;
32.查詢每門課程的平均成績，結(jié)果按平均成績降序排列肚医，平均成績相同時绢馍，按課程編號升序排列
select t1.cid,
avg(t1.score) avg_score
from sc t1
group by t1.cid
order by avg_score desc,t1.cid desc;
-- 將排名列出
set @crank = 0;
select a1.cid,
a1.avg_score,
@crank := @crank + 1 as rank
from (
select t1.cid,
avg(t1.score) avg_score
from sc t1
group by t1.cid
order by avg_score, t1.cid
) a1;
33.查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號、姓名和平均成績
select t2.sid,
t2.sname,
a1.avg_score
from student t2
right join (
select t1.sid,
avg(t1.score) avg_score
from sc t1
group by t1.sid
having avg_score >= 85
) a1
on t2.sid = a1.sid;
34.查詢課程名稱為「數(shù)學(xué)」肠套，且分數(shù)低于 60 的學(xué)生姓名和分數(shù)
select t3.sname,
a2.score
from student t3
right join (
select t2.sid,
t2.score
from sc t2
right join (
select t1.cid
from course t1
where t1.cname = '數(shù)學(xué)'
) a1
on a1.cid = t2.cid
where t2.score < 60
) a2
on t3.sid = a2.sid;
-- 或者
select student.sname, sc.score from student, sc, course
where student.sid = sc.sid
and course.cid = sc.cid
and course.cname = "數(shù)學(xué)"
and sc.score < 60;
35.查詢所有學(xué)生的課程及分數(shù)情況（存在學(xué)生沒成績舰涌，沒選課的情況）
select student.sname, cid, score from student
left join sc
on student.sid = sc.sid;
36.查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分數(shù)
select t1.sid,
t2.sname,
t3.cname,
t1.score
from sc t1
left join student t2 on t1.sid = t2.sid
left join course t3 on t1.cid = t3.cid
where t1.score > 70;
37.查詢存在不及格的課程
select t2.*,
t1.*
from sc t1
left join course t2 on t1.cid = t2.cid
where t1.score < 60;
38.查詢課程編號為 01 且課程成績在 80 分以上的學(xué)生的學(xué)號和姓名
select t2.sid,
t2.sname
from sc t1
left join student t2 on t1.sid = t2.sid
where t1.cid='01'
and t1.score >= 80;
39.求每門課程的學(xué)生人數(shù)
select t1.cid,
count(t1.sid) as cnt
from sc t1
group by t1.cid;
40.成績不重復(fù)你稚，查詢選修「張三」老師所授課程的學(xué)生中瓷耙，成績最高的學(xué)生信息及其成績
set @crank = 0;
select t4.*
from student t4
right join (
select a2.sid
from (
select a1.*,
@crank := @crank + 1 as rank
from (
select t1.tid,
t2.cid,
t3.sid,
t3.score
from teacher t1
left join course t2 on t1.tid = t2.tid
left join sc t3 on t2.cid = t3.cid
where t1.tname = '張三'
order by t3.score desc
) a1
) a2
where a2.rank = 1
)a3
on t4.sid = a3.sid;
41.成績有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中刁赖，成績最高的學(xué)生信息及其成績
42.查詢不同課程成績相同的學(xué)生的學(xué)生編號搁痛、課程編號、學(xué)生成績
這里用了inner join后會有概念是重復(fù)的記錄：“01 課與 03課”=“03 課與 01 課”宇弛，所以這里取唯一一條可以直接用group by
select a.cid, a.sid, a.score from sc as a
inner join
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;
43.查詢每門功課成績最好的前兩名
同25題
select *
from sc t1
where (
select count(*)
from sc t2
where t1.cid = t2.cid
and t1.score < t2.score
) < 2
and t1.score is not null
order by t1.cid, t1.score desc
;
44.統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計）鸡典。
select t1.cid,
count(t1.sid) as cnt
from sc t1
group by t1.cid
having cnt > 5;
45.檢索至少選修兩門課程的學(xué)生學(xué)號
select t1.sid,
count(cid) cnt
from sc t1
group by sid
having cnt >= 2
;
46.查詢選修了全部課程的學(xué)生信息
select t2.*
from student t2
right join (
select t1.sid as sid,
count(cid) as cnt
from sc t1
group by sid
having cnt = (select count(distinct (cid)) from course)
)a1
on t2.sid = a1.sid;
47.查詢各學(xué)生的年齡，只按年份來算
select t1.sid as 學(xué)生編號,
t1.sname as 學(xué)生姓名,
TIMESTAMPDIFF(YEAR, t1.sage, CURDATE()) as 學(xué)生年齡
from student t1;
48.按照出生日期來算枪芒，當(dāng)前月日 < 出生年月的月日則轿钠，年齡減一
select t1.sid as 學(xué)生編號,
t1.sname as 學(xué)生姓名,
TIMESTAMPDIFF(YEAR, t1.sage, CURDATE()) as 學(xué)生年齡
from student t1;
49.查詢本周過生日的學(xué)生
select *
from student t1
where WEEKOFYEAR(t1.sage)=WEEKOFYEAR(CURDATE());
50.查詢下周過生日的學(xué)生
select *
from student t1
where WEEKOFYEAR(t1.sage)=WEEKOFYEAR(CURDATE())+1;
51.查詢本月過生日的學(xué)生
select *
from student t1
where MONTH(t1.sage)=MONTH(CURDATE());
52.查詢下月過生日的學(xué)生
select *
from student t1
where MONTH(t1.sage)=MONTH(CURDATE())+1;
