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

更新時間：2024年10月30日 10:58:30 作者：程序猿進(jìn)階

本文詳細(xì)討論了如何將行數(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)化為：

姓名	月工資	年獎金
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ù)表達(dá)式時，條件判斷需要使用 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 條件是在臨時表生成好后，再對臨時表進(jìn)行過濾的條件。

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é)果集進(jìn)行并集操作，不包括重復(fù)行，同時進(jìn)行默認(rèn)規(guī)則的排序；Union All：對兩個結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；注意 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é)生平均成績及其名次：重點(diǎn)是名次的獲取，通過變量 @i 進(jìn)行遞增獲取。

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ù)到下一個月進(jìn)行判斷,太麻煩。

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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Java面試題解析之判斷以及防止SQL注入

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

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

目錄

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

二、準(zhǔn)備工作：

三、練習(xí)題

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

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

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

目錄

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

二、準(zhǔn)備工作：

三、練習(xí)題

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

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

二、準(zhǔn)備工作：

三、練習(xí)題