欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Java面試之SQL語句題經(jīng)典案例

 更新時間：2024年10月30日 10:58:30   作者：程序猿進階  
本文詳細討論了如何將行數(shù)據(jù)轉(zhuǎn)化為列數(shù)據(jù),并提供了多種SQL查詢練習(xí)題,包括查詢特定條件的學(xué)生信息、課程成績比較、學(xué)生成績排名等,文章還解釋了在SQL中使用Union、UnionAll和pivot的方法,以及如何處理復(fù)雜的SQL查詢問題,需要的朋友可以參考下

一、行轉(zhuǎn)列問題

現(xiàn)有表格A，按照以下格式排列；

姓名收入類型收入金額
Tom年獎金5w
Tom月工資10k
Jack年獎金8w
Jack月工資12k

先需要將表格轉(zhuǎn)化為：

姓名月工資年獎金
Tom10k50k
Jack12k80k

方法一：使用靜態(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)文章

  • Java 中運行字符串表達式的方法

    Java 中運行字符串表達式的方法

    這篇文章主要介紹了Java 中運行字符串表達式的方法,本文通過實例代碼給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下
    2018-11-11
  • 解決springboot整合druid遇到的坑

    解決springboot整合druid遇到的坑

    這篇文章主要介紹了解決springboot整合druid遇到的坑，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2021-08-08
  • Java校驗是否為連續(xù)的區(qū)間問題

    Java校驗是否為連續(xù)的區(qū)間問題

    這篇文章主要介紹了Java校驗是否為連續(xù)的區(qū)間問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2023-12-12
  • idea gradle項目復(fù)制依賴小技巧(推薦)

    idea gradle項目復(fù)制依賴小技巧(推薦)

    這篇文章主要介紹了idea gradle項目復(fù)制依賴小技巧,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2020-11-11
  • Windows Zookeeper安裝過程及啟動圖解

    Windows Zookeeper安裝過程及啟動圖解

    這篇文章主要介紹了Windows Zookeeper安裝過程及啟動圖解,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2020-12-12
  • 詳解Java動態(tài)代理的實現(xiàn)機制

    詳解Java動態(tài)代理的實現(xiàn)機制

    這篇文章主要為大家詳細介紹了Java動態(tài)代理的實現(xiàn)機制，感興趣的小伙伴們可以參考一下
    2016-03-03
  • 零基礎(chǔ)寫Java知乎爬蟲之獲取知乎編輯推薦內(nèi)容

    零基礎(chǔ)寫Java知乎爬蟲之獲取知乎編輯推薦內(nèi)容

    上篇文章我們拿百度首頁做了個小測試，今天我們來個復(fù)雜的，直接抓取知乎編輯推薦的內(nèi)容，小伙伴們可算松了口氣，終于進入正題了，哈哈。
    2014-11-11
  • SpringBoot整合Netty+Websocket實現(xiàn)消息推送的示例代碼

    SpringBoot整合Netty+Websocket實現(xiàn)消息推送的示例代碼

    WebSocket使得客戶端和服務(wù)器之間的數(shù)據(jù)交換變得更加簡單,允許服務(wù)端主動向客戶端推送數(shù)據(jù),本文主要介紹了SpringBoot整合Netty+Websocket實現(xiàn)消息推送的示例代碼,具有一定的參考價值,感興趣的可以了解一下
    2024-01-01
  • java線程池使用及原理面試題

    java線程池使用及原理面試題

    很多面試官喜歡把線程池作為問題的起點，然后延伸到其它內(nèi)容，由于我們專欄已經(jīng)說過隊列、線程、鎖面試題了，所以本章面試題還是以線程池為主
    2022-03-03
  • spring通過構(gòu)造函數(shù)注入實現(xiàn)方法分析

    spring通過構(gòu)造函數(shù)注入實現(xiàn)方法分析

    這篇文章主要介紹了spring通過構(gòu)造函數(shù)注入實現(xiàn)方法,結(jié)合實例形式分析了spring通過構(gòu)造函數(shù)注入的原理、實現(xiàn)步驟及相關(guān)操作注意事項,需要的朋友可以參考下
    2019-10-10

最新評論