Java面試之SQL語(yǔ)句題經(jīng)典案例
更新時(shí)間：2024年10月30日 10:58:30 作者：程序猿進(jìn)階
本文詳細(xì)討論了如何將行數(shù)據(jù)轉(zhuǎn)化為列數(shù)據(jù),并提供了多種SQL查詢練習(xí)題,包括查詢特定條件的學(xué)生信息、課程成績(jī)比較、學(xué)生成績(jī)排名等,文章還解釋了在SQL中使用Union、UnionAll和pivot的方法,以及如何處理復(fù)雜的SQL查詢問(wèn)題,需要的朋友可以參考下
一、行轉(zhuǎn)列問(wèn)題
現(xiàn)有表格A，按照以下格式排列；
| 姓名 | 收入類型 | 收入金額 |
|---|---|---|
| Tom | 年獎(jiǎng)金 | 5w |
| Tom | 月工資 | 10k |
| Jack | 年獎(jiǎng)金 | 8w |
| Jack | 月工資 | 12k |
先需要將表格轉(zhuǎn)化為：
| 姓名 | 月工資 | 年獎(jiǎng)金 |
|---|---|---|
| Tom | 10k | 50k |
| Jack | 12k | 80k |
方法一：使用靜態(tài)SQL
select '姓名', sum(case '收入類型' when '年獎(jiǎng)金' then '收入金額' else 0 end) 年獎(jiǎng)金, sum(case '收入類型' when '月工資' then '收入金額' else 0 end) 月工資 from A group by '姓名'
方法二：使用 pivot：MySQL不支持
select * from
(
select 姓名,收入類型,收入金額 from A
) test
pivot(sum(收入金額) for 收入類型 in ('月工資','年終獎(jiǎng)')) pvt
二、準(zhǔn)備工作：
【1】表名和字段
–1.學(xué)生表 Student(s_id,s_name,s_birth,s_sex) –學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別 –2.課程表 Course(c_id,c_name,t_id) – –課程編號(hào), 課程名稱, 教師編號(hào) –3.教師表 Teacher(t_id,t_name) –教師編號(hào),教師姓名 –4.成績(jī)表 Score(s_id,c_id,s_score) –學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù)
【2】測(cè)試數(shù)據(jù)
--建表
--學(xué)生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--課程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教師表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成績(jī)表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入學(xué)生表測(cè)試數(shù)據(jù)
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--課程表測(cè)試數(shù)據(jù)
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');
--教師表測(cè)試數(shù)據(jù)
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成績(jī)表測(cè)試數(shù)據(jù)
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
三、練習(xí)題
【1】查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)：當(dāng)對(duì)一張表中的一列數(shù)據(jù)比較時(shí)，應(yīng)當(dāng)將一張表拆分為兩張表；
SELECT st.*,sc.`s_score` AS '語(yǔ)文' ,sc2.`s_score` AS '數(shù)學(xué)' FROM student st LEFT JOIN score sc ON st.s_id=sc.`s_id` AND sc.`c_id`='01' LEFT JOIN score sc2 ON st.s_id=sc2.`s_id` AND sc2.`c_id`='02' WHERE sc.`s_score` > sc2.`s_score`;
【2】查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)：分組在 having 之前，有函數(shù)表達(dá)式時(shí)，條件判斷需要使用 having，同時(shí)主要成績(jī)需要截取為兩位；
SELECT s.`s_id`,s.`s_name`,ROUND(AVG(sc.`s_score`),2) AS '平均成績(jī)' FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` GROUP BY sc.`s_id` HAVING AVG(sc.`s_score`) >= 60;
【3】查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)；
SELECT s.`s_id`,s.`s_name`,COUNT(sc.`c_id`) AS '選課總數(shù)',SUM(CASE WHEN sc.`s_score` IS NULL THEN 0 ELSE sc.`s_score` END) AS '總成績(jī)' FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` GROUP BY sc.`s_id`
【4】查詢學(xué)過(guò) “張三” 老師授課的同學(xué)的信息；
SELECT s.* FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` LEFT JOIN course c ON sc.`c_id` = c.`c_id` LEFT JOIN teacher t ON t.`t_id` = c.`t_id` WHERE t.`t_name` = "張三"
【5】查詢沒學(xué)過(guò)"張三"老師授課的同學(xué)的信息；
SELECT st.* FROM student st WHERE st.s_id NOT IN( SELECT sc.s_id FROM score sc WHERE sc.c_id IN (SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id=c.t_id WHERE t.t_name="張三") )
【6】查詢學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
SELECT s.* FROM student s
INNER JOIN score sc ON s.`s_id` = sc.`s_id`
INNER JOIN score sc1 ON s.`s_id` = sc1.`s_id`
WHERE sc.`c_id`='01' AND sc1.`c_id`='02'
--方式二
SELECT a.*
FROM
student a,
score b,
score c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02';
【7】查詢至少有一門課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
SELECT DISTINCT s.* FROM student s
LEFT JOIN score c ON s.`s_id` = c.`s_id`
WHERE c.`c_id` IN (
SELECT sc.`c_id` FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
WHERE s.`s_id`='01'
);
【8】查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
SELECT DISTINCT s.* FROM student s
LEFT JOIN score c ON s.`s_id` = c.`s_id`
GROUP BY s.`s_id`
HAVING COUNT(c.`c_id`) = (
SELECT COUNT(sc.`c_id`) FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
WHERE s.`s_id`='01'
);
【9】查詢沒學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名
SELECT s.`s_name` FROM student s
WHERE s.`s_id` NOT IN(
SELECT sc.`s_id` FROM score sc
LEFT JOIN course c ON sc.`c_id` = c.`c_id`
LEFT JOIN teacher t ON t.`t_id` = c.`t_id`
WHERE t.`t_name`="張三"
)
【10】查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
SELECT s.`s_id`,s.`s_name`,AVG(sc.`s_score`) FROM student s
INNER JOIN score sc ON s.`s_id` = sc.`s_id`
WHERE s.`s_id` IN (
SELECT sc.`s_id` FROM score sc
WHERE sc.`s_score`<60
GROUP BY sc.`s_id`
HAVING COUNT(1)>=2
)
GROUP BY s.`s_id`
【11】按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)：這里要注意 where 和 on 的區(qū)別：on 條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會(huì)返回左（右）邊表中的記錄。(返回左（右）表全部記錄)。此時(shí)可能會(huì)出現(xiàn)與右表不匹配的記錄即為空的記錄。即使on后邊的條件不為真也會(huì)返回左（右）表中的記錄。where 條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過(guò)濾的條件。
SELECT s.`s_id`,s.`s_name`,sc.`s_score` AS "語(yǔ)文" ,sc1.`s_score` AS "數(shù)學(xué)",sc2.`s_score` AS "英語(yǔ)",AVG(sc3.`s_score`) "平均分" FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id` = "01" LEFT JOIN score sc1 ON s.`s_id` = sc1.`s_id` AND sc1.`c_id` = "02" LEFT JOIN score sc2 ON s.`s_id` = sc2.`s_id` AND sc2.`c_id` = "03" LEFT JOIN score sc3 ON s.`s_id` = sc3.`s_id` GROUP BY s.`s_id` ORDER BY AVG(sc3.`s_score`) DESC
【12】查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程 Name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率（及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90）
SELECT c.`c_id`,c.`c_name`,MAX(s.`s_score`) "最高分",MIN(s.`s_score`) "最低分",AVG(s.`s_score`) "平均分", ((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND sc.`s_score` >= 60)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "及格率", ((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND 80 >= sc.`s_score` AND sc.`s_score` >= 70)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "中等率", ((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND 90 >= sc.`s_score` AND sc.`s_score` >= 80)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "優(yōu)良率", ((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND sc.`s_score` >= 90)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "優(yōu)秀率" FROM course c LEFT JOIN score s ON c.`c_id` = s.`c_id` GROUP BY c.`c_id`;
【13】查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)：Union：對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；Union All：對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；注意 limit下標(biāo)是從0開始的。
(SELECT s.*,c.`c_name`,sc.`s_score` "成績(jī)" FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="01" LEFT JOIN course c ON sc.`c_id` = c.`c_id` ORDER BY sc.`s_score` DESC LIMIT 1,2) UNION ALL (SELECT s.*,c.`c_name`,sc.`s_score` "成績(jī)" FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="02" LEFT JOIN course c ON sc.`c_id` = c.`c_id` ORDER BY sc.`s_score` DESC LIMIT 1,2) UNION ALL (SELECT s.*,c.`c_name`,sc.`s_score` "成績(jī)" FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="03" LEFT JOIN course c ON sc.`c_id` = c.`c_id` ORDER BY sc.`s_score` DESC LIMIT 1,2)
【14】查詢學(xué)生平均成績(jī)及其名次：重點(diǎn)是名次的獲取，通過(guò)變量 @i 進(jìn)行遞增獲取。
SET @i=0; SELECT test.*,@i:=@i+1 "名次" FROM( SELECT s.`s_name`,ROUND(AVG(sc.`s_score`),2) "平均成績(jī)" FROM score sc LEFT JOIN student s ON s.`s_id` = sc.`s_id` GROUP BY sc.`s_id` ORDER BY AVG(sc.`s_score`) DESC) test;
【15】查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)：思路就是先查詢一條數(shù)據(jù)，然后與表中的數(shù)據(jù)比較相同的成績(jī)，且科目號(hào)不相同的數(shù)據(jù)行，如果大于1則返回當(dāng)前行即可。逐行比較；
SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id LEFT JOIN course c ON c.c_id=sc.c_id WHERE ( SELECT COUNT(1) FROM student st2 LEFT JOIN score sc2 ON sc2.s_id=st2.s_id LEFT JOIN course c2 ON c2.c_id=sc2.c_id WHERE sc.s_score=sc2.s_score AND c.c_id!=c2.c_id )>=1
【16】 查詢每門功成績(jī)最好的前兩名
SELECT a.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="01" ORDER BY sc.s_score DESC LIMIT 0,2) a UNION ALL SELECT b.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="02" ORDER BY sc.s_score DESC LIMIT 0,2) b UNION ALL SELECT c.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="03" ORDER BY sc.s_score DESC LIMIT 0,2) c
方式二
SELECT a.s_id,a.c_id,a.s_score FROM score a WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id
【17】查詢本周過(guò)生日的學(xué)生：此處可能有問(wèn)題,week函數(shù)取的為當(dāng)前年的第幾周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期幾(%w)， 再判斷本周是否會(huì)持續(xù)到下一個(gè)月進(jìn)行判斷,太麻煩。
SELECT st.* FROM student st WHERE WEEK(NOW())=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))
【18】查詢下周過(guò)生日的學(xué)生
SELECT st.* FROM student st WHERE WEEK(NOW())+1=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))
【19】查詢本月過(guò)生日的學(xué)生
SELECT st.* FROM student st WHERE MONTH(NOW())=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
【20】查詢下月過(guò)生日的學(xué)生： 注意，如果當(dāng)前月為12月時(shí)，用month(now())+1為13而不是1，可用 timestampadd() 函數(shù)或 mod 取模
SELECT st.* FROM student st WHERE MONTH(TIMESTAMPADD(MONTH,1,NOW()))=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
方法二：
SELECT st.* FROM student st WHERE (MONTH(NOW()) + 1) MOD 12 = MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
總結(jié)
到此這篇關(guān)于Java面試之SQL語(yǔ)句題經(jīng)典案例的文章就介紹到這了,更多相關(guān)Java面試SQL語(yǔ)句題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
相關(guān)文章
這篇文章主要介紹了Java 中運(yùn)行字符串表達(dá)式的方法,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-11-11
這篇文章主要介紹了解決springboot整合druid遇到的坑，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-08-08
Java校驗(yàn)是否為連續(xù)的區(qū)間問(wèn)題
這篇文章主要介紹了Java校驗(yàn)是否為連續(xù)的區(qū)間問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
idea gradle項(xiàng)目復(fù)制依賴小技巧(推薦)
這篇文章主要介紹了idea gradle項(xiàng)目復(fù)制依賴小技巧,本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-11-11
Windows Zookeeper安裝過(guò)程及啟動(dòng)圖解
這篇文章主要介紹了Windows Zookeeper安裝過(guò)程及啟動(dòng)圖解,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-12-12
詳解Java動(dòng)態(tài)代理的實(shí)現(xiàn)機(jī)制
這篇文章主要為大家詳細(xì)介紹了Java動(dòng)態(tài)代理的實(shí)現(xiàn)機(jī)制，感興趣的小伙伴們可以參考一下2016-03-03
零基礎(chǔ)寫Java知乎爬蟲之獲取知乎編輯推薦內(nèi)容
上篇文章我們拿百度首頁(yè)做了個(gè)小測(cè)試，今天我們來(lái)個(gè)復(fù)雜的，直接抓取知乎編輯推薦的內(nèi)容，小伙伴們可算松了口氣，終于進(jìn)入正題了，哈哈。2014-11-11
SpringBoot整合Netty+Websocket實(shí)現(xiàn)消息推送的示例代碼
WebSocket使得客戶端和服務(wù)器之間的數(shù)據(jù)交換變得更加簡(jiǎn)單,允許服務(wù)端主動(dòng)向客戶端推送數(shù)據(jù),本文主要介紹了SpringBoot整合Netty+Websocket實(shí)現(xiàn)消息推送的示例代碼,具有一定的參考價(jià)值,感興趣的可以了解一下2024-01-01
很多面試官喜歡把線程池作為問(wèn)題的起點(diǎn)，然后延伸到其它內(nèi)容，由于我們專欄已經(jīng)說(shuō)過(guò)隊(duì)列、線程、鎖面試題了，所以本章面試題還是以線程池為主2022-03-03
spring通過(guò)構(gòu)造函數(shù)注入實(shí)現(xiàn)方法分析
這篇文章主要介紹了spring通過(guò)構(gòu)造函數(shù)注入實(shí)現(xiàn)方法,結(jié)合實(shí)例形式分析了spring通過(guò)構(gòu)造函數(shù)注入的原理、實(shí)現(xiàn)步驟及相關(guān)操作注意事項(xiàng),需要的朋友可以參考下2019-10-10