學(xué)生表：student(學(xué)號sno,學(xué)生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學(xué)號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現(xiàn)以MySQL為主
/*
分析思路
select 查詢結(jié)果 []
from 從哪張表中查找數(shù)據(jù) [成績表score]
where 查詢條件 [課程編號為“04”且分數(shù)小于60]
group by 分組 [沒有]
having 對分組結(jié)果指定條件 []
order by 對查詢結(jié)果排序[查詢結(jié)果按按分數(shù)降序排列];
*/
select 學(xué)號 from score where 課程號='04' and 成績 <60
order by 成績 desc;
select count(教師號) from teacher where 教師姓名 like '孟%';
/*
查找1990年出生的學(xué)生名單
學(xué)生表中出生日期列的類型是datetime
*/
select 學(xué)號,姓名 from student where year(出生日期)=1990;
select sum(成績) from score where 課程號 = '0002';
select count(distinct 學(xué)號) as 學(xué)生人數(shù) from score;
select 課程號, count(學(xué)號) from score group by 課程號;
/*
分析思路
group by 分組 [男生、女生人數(shù)：按性別分組
having 對分組結(jié)果指定條件 [沒有]
order by 對查詢結(jié)果排序[沒有];
*/
select 性別,count(*) from student group by 性別;
select 課程號 from score where 成績<60 order by 課程號 desc;
學(xué)生表：student(學(xué)號sno,學(xué)生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學(xué)號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現(xiàn)以MySQL為主
/*
分析思路
select 查詢結(jié)果 [課程ID：是課程號的別名,最高分：max(成績) ,最低分：min(成績)]
from 從哪張表中查找數(shù)據(jù) [成績表score]
where 查詢條件 [沒有]
group by 分組 [各科成績：也就是每門課程的成績，需要按課程號分組];
*/
select 課程號,max(成績) as 最高分,min(成績) as 最低分from score
group by 課程號;
/*
題目翻譯成大白話：
平均成績：展開來說就是計算每個學(xué)生的平均成績
這里涉及到“每個”就是要分組了
平均成績大于60分，就是對分組結(jié)果指定條件
*/
select 學(xué)號, avg(成績)from score group by 學(xué)號having avg(成績)>60;
/*
翻譯成大白話：
第1步，需要先計算出每個學(xué)生選修的課程數(shù)據(jù)，需要按學(xué)號分組
第2步，至少選修兩門課程：也就是每個學(xué)生選修課程數(shù)目>=2，對分組結(jié)果指定條件
*/
select 學(xué)號, count(課程號) as 選修課程數(shù)目from score group by 學(xué)號 having count(課程號)>=2;
/*
翻譯成大白話，問題解析：
1）查找出姓名相同的學(xué)生有誰，每個姓名相同學(xué)生的人數(shù)
查詢結(jié)果：姓名,人數(shù)
條件：怎么算姓名相同？按姓名分組后人數(shù)大于等于2，因為同名的人數(shù)大于等于2
*/
select 姓名,count(*) as 人數(shù) from student group by 姓名
having count(*)>=2;
select 課程號, avg(成績) as 平均成績from score
group by 課程號 order by 平均成績 asc,課程號 desc;
要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排序，若人數(shù)相同，按課程號升序排序。
select 課程號, count(學(xué)號) as '選修人數(shù)'
from score group by 課程號having count(學(xué)號)>2
order by count(學(xué)號) desc,課程號 asc;
/*第1步：得到每個學(xué)生的平均成績，顯示學(xué)號，平均成績*/
select 學(xué)號, avg(成績) as 平均成績 from score group by 學(xué)號;
/* 第2步：再加上限制條件：*/
select 學(xué)號, avg(成績) as 平均成績from score where 成績 <60 group by 學(xué)號 having count(課程號)>=2;
學(xué)生表：student(學(xué)號sno,學(xué)生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學(xué)號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現(xiàn)以MySQL為主
/*
第1步，寫子查詢（所有課程成績 < 60 的學(xué)生）*/
select 學(xué)號 from score where 成績 < 60;
/*第2步，查詢結(jié)果：學(xué)生學(xué)號，姓名，條件是前面1步查到的學(xué)號*/
select 學(xué)號,姓名 from student where 學(xué)號 in ( select 學(xué)號
from score where 成績 < 60);
/*
查找出學(xué)號，條件：沒有學(xué)全所有課，也就是該學(xué)生選修的課程數(shù) < 總的課程數(shù)
【考察知識點】in，子查詢
*/
select 學(xué)號,姓名from student where 學(xué)號 in( select 學(xué)號 from score
group by 學(xué)號 having count(課程號) < (select count(課程號) from course));
select 學(xué)號,姓名from student where 學(xué)號 in( select 學(xué)號 from score
group by 學(xué)號having count(課程號)=2);
/*我們可以使用分組（group by）和匯總函數(shù)得到每個組里的一個值（最大值，最小值，平均值等）。
但是無法得到成績最大值所在行的數(shù)據(jù)。*/
select 課程號,max(成績) as 最大成績 from score group by 課程號;
/*我們可以使用關(guān)聯(lián)子查詢來實現(xiàn)：*/
select * from score as a where 成績 = (select max(成績)
from score as b where b.課程號 = a.課程號);
/*上面查詢結(jié)果課程號“0001”有2行數(shù)據(jù)，是因為最大成績80有2個
分組取每組最小值：按課程號分組取成績最小值所在行的數(shù)據(jù)*/
select * from score as a where 成績 = (select min(成績)
from score as b where b.課程號 = a.課程號);
/*第1步，查出有哪些組，我們可以按課程號分組，查詢出有哪些組，對應(yīng)這個問題里就是有哪些課程號*/
select 課程號,max(成績) as 最大成績from score group by 課程號;
/*第2步：先使用order by子句按成績降序排序（desc），然后使用limt子句返回topN（對應(yīng)這個問題返回的成績前兩名*/
select * from score where 課程號 = '0001' order by 成績 ?desc?limit 2;
/*第3步，使用union all 將每組選出的數(shù)據(jù)合并到一起.同樣的，可以寫出其他組的（其他課程號）取出成績前2名的sql*/
(select * from score where 課程號 = '0001' order by 成績 ?desc limit 2) union all
(select * from score where 課程號 = '0002' order by 成績 ?desc limit 2) union all
(select * from score where 課程號 = '0003' order by 成績 ?desc limit 2);
select a.學(xué)號,a.姓名,count(b.課程號) as 選課數(shù),sum(b.成績) as 總成績
from student as a left join score as b on a.學(xué)號 = b.學(xué)號group by a.學(xué)號;
select a.學(xué)號,a.姓名, avg(b.成績) as 平均成績
from student as a left join score as b
on a.學(xué)號 = b.學(xué)號group by a.學(xué)號having avg(b.成績)>85;
select a.學(xué)號, a.姓名, c.課程號,c.課程名稱
from student a inner join score b on a.學(xué)號=b.學(xué)號
inner join course c on b.課程號=c.課程號;
select a.學(xué)號,a.姓名
from student as a inner join score as b on a.學(xué)號=b.學(xué)號
where b.課程號='0003' and b.成績>80;
變成
select userId,'語文' as subjectName,chinese_score as score from tb_score1
union all
select userId,'數(shù)學(xué)' as subjectName,math_score as score from tb_score1
union all
select userId,'英語' as subjectName,english_score as score from tb_score1
union all
select userId,'政治' as subjectName,politics_score as score from tb_score1;
-- 考察case表達式
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成<85 then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70 then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b
on a.課程號=b.課程號 group by a.課程號,b.課程名稱;
-- 考察case表達式
select 課程號,
sum(case when 成績>=60 then 1 else 0 end) as 及格人數(shù),
sum(case when 成績 < 60 then 1 else 0 end) as 不及格人數(shù)
from score group by 課程號;
轉(zhuǎn)化后
SELECT
userid,
SUM( CASE subjectName WHEN '語文' THEN score ELSE 0 END ) '語文',
SUM( CASE subjectName WHEN '數(shù)學(xué)' THEN score ELSE 0 END ) '數(shù)學(xué)',
SUM( CASE subjectName WHEN '英語' THEN score ELSE 0 END ) '英語',
SUM( CASE subjectName WHEN '政治' THEN score ELSE 0 END ) '政治'
FROM
tb_score
GROUP BY
userid;
SELECT
userid,
SUM( IF ( subjectName = '語文', score, 0 ) ) '語文',
SUM( IF ( subjectName = '數(shù)學(xué)', score, 0 ) ) '數(shù)學(xué)',
SUM( IF ( subjectName = '英語', score, 0 ) ) '英語',
SUM( IF ( subjectName = '政治', score, 0 ) ) '政治'
FROM
tb_score
GROUP BY
userid;
MySQL提供了 group by with rollup 函數(shù)進行g(shù)roup by 字段的匯總，但是與order by 互斥的不能同時用。
SELECT
IFNULL( userid, 'total' ) AS userid,
SUM( IF ( subjectName = '語文', score, 0 ) ) '語文',
SUM( IF ( subjectName = '數(shù)學(xué)', score, 0 ) ) '數(shù)學(xué)',
SUM( IF ( subjectName = '英語', score, 0 ) ) '英語',
SUM( IF ( subjectName = '政治', score, 0 ) ) '政治',
SUM( IF ( subjectName = 'total', score, 0 ) ) AS 'total'
FROM
(
SELECT
userid,
IFNULL( subjectName, 'total' ) AS subjectName,
SUM( score ) AS score
FROM
tb_score
GROUP BY userid, subjectName WITH ROLLUP
) AS a
GROUP BY userid
WITH ROLLUP;
SELECT IFNULL(userid,'total') AS userid,
SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid WITH ROLLUP;
SELECT userid,
SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid
UNION
SELECT 'total',SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) FROM tb_score;
SELECT userid,GROUP_CONCAT(`subjectName`,":",score)AS 成績 FROM tb_score GROUP BY userid;
官方微信
官方抖音
京公網(wǎng)安備 11030102010736號