Java面試之SQL語句題經(jīng)典案例
一、行轉(zhuǎn)列問題
現(xiàn)有表格A,按照以下格式排列;
姓名 | 收入類型 | 收入金額 |
---|---|---|
Tom | 年獎金 | 5w |
Tom | 月工資 | 10k |
Jack | 年獎金 | 8w |
Jack | 月工資 | 12k |
先需要將表格轉(zhuǎn)化為:
姓名 | 月工資 | 年獎金 |
---|---|---|
Tom | 10k | 50k |
Jack | 12k | 80k |
方法一:使用靜態(tài)SQL
select '姓名', sum(case '收入類型' when '年獎金' then '收入金額' else 0 end) 年獎金, sum(case '收入類型' when '月工資' then '收入金額' else 0 end) 月工資 from A group by '姓名'
方法二:使用 pivot:MySQL不支持
select * from ( select 姓名,收入類型,收入金額 from A ) test pivot(sum(收入金額) for 收入類型 in ('月工資','年終獎')) pvt
二、準(zhǔn)備工作:
【1】表名和字段
–1.學(xué)生表 Student(s_id,s_name,s_birth,s_sex) –學(xué)生編號,學(xué)生姓名, 出生年月,學(xué)生性別 –2.課程表 Course(c_id,c_name,t_id) – –課程編號, 課程名稱, 教師編號 –3.教師表 Teacher(t_id,t_name) –教師編號,教師姓名 –4.成績表 Score(s_id,c_id,s_score) –學(xué)生編號,課程編號,分?jǐn)?shù)
【2】測試數(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`) ); --成績表 CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); --插入學(xué)生表測試數(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' , '女'); --課程表測試數(shù)據(jù) insert into Course values('01' , '語文' , '02'); insert into Course values('02' , '數(shù)學(xué)' , '01'); insert into Course values('03' , '英語' , '03'); --教師表測試數(shù)據(jù) insert into Teacher values('01' , '張三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); --成績表測試數(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"課程成績高的學(xué)生的信息及課程分?jǐn)?shù):當(dāng)對一張表中的一列數(shù)據(jù)比較時,應(yīng)當(dāng)將一張表拆分為兩張表;
SELECT st.*,sc.`s_score` AS '語文' ,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】查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績:分組在 having 之前,有函數(shù)表達式時,條件判斷需要使用 having,同時主要成績需要截取為兩位;
SELECT s.`s_id`,s.`s_name`,ROUND(AVG(sc.`s_score`),2) AS '平均成績' 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é)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績;
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 '總成績' FROM student s LEFT JOIN score sc ON s.`s_id` = sc.`s_id` GROUP BY sc.`s_id`
【4】查詢學(xué)過 “張三” 老師授課的同學(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é)過"張三"老師授課的同學(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é)過編號為"01"并且也學(xué)過編號為"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é)號為"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"號的同學(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é)過"張三"老師講授的任一門課程的學(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é)號,姓名及其平均成績
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】按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績:這里要注意 where 和 on 的區(qū)別:on 條件是在生成臨時表時使用的條件,它不管on中的條件是否為真,都會返回左(右)邊表中的記錄。(返回左(右)表全部記錄)。此時可能會出現(xiàn)與右表不匹配的記錄即為空的記錄。即使on后邊的條件不為真也會返回左(右)表中的記錄。where 條件是在臨時表生成好后,再對臨時表進行過濾的條件。
SELECT s.`s_id`,s.`s_name`,sc.`s_score` AS "語文" ,sc1.`s_score` AS "數(shù)學(xué)",sc2.`s_score` AS "英語",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】查詢各科成績最高分、最低分和平均分:以如下形式顯示:課程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】查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績:Union:對兩個結(jié)果集進行并集操作,不包括重復(fù)行,同時進行默認(rèn)規(guī)則的排序;Union All:對兩個結(jié)果集進行并集操作,包括重復(fù)行,不進行排序;注意 limit下標(biāo)是從0開始的。
(SELECT s.*,c.`c_name`,sc.`s_score` "成績" 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` "成績" 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` "成績" 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é)生平均成績及其名次:重點是名次的獲取,通過變量 @i 進行遞增獲取。
SET @i=0; SELECT test.*,@i:=@i+1 "名次" FROM( SELECT s.`s_name`,ROUND(AVG(sc.`s_score`),2) "平均成績" 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】查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績:思路就是先查詢一條數(shù)據(jù),然后與表中的數(shù)據(jù)比較相同的成績,且科目號不相同的數(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】 查詢每門功成績最好的前兩名
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】查詢本周過生日的學(xué)生:此處可能有問題,week函數(shù)取的為當(dāng)前年的第幾周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期幾(%w), 再判斷本周是否會持續(xù)到下一個月進行判斷,太麻煩。
SELECT st.* FROM student st WHERE WEEK(NOW())=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))
【18】查詢下周過生日的學(xué)生
SELECT st.* FROM student st WHERE WEEK(NOW())+1=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))
【19】查詢本月過生日的學(xué)生
SELECT st.* FROM student st WHERE MONTH(NOW())=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))
【20】查詢下月過生日的學(xué)生: 注意,如果當(dāng)前月為12月時,用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語句題經(jīng)典案例的文章就介紹到這了,更多相關(guān)Java面試SQL語句題內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
零基礎(chǔ)寫Java知乎爬蟲之獲取知乎編輯推薦內(nèi)容
上篇文章我們拿百度首頁做了個小測試,今天我們來個復(fù)雜的,直接抓取知乎編輯推薦的內(nèi)容,小伙伴們可算松了口氣,終于進入正題了,哈哈。2014-11-11SpringBoot整合Netty+Websocket實現(xiàn)消息推送的示例代碼
WebSocket使得客戶端和服務(wù)器之間的數(shù)據(jù)交換變得更加簡單,允許服務(wù)端主動向客戶端推送數(shù)據(jù),本文主要介紹了SpringBoot整合Netty+Websocket實現(xiàn)消息推送的示例代碼,具有一定的參考價值,感興趣的可以了解一下2024-01-01spring通過構(gòu)造函數(shù)注入實現(xiàn)方法分析
這篇文章主要介紹了spring通過構(gòu)造函數(shù)注入實現(xiàn)方法,結(jié)合實例形式分析了spring通過構(gòu)造函數(shù)注入的原理、實現(xiàn)步驟及相關(guān)操作注意事項,需要的朋友可以參考下2019-10-10