最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

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

二、準備工作：

【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é)生編號,課程編號,分數(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' , '孫風' , '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é)生的信息及課程分數(shù)：當對一張表中的一列數(shù)據(jù)比較時，應(yī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ù)行，同時進行默認規(guī)則的排序；Union All：對兩個結(jié)果集進行并集操作，包括重復(fù)行，不進行排序；注意 limit下標是從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則返回當前行即可。逐行比較；

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ù)取的為當前年的第幾周,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é)生： 注意，如果當前月為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使用Mockito進行模擬和測試樁實踐過程

    Java使用Mockito進行模擬和測試樁實踐過程

    本文介紹了Mockito框架的基本概念、使用方法和進階用法,包括模擬對象、測試樁、參數(shù)匹配、異步測試、void方法的樁方法和驗證、mock靜態(tài)方法和final方法等內(nèi)容,Mockito是一個流行的Java模擬框架,具有簡單易用、支持廣泛、文檔豐富的優(yōu)點
    2026-04-04
  • SpringBoot集成Flink-CDC實現(xiàn)對數(shù)據(jù)庫數(shù)據(jù)的監(jiān)聽問題

    SpringBoot集成Flink-CDC實現(xiàn)對數(shù)據(jù)庫數(shù)據(jù)的監(jiān)聽問題

    Flink CDC（Flink Change Data Capture）是一種基于數(shù)據(jù)庫日志的CDC技術(shù),它實現(xiàn)了一個全增量一體化的數(shù)據(jù)集成框架,這篇文章主要介紹了SpringBoot集成Flink-CDC,實現(xiàn)對數(shù)據(jù)庫數(shù)據(jù)的監(jiān)聽,需要的朋友可以參考下
    2024-07-07
  • SpringBoot之Profile的兩種使用方式詳解

    SpringBoot之Profile的兩種使用方式詳解

    當在不同的環(huán)境下,想通過修改配置文件來連接不同的數(shù)據(jù)庫,比如在開發(fā)過程中啟動項目時,想連接開發(fā)環(huán)境對應(yīng)的數(shù)據(jù)庫,可以在配置文件中指定environment=dev,其他環(huán)境類似,此時就需要用到Spring為我們提供的Profile功能,本文給大家介紹了SpringBoot之Profile的兩種使用方式
    2024-10-10
  • java圖像處理之倒角距離變換

    java圖像處理之倒角距離變換

    這篇文章主要為大家詳細介紹了java圖像處理之倒角距離變換的相關(guān)資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2018-01-01
  • 初識Java8中的Stream

    初識Java8中的Stream

    lambda表達式是stream的基礎(chǔ)，接下來通過實例代碼給大家詳細介紹java8中的stream，感興趣的朋友一起看看吧
    2017-08-08
  • java中的編碼轉(zhuǎn)換過程(以utf8和gbk為例)

    java中的編碼轉(zhuǎn)換過程(以utf8和gbk為例)

    這篇文章主要介紹了java中的編碼轉(zhuǎn)換過程(以utf8和gbk為例)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2023-04-04
  • java中樂觀鎖與悲觀鎖區(qū)別及使用場景分析

    java中樂觀鎖與悲觀鎖區(qū)別及使用場景分析

    本文主要介紹了java中樂觀鎖與悲觀鎖區(qū)別及使用場景分析,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2024-08-08
  • Spring+Mybatis動態(tài)切換數(shù)據(jù)源的方法

    Spring+Mybatis動態(tài)切換數(shù)據(jù)源的方法

    這篇文章主要為大家詳細介紹了Spring+Mybatis動態(tài)切換數(shù)據(jù)源的方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2018-01-01
  • Java CAS基本實現(xiàn)原理代碼實例解析

    Java CAS基本實現(xiàn)原理代碼實例解析

    這篇文章主要介紹了Java CAS基本實現(xiàn)原理代碼實例解析,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2020-07-07
  • Java實現(xiàn)常見的排序算法代碼實例

    Java實現(xiàn)常見的排序算法代碼實例

    這篇文章主要介紹了Java實現(xiàn)常見的排序算法代碼實例,按照思路實現(xiàn)了以下幾個排序算法(冒泡排序、直接插入排序、直接選擇排序、快速排序),方便日后用到,特此記錄一下,需要的朋友可以參考下
    2023-11-11

最新評論

宝兴县| 淄博市| 华坪县| 荔波县| 丰都县| 宾川县| 盱眙县| 石阡县| 平凉市| 秦皇岛市| 嘉兴市| 大竹县| 全椒县| 洛扎县| 石首市| 南靖县| 太仓市| 子长县| 子长县| 兴城市| 伊金霍洛旗| 太仆寺旗| 东乡族自治县| 肥乡县| 曲靖市| 印江| 醴陵市| 津南区| 宝清县| 崇仁县| 三河市| 驻马店市| 澄迈县| 黄龙县| 城口县| 曲靖市| 宝山区| 富蕴县| 河西区| 宣城市| 新郑市|