1. 高頻sql面試問題
SQL（結構化查詢語言）是一種設計用於檢索和操作數據的資料庫。它是美國國家標准協會（ANSI）的標准。此語言用於執行選擇，更新，刪除和插入等數據任務。
表是在具有列和行的模型中設計的數據集合。在表中，指定了列數，稱為欄位，但未定義行數，稱為記錄。
DBMS是一個控制數據維護和使用的程序。它被認為是管理數據的文件管理器。有四種類型的DBMS：
最有用的DBMS是Relational DBMS。它為數據提供了一個關系運算符。
SQL命令分為以下類型：
它包含來自一個或多個表的行和列，可以定義為虛擬表。它消耗的內存較少。
句法：
Join用於從相關的行和列中檢索數據。它在兩個或多個表之間工作，並且它從兩個表返回至少一個匹配。
連接類型是：
資料庫查詢是資料庫表中的數據請求。查詢可以是選擇查詢或任何其他類型的查詢。
子查詢是查詢的一部分。外部查詢已知主查詢，內部查詢識別子查詢。始終首先執行子查詢，並將結果傳遞給主查詢。
Autoincrement是一個關鍵字，用於在表中插入新記錄時生成數字。
它可用於設置表中數據類型的限制。在創建或更新表語句時，可以使用約束。一些限制是：
SQL中有不同類型的鍵：
規范化是一種設計技術，它以減少數據依賴性的方式排列表。它將表分成小模塊並按關系鏈接。
非規范化是一種優化方法，我們將多餘的數據增加到表中，並在規范化後應用。
存儲過程是一組SQL語句，用作訪問資料庫的函數。為了減少網路流量並提高性能，我們使用存儲過程。
句法：
索引用於加速查詢的性能。它可以更快地從表中檢索數據。可以在一組列上創建索引。
聚簇索引 - 它有助於輕松檢索數據，並且只有一個聚簇索引與一個表一起分配。它會更改記錄在資料庫中的保存方式。
非聚集索引 - 與聚簇索引相比，非聚簇索引很慢。並且在非集群索引的情況下，該表可以具有多個索引。它為表創建一個對象，該表是搜索後指向表的一個點。
觸發器 被 用來執行對表中的特定動作，諸如插入，更新或刪除 。它是一種 存儲過程 。動作和事件是觸發器的主要組成部分。執行Action時，事件響應該操作而出現。
通常，這些屬性稱為ACID。它們有助於資料庫事務。
A tomicity -在一個事務中連接兩個或更多個單獨的數據塊，或者所有的塊都致力於，或者一個都不。
C onsistency - 事務或者生成新的有效數據狀態，或者如果發生任何失望，則在事務啟動之前將所有數據返回到其狀態。
I solation - 正在進行且尚未提交的事務必須繼續與任何其他操作隔離。
D urability -在此操作中，系統保存提交的數據，每當事件發生故障和系統啟動後，所有的數據是可用的正確的位置。
SQL語句分為幾類：
它被定義為通過為查詢提供條件來設置結果集的限制。他們從整個記錄中過濾掉一些行。
一些SQL CLAUSES是WHERE和HAVING。
它是一個返回單個值的數學函數。
SQL中的聚合函數是：
為了操作字元串，我們使用String Function。其中一些是：
排序規則 是一 組規則，用於確定數據如何通過比較進行排序 。例如使用一組規則存儲的字元數據，這些規則定義了正確字元的序列以及類型，重音和區分大小寫。
在系統內存中執行SQL語句時，會創建一個臨時工作區，稱為Cursor。在select語句中，游標存儲了信息。游標可以使用多行，但一次只能處理一行。這組行稱為活動集。
游標有兩種類型：
SQL伺服器是Microsoft關系資料庫管理系統（RDBMS）的一種類型或示例。它在IT氛圍中提供廣泛的事務處理和商業智能。
運算符是一個保留字，主要用於SQL語句的WHERE子句中以進行操作。
空值是沒有值的欄位。它與Zero不同。假設有一個表，並且在表中有一個欄位，可以在不添加值的情況下將記錄插入欄位，然後該欄位將以NULL值保存。
空白是我們提供的價值。
零隻是一個數字。
數據倉庫被稱為來自多個信息源的中央數據中心。這些數據可用於在線處理和挖掘。
在表中，應該只有一個PRIMARY KEY，但在另一種情況下，UNIQUE KEY可以是任意數量的。
PRIMARY KEYS不允許NULL值，但UNIQUE KEY允許NULL值。
2. 商業分析崗 面經
從8月初到11月初，整整過去了3個月，秋招總算告一段落，只有這個時候才能稍微心安些回顧一下秋招。。。我是工科背景，不想干老本行就在3月份時找到了一份大廠商業分析的實習，所以下面的分享也以商分為主。在遍地程序猿和PM的牛客，這應該是一個比較邊緣的崗位，分享一波面經，裡面也有一些有趣的經歷，希望能對後續有需要的同學一些幫助。
【1】美團，商業分析崗
—流程：統一筆試-群面-業務面-主管面-hr面
—節奏：三天完成所有面試，群面和業務面都是當晚通知結果、並安排第二天繼續面，hr面之後一周收到面試通過的電話
—面試回憶：
①群面：兩組面試題：盒馬鮮生或者拼多多。當時我們組面試的題目是盒馬鮮生相關的，給了一大段材料，閱讀完後讓討論盒馬鮮生未來三年的發展目標、競爭壁壘和三個核心發展策略。當時我們組人很少、只有5個人，加上有2個同學基本不怎麼說話，整個面試過程會出現有時全場沉默的尷尬。最後我和一位男生通過了。後面和hr聊過這次群面，hr說群面時不在於話說多少，而在於能不能說出要點，我當時在目標那塊提到了要重視商品來源（種植地、養殖地）等，算是很重要的點。
②二面-業務面：兩位面試官，全程聊了40min，一部分圍繞簡歷上和商分相關的實習經歷，一部分是對美團業務的了解和分析。美團是出了名的深挖簡歷，簡歷挖的非常非常細，所以建議面試前一定要好好准備簡歷。業務這塊因為面試通知上有寫部門，當時提前准備了一下，聊的還挺順利。
③三面-主管面：面試官是一位很可愛的小姐姐，一點都看不出來已經工作了七八年。當時她對我的專業背景很感興趣，我是學城市規劃的，畢業論文在做一些大數據在城市研究領域的應用。美團目前正在進行一些線下部署，並且本身有很多很多的數據，她希望我能夠利用這些數據、藉助GIS等進行空間分析，進而指導線下的部署。感謝我的專業，沒想到竟然在這里也能用上，可能也正是因為專業背景我才能通過三面，因為其他應聘者的數據分析能力都比我強太多。
④四面-hr面：總監面之後直接被領過去hr面，先聊了下群面，然後她給我介紹了下美團的工作內容、強度和節奏，其他就沒什麼了。
—其他：美團的商分團隊很強大、也很成熟，但估計工作強度和壓力也很大
【2】貝殼，商業分析崗
—流程：當場筆試-業務面-主管面-hr面
—節奏：兩周完成所有面試，周六上午筆試完直接去一面，一周後二面+hr面（插一句：貝殼的商分團隊很厲害，基本都是美團過來的），一個月後收到通過的郵件
—面試回憶：
①筆試題目：一道數據+圖表分析大題，基於此為產品、運營等部門提建議；兩道選做題：SQL、R
②一面-業務面：因為貝殼商分都是從美團挖過來的，所以不可避免的深挖簡歷，當時還被指出了一個邏輯漏洞，對面試官佩服的不行。這個面試官很有趣，問完簡歷後，聊了會筆試題，說這題不是他出的，這題太硬了，只要願意學很容易上手（我剛自學一個月SQL，但題還是做錯了），說咱來點智力題，智商是硬傷，和智商不過關的人合作真的很心累。
第一題：老王是賣衣服的，一件衣服的進價是50元，老王賣了80元，顧客給了一百元給老王，老王沒零錢，於是找鄰居換了100零錢，事後發現100元是假的，於是老王又賠了鄰居100元，請問老王一共虧了多少錢？
第二題：從前有一個商人要僱傭一個工人七天，但是他只有一根金條，而且要求這根金條只能切兩次，工人的工資每天都要發，請問，怎麼切割這根金條？
第三題：估算北京的租房需求量
第一道題剛一開始答錯了，但在他的引導下找到了正確答案；第二題很快答對了；第三題給了他兩種解法，他對第二種比較滿意。面完之後感覺不太好，因為面簡歷時沒怎麼准備、一些內容忘了，犯了這種非常低級的錯誤，但後面的題做的還算可以。
③二面-主管面：不得不說，貝殼的商業分析團隊很優秀，而且面試的思路很清奇[捂臉]。這位大佬的面試一點也不像面試，全程都像在聊天、在傳授經驗。問：你了解互聯網的商業分析崗嗎？我：不是很了解。大佬開始滔滔不絕地和我分享他的見地（篇幅有限、此處省去1萬字）.......關鍵是還都特別犀利和深刻，真的收獲非常大。然後又聊到了拼多多、抖音等很火爆的產品，大佬的見地和一般人很不一樣，他不看好抖音，看好拼多多、但又指出拼多多動作有些太快，一旦有這么大動作、讓阿里爸爸看到商機，很快就會被吃掉，京東和它的後起之秀天貓就是非常好的例子......其他的就不展開了，面完之後真的是好感度+10086
④三面-hr面：三面完之後直接hr面，不得不說這位hrbp知道的也很多[捂臉]，對著三面的話題又展開聊了會兒。然後就是介紹了部門的業務方向。
—其他：貝殼的商分團隊很強大、，很多是從美團挖過來的大佬，關鍵是都還很有趣
【3】京東，商業分析崗
—流程：統一筆試-業務面-主管面-hr電話面
—節奏：五天完成所有面試，業務面當晚通知結果、安排隔天總監面，然後隔天hr電話面，一個月後收到通過的郵件
—面試回憶：
①一面-業務面：一個很nice的小姐姐，看起來很年輕，估計剛工作沒幾年，主要是就著簡歷問，聊的很輕松，10min就結束了
②二面-主管面：這個也是個大佬，我都被面的懷疑人生了，而且覺得鐵定涼。簡單幾句話聊了聊簡歷。問：有沒有帶自己做的一些分析報告之類的。我一驚，之前有準備過京東、天貓、蘇寧的競品分析，但後面一開始秋招就沒繼續做了，實習的內容也不太方便直接拿給他看。然後我就硬著頭皮給他看了沒做完的這份競品分析。被他批的很慘：更像份資料收集和梳理的報告，沒有太多自己的觀點，你對行業的判斷，基本都是基於別人提出了什麼。又聊天貓，批：你的這些觀點都已經過時了。又問SQL，沒怎麼答上來，批：自學的SQL看來也不怎麼樣。然後又問了幾道類似觀察分析的題：
第一題：啤酒瓶為什麼綠色的
第二題：超市為什麼都被放在商場的地下一層？珠寶之類的為什麼都在一層？吃飯的地兒為什麼都在頂層？
大佬說這些題都是在考察你的好奇心。好奇心，很重要。除了上面的第二題答的還可以，其他的都感覺很悲催。面到最後，大佬問還有什麼要問的沒，我就問他：您覺得怎麼才能做好商業分析。答：得有人帶，你可以來我們部門啊。嗯？我心裡一驚，面漏喜色，以為被伸出了橄欖枝。隨即大佬加了一句：如果有機會的話.......
③三面-hr面：（真的特別驚訝二面竟然過了，可能大佬覺得我底子雖差，但還不至於爛泥扶不上牆......）電話聊，為什麼想來京東？怎麼看京東和天貓？你最崇拜的一個人？有時候需要加班能接受嗎？
—其他：後來發的offer是數據分析崗，可能京東的商分就是數分？不太清楚......
【4】第四範式，商業分析崗
—流程：郵件發筆試題-主管面-hr面-聯合創始人面
—節奏：線下給一周的時間做筆試題，四天完成所有面試，一個半月後收到通過的郵件
—面試回憶：
①筆試題目：一第四範式是AI相關的，筆試題只有一道，但工作量非常大，就是從全球TOP15人工智慧&機器學習領域的公司中，選出3家進行4X4維度（4個方面，每個方面再細分成4小方面）的分析。當時還挺喜歡第四範式的，也很看好它的前景，所以很重視這個筆試題，加上期間基本沒什麼面試，所以基本一整周的時間都在做這個，最後也才只做完了兩家公司的案例。因為材料全部需要從英文網站上找，而且信息很散很碎。
②一面-主管面：範式的商業分析團隊很年輕，這位主管是從IBM挖過來的大佬，正在組建商業分析團隊，所以一面就是部門主管面。面試基本全程都在圍繞我做的那份筆試PPT。面完之後說他這邊過了，後面會有一個聯合創始人面，問我有什麼要問的沒。我就問了下聯合創始人面要怎麼准備。這位大佬特別好，說我目前的話可能對公司的業務和產品不太熟悉，這塊需要做下功課。然後互加了微信，聯合創始人面之前，大佬叫我提前過來，親自給我講他們公司的業務、產品和商分的定位、工作內容，整整講了一個小時......真的特別感動
③二面-hr面：很常規的面試，職業規劃？更傾向於去成熟的大公司還是成長中的小公司？秋招進展？
④三面-聯合創始人面：主要是就著簡歷問的，聊的還挺愉快的
—其他：在意向書事件之前我對第四範式的好感度一直很高，加上之前主管專門給我講他們公司的產品和業務體系，覺得這是一家很有溫度的公司。只是意向書這事一出，作為當事人這中間一個月發生的事情，讓我對它的好感度驟降。
洋洋灑灑沒想到已經寫了這么多字[捂臉]......8、9月份我基本也是0offer、非常煎熬的焦慮期，這幾個offer都是最近才陸陸續續收到的，非技術崗的戰線拉的是真的長，以及好事多磨，如果有牛油現在還沒有理想的offer，也暫且先等等，穩住，才能贏......
還有一些沒面到最後，或者是偏產品、游戲方向的經歷沒往上放，也還有很多話想說，都先攢著吧，有空再碼字。
3. 外賣餓了么/美團數據分析師面試必備問題！
一般會在數據分析師面試的時候會問到，關於外賣行業，比一般的行業相對比較特殊，所以分析思路相對會比較復雜且考慮多因素。
下面簡單介紹外賣行業內部的分類：
1，B端，也就是大眾理解的商家端，和天貓類似。是外賣平台的商家們，外賣平台的特殊點就是現在商家端沒有太多的營銷工具來進行私域流量的監督和營銷，主要因為現在整體外賣行業還沒有完全進入精細化運營，因此，此部分沒有特別專業的幫助賣家精細化運營的方式，整體上商家也沒有天貓上的賣家注重這些。
2，C端，這個很好理解就是消費者，每個點外賣的人都是C端消費者。
3，最特殊的就是D端，這個是外賣行業比較特殊的存在，也就是騎手外賣小哥們。其實D端才是外賣行業成本最大的
原因有三
4. 什麼很多的面試要求上，都要求人員掌握數據分析的技能
什麼是數據分析？數據分析是指用適當的統計分析方法對收集來的大量數據進行分析，將它們加以匯總和理解並消化，以求最大化地開發數據的功能，發揮數據的作用。簡單來說，就是通過數據解決任何業務問題。
一個業務人員最重要的能力不是你會什麼技能，具備什麼知識，而是能解決問題，解決問題的前提是發現問題，數據分析恰好可以完成發現問題解決問題的使命。
在日益研究的招聘環境下，不管你是正要進入互聯網產品、運營等業務崗位的在校生，還是1-5 年經驗的運營、產品、營銷、新媒體等從業者，數據分析能力已經是用人單位對業務人員的重要考核點。
今年疫情影響下，相比傳統金融行業求職的一片哀嚎，技術類起薪確實比較香，根據2019年首屆數據科學理學碩士畢業生就業報告，畢業生平均薪資達到了27w，主要集中在互聯網、金融科技、量化領域。
隨著大數據技術逐漸滲透到各行各業，數據科學人才將迎來一波紅利。而對於本科非理工背景，又想在工作中將業務和技術結合起來的同學來說，數據分析(包含大數據類)無疑是不錯的選擇。
今天就給大家分享一下幾個熱門行業數據分析崗位的基本情況：
互聯網互聯網代表：阿里、騰訊、網路、京東、位元組跳動、拼多多、滴滴、美團、shopee（新加坡) 等
1）難度系數：☆☆☆☆☆
2）技能要求：
給大家看看騰訊的數據分析崗位要求
所需能力，主要是三點：
a.必須精通一門編程語言，Python/MATLAB/C++；
b.有金融數據分析的能力；
c. 熟練掌握統計模型及機器學習模型，懂原理、能調包實現，最好能建模
3）薪資水平
券商基金的薪資基本無上限，看個人績效拿獎金，底薪大多20w上下
事業單位系事業單位代表：上交所技術、深交所金融科技、深圳市/區政府及其研究院
1）難度：不太好評估，身邊的樣本較少，技術難度可能低於互聯網，但是由於招聘名額也較少，所以實際競爭比其實不低，同時也會比較關注學歷背景
2）薪資水平：基本和公務員齊平，一線城市的公務員和事業單位待遇都不低，加班和失業的機會也比較少，可以說是性價比較高的一份工作，不說了，就兩個字，羨慕。
5. 面試題目(sql)
1、忍不住想說一句，因為第一題中的欄位類型是
【日期型】，而各種資料庫操作日期型數據有不同的方法，沒有一種共通的方法，所以脫離了資料庫而言沒有一種共通的sql。
2、select
ID,NAME,ADDRESS,PHONE,LOGDATE
from
T
where
ID
in(
select
ID
from
T
group
by
NAME
having
count(*)>1)
order
by
NAME;
3、delete
from
T
where
ID
not
in
(select
min(id)
from
T
group
by
name);
4、update
T
set
T.ADDRESS=(select
E.ADDRESS
from
E
where
E.NAME=T.NAME),
T.PHONE=(select
E.PHONE
from
E
where
E.NAME=T.NAME);
5、這個不同的資料庫也有不同的處理方法，不能脫離資料庫談了。
如：SqlServer或者access可以使用
top
oracle可以使用
rownum
等
---
以上，希望對你有所幫助。
6. 軟體測試SQL面試題
資料庫，無外乎增刪改查：
增：
insert into A表 values (value1，value2，value3......)向A表中新增數據
刪：
delete from A表 ( where id=1) 刪除A表（刪除A表id=1的數據）如果刪除某個測試數據，記得加where條件，否則整張表的數據都被刪除，防止誤操作！不過，數據可以回滾找回。
truncate A表：清除表數據，數據無法回滾。
drop A表：整表結構刪除，即，這張表不存在了。
改：
update A表 set name='test' where id=1 修改id為1的name值為test
查：
1：左關聯和右關聯的區別？
答：左關聯（ left join ）左表為主，左表數據全部顯示，右表顯示關聯數據，無關聯顯示null，右關聯( right join )相反。
2：模糊查詢？
select * from A表 where name like ' %a% ' (name包含a字母)
查詢結果： a ， a bc，b a c，bc a
select * from A表 where name like ' a% ' (name值以a字母開頭)
查詢結果： a ， a bc
select * from A表 where name like ' %a ' (name值以a字母結尾)
查詢結果： a ，bc a
3：統計：count和sum
count統計表的 記錄數 ，sum統計某列數值 總和 。
select count(*) from A表
select count(1) from A表
select count(列名) from A表
區別：
count( * )： 所有記錄，包括null值
count( 1 )：所有記錄，包括null值
count( 列名 )：列名有值得記錄， 不包括null值
執行效率：
以前是count(1)比count(*)快，但現在count(*)底層演算法優化,查詢更快，所以推薦count(*)
統計有效數據的記錄，count(列名)
select sum(列名) from A表 計算列名數值總和。
舉例：
查詢A表中，姓王的用戶量
select count(*) from A where name like '王%'
Plus版
1：去重distinct，查詢不重復記錄的數據
必須放開頭
select distinct 列名 from 表名（查詢所有列名數據，去掉重復數據）
舉例：
表A，查詢考核等級grade，有哪些值
select distinct grade from A
2：分組group by，根據某個欄位分組
select 列名 from 表名 group by 列名，一般會配合聚合函數一起使用
舉例：
表A中，查詢考核等級grade欄位，不同值各多少人
select grade,count(*) from A group by grade
3：limit，查詢結果返回的數量，多用於分頁查詢
select * from 表名 limit i,n i代表查詢結果的索引值，默認從0開始，n返回查詢的結果數。
舉例：
訂單表A，查詢第21條到30條數據
select * from A limit 20,10
可能會問為什麼不能直接用id查詢？因為id不準確，可能存在id不連續的情況。如果某條數據被物理刪除了呢？
何為物理刪除和邏輯刪除？
物理 刪除 ，直接將某條數據，從表中刪除。
邏輯 刪除，僅通過某個欄位標記刪除，實際表中還存在。(比如：is_delete=1代表已刪除，is_delete=0未刪除)
4：排序order by 列名 asc（列名值升序排列）和order by 列名 desc（列名值降序排列）
舉例：
用戶表A，按照用戶id升序（select查詢默認根據主鍵升序，所以升序，不加order by也可以）
select * from A order by id asc同select *from A
用戶表A，按照新建時間倒序
select * from A order by create_time desc
5：in和between查詢某個范圍的數值
舉例：
in：查詢指定數值的數據
between：查詢某個范圍內的數據
舉例：
查詢表A中，id=1和id=10的數據
select * from A where id in(1,10)
查詢表A中，1月份新增的數據
select * from A where create_time between '2022-01-01' and '2022-01-31 23:59:59'
已知有如下4張表：
學生表：student(學號,學生姓名,出生年月,性別)
成績表：score(學號,課程號,成績)
課程表：course(課程號,課程名稱,教師號)
教師表：teacher(教師號,教師姓名)
准備練習數據
1）創建學生表（student）
2）創建成績表（score）
3）創建課程表（course）
4）教師表（teacher）
1）學生表添加數據
2）成績表添加數據
3）課程表添加數據
4）教師表添加數據
簡單查詢
查詢姓「猴」的學生名單
查詢姓名中最後一個字是「猴」字的學生名單
查詢姓名中帶「猴」字的學生名單
查詢姓「孟」老師的個數
匯總分析
1.匯總分析
查詢課程編號為「0002」的總成績
查詢選了課程的學生人數
2.分組
查詢各科成績的最高分和最低分, 以如下的形式顯示：課程號，最高分，最低分
查詢每門課程選修的學生數
3.分組結果的條件
查詢至少選修兩門課程的學生學號
查詢同名同姓學生名單並統計同名人數
分析：條件：怎麼算姓名相同？按姓名分組後人數大於等於2，因為同名的人數大於等於2，分析出這一點很重要
查詢不及格的課程並按課程號從大到小排列
查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列
檢索課程編號為「0004」且分數小於60的學生學號，結果按按分數降序排列
統計每門課程的學生選修人數(超過2人的課程才統計)，要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序
查詢兩門以上不及格課程的同學的學號及其平均成績
第2步：再加上限制條件：
1）不及格課程
2）兩門以上[不及格課程]
4.查詢結構排序，分組的指定條件
查詢學生的總成績並進行排名
查詢平均成績大於60分的學生的學號和平均成績
復雜查詢
查詢課程成績小於60分學生的學號、姓名
【知識點】子查詢
1.翻譯成大白話
1）查詢結果：學生學號，姓名
2）查詢條件：所有課程成績 < 60 的學生，需要從成績表裡查找，用到子查詢
第1步，寫子查詢（所有課程成績 < 60 的學生）
第2步，查詢結果：學生學號，姓名，條件是前面1步查到的學號
查詢沒有學全所有課的學生的學號、姓名
查詢出只選修了兩門課程的全部學生的學號和姓名
查找1990年出生的學生名單
查詢本月過生日的學生
工作中會經常遇到這樣的業務問題：
如何找到每個類別下用戶最喜歡的產品是哪個？
如果找到每個類別下用戶點擊最多的5個商品是什麼？
這類問題其實就是常見的：分組取每組最大值、最小值，每組最大的N條（top N）記錄。
分組取每組最大值，按課程號分組取成績最大值所在行的數據
分組取每組最小值，按課程號分組取成績最小值所在行的數據
每組最大的N條記錄，查詢各科成績前兩名的記錄
第1步，查出有哪些組
我們可以按課程號分組，查詢出有哪些組，對應這個問題里就是有哪些課程號
第2步：先使用order by子句按成績降序排序（desc），然後使用limt子句返回topN（對應這個問題返回的成績前兩名）
第3步，使用union all 將每組選出的數據合並到一起
多表查詢
查詢所有學生的學號、姓名、選課數、總成績
查詢平均成績大於85的所有學生的學號、姓名和平均成績
查詢學生的選課情況：學號，姓名，課程號，課程名稱
7. SQL查詢面試題與答案
SQL查詢面試題與答案
SQL語言是一種資料庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理關系資料庫系統;同時也是資料庫腳本文件的擴展名。下面是我搜集的SQL查詢面試題與答案，歡迎大家閱讀。
SQL查詢面試題與答案一
1.一道SQL語句面試題，關於group by表內容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負
2005-05-09 負
2005-05-10 勝
2005-05-10 負
2005-05-10 負
如果要生成下列結果, 該如何寫sql語句?
勝 負
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-10','勝')
insert into #tmp values('2005-05-10','負')
insert into #tmp values('2005-05-10','負')
1)select rq, sum(case when shengfu='勝' then 1 else 0 end)'勝',sum(case when shengfu='負' then 1 else 0 end)'負' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='勝'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='負'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 勝,b.b1 負 from
(select col001,count(col001) a1 from temp1 where col002='勝' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='負' group by col001) b
where a.col001=b.col001
2.請教一個面試中遇到的SQL語句的查詢問題
表中有A B C三列,用SQL語句實現：當A列大於B列時選擇A列否則選擇B列，當B列大於C列時選擇B列否則選擇C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面試題：一個日期判斷的sql語句?
請取出tb_send表中日期(SendTime欄位)為當天的所有記錄?(SendTime欄位為datetime型，包含日期與時間)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一張表，裡面有3個欄位：語文，數學，英語。其中有3條記錄分別表示語文70分，數學80分，英語58分，請用一條sql語句查詢出這三條記錄並按以下條件顯示出來(並寫出您的思路)：
大於或等於80表示優秀，大於或等於60表示及格，小於60分表示不及格。
顯示格式：
語文 數學 英語
及格 優秀 不及格
------------------------------------------
select
(case when 語文>=80 then '優秀'
when 語文>=60 then '及格'
else '不及格') as 語文,
(case when 數學>=80 then '優秀'
when 數學>=60 then '及格'
else '不及格') as 數學,
(case when 英語>=80 then '優秀'
when 英語>=60 then '及格'
else '不及格') as 英語,
from table
5.在sqlserver2000中請用sql創建一張用戶臨時表和系統臨時表，裡麵包含兩個欄位ID和IDValues,類型都是int型，並解釋下兩者的區別?
------------------------------------------
用戶臨時表:create table #xx(ID int, IDValues int)
系統臨時表:create table ##xx(ID int, IDValues int)
區別:
用戶臨時表只對創建這個表的用戶的Session可見,對其他進程是不可見的.
當創建它的進程消失時這個臨時表就自動刪除.
全局臨時表對整個SQL Server實例都可見,但是所有訪問它的Session都消失的時候,它也自動刪除.
6.sqlserver2000是一種大型資料庫，他的`存儲容量只受存儲介質的限制，請問它是通過什麼方式實現這種無限容量機制的。
------------------------------------------
它的所有數據都存儲在數據文件中(*.dbf),所以只要文件夠大,SQL Server的存儲容量是可以擴大的.
SQL Server 2000 資料庫有三種類型的文件：
主要數據文件
主要數據文件是資料庫的起點，指向資料庫中文件的其它部分。每個資料庫都有一個主要數據文件。主要數據文件的推薦文件擴展名是 .mdf。
次要數據文件
次要數據文件包含除主要數據文件外的所有數據文件。有些資料庫可能沒有次要數據文件，而有些資料庫則有多個次要數據文件。次要數據文件的推薦文件擴展名是 .ndf。
日誌文件
日誌文件包含恢復資料庫所需的所有日誌信息。每個資料庫必須至少有一個日誌文件，但可以不止一個。日誌文件的推薦文件擴展名是 .ldf。
7.請用一個sql語句得出結果
從table1,table2中取出如table3所列格式數據，注意提供的數據及結果不準確，只是作為一個格式向大家請教。
如使用存儲過程也可以。
table1
月份mon 部門dep 業績yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部門dep 部門名稱dname
--------------------------------
01 國內業務一部
02 國內業務二部
03 國內業務三部
04 國際業務部
table3 (result)
部門dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部門名稱dname,b.業績yj as '一月份',c.業績yj as '二月份',d.業績yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部門dep = b.部門dep and b.月份mon = '一月份' and
a.部門dep = c.部門dep and c.月份mon = '二月份' and
a.部門dep = d.部門dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.華為一道面試題
一個表中的Id有多個記錄，把所有這個id的記錄查出來，並顯示共有多少條記錄數。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查詢面試題與答案二
1、查詢不同老師所教不同課程平均分從高到低顯示
SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
2、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理(001)，馬克思(002)，UML (003)，資料庫(004)
[學生ID],[學生姓名],企業管理,馬克思,UML,資料庫,平均成績
SELECT DISTINCT top 3
SC.S# As 學生學號,
Student.Sname AS 學生姓名 ,
T1.score AS 企業管理,
T2.score AS 馬克思,
T3.score AS UML,
T4.score AS 資料庫,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 總分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
3、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 課程ID, Cname as 課程名稱
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
4、查詢學生平均成績及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績)
FROM (SELECT S#,AVG(score) AS 平均成績
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成績 > T2.平均成績) as 名次,
S# as 學生學號,平均成績
FROM (SELECT S#,AVG(score) 平均成績
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成績 desc;
5、查詢各科成績前三名的記錄:(不考慮成績並列情況)
SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
6、查詢每門課程被選修的學生數
select c#,count(S#) from sc group by C#;
7、查詢出只選修了一門課程的全部學生的學號和姓名
select SC.S#,Student.Sname,count(C#) AS 選課數
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查詢所有課程成績小於60分的同學的學號、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查詢沒有學全所有課的同學的學號、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名;
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名;
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');
14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名;
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、刪除學習“葉平”老師課的SC表記錄;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';
16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、2、
號課的平均成績;
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示： 學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分
SELECT S# as 學生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 資料庫
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語
,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成績從低到高和及格率的百分數從高到低順序
SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理(001)，馬克思(002)，OO&UML (003)，資料庫(004)
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企業管理及格百分數
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 馬克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 馬克思及格百分數
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分數
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 資料庫平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 資料庫及格百分數
FROM SC
;8. sql面試題50題（mysql版）
--插入學生表測試數據
insert into Student values(༽' , '趙雷' , -01-01' , '男');
insert into Student values(༾' , '錢電' , -12-21' , '男');
insert into Student values(༿' , '孫風' , -05-20' , '男');
insert into Student values(ཀ' , '李雲' , -08-06' , '男');
insert into Student values(ཁ' , '周梅' , -12-01' , '女');
insert into Student values(ག' , '吳蘭' , -03-01' , '女');
insert into Student values(གྷ' , '鄭竹' , -07-01' , '女');
insert into Student values(ང' , '王菊' , -01-20' , '女');
--課程表測試數據
insert into Course values(༽' , '語文' , ༾');
insert into Course values(༾' , '數學' , ༽');
insert into Course values(༿' , '英語' , ༿');
--教師表測試數據
insert into Teacher values(༽' , '張三');
insert into Teacher values(༾' , '李四');
insert into Teacher values(༿' , '王五');
--成績表測試數據
insert into Score values(༽' , ༽' , 80);
insert into Score values(༽' , ༾' , 90);
insert into Score values(༽' , ༿' , 99);
insert into Score values(༾' , ༽' , 70);
insert into Score values(༾' , ༾' , 60);
insert into Score values(༾' , ༿' , 80);
insert into Score values(༿' , ༽' , 80);
insert into Score values(༿' , ༾' , 80);
insert into Score values(༿' , ༿' , 80);
insert into Score values(ཀ' , ༽' , 50);
insert into Score values(ཀ' , ༾' , 30);
insert into Score values(ཀ' , ༿' , 20);
insert into Score values(ཁ' , ༽' , 76);
insert into Score values(ཁ' , ༾' , 87);
insert into Score values(ག' , ༽' , 31);
insert into Score values(ག' , ༿' , 34);
insert into Score values(གྷ' , ༾' , 89);
insert into Score values(གྷ' , ༿' , 98);
-- 1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數
select c.*,a.s_score as 01課程score,b.s_score as 02課程score from
score a,score b
left join student c
on b.s_id = c.s_id
where a.s_id = b.s_id and a.c_id = ༽' and b.c_id = ༾' and a.s_score > b.s_score;
-- 2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數
select a.* ,b.s_score as 01課程,c.s_score as 02課程 from student a
join score b
on a.s_id=b.s_id and b.c_id = ༽'
left join score c
on b.s_id = c.s_id and c.c_id = ༾'
where b.s_score < c.s_score ;
-- 3、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成績 from student a
join score b
on a.s_id = b.s_id
group by b.s_id having 平均成績 >= 60;
備註：round[avg(成績),1]里,round是四捨五入函數，1代表保留1位小數
-- 4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績
-- (包括有成績的和無成績的)
select b. ,round(avg(a.s_score),2) as 平均成績 from
student b
left join score a on b.s_id = a.s_id group by a.s_id having 平均成績 < 60
union
select b. ,0 as 平衡成績 from student b where b.s_id not in (select s_id from score);
-- 5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績
select a.s_id,a.s_name,count(b.c_id) as 選課總數 ,sum(b.s_score) as 總分 from student a
left join score b
on a.s_id = b.s_id group by s_id ;
-- 6、查詢"李"姓老師的數量
select count(*) as 李姓老師數量 from teacher where t_name like '李%'
-- 7、查詢學過"張三"老師授課的同學的信息
select a.* from student a join score b
on a.s_id = b.s_id
where b.c_id in (select c.c_id from course c
join teacher d on c.t_id = d.t_id where d.t_name = '張三');
-- 8、查詢沒學過"張三"老師授課的同學的信息
select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in
(select s_id from score where c_id =
(select c_id from course where t_id =
(select t_id from teacher where t_name = '張
三'))) group by a.s_id;
-- 9、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息
select * from student where s_id in
(select a.s_id from score a join score b on a.s_id = b.s_id
where a.c_id = ༽' and b.c_id = ༾');
-- 10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息
select * from student where s_id in
(select s_id from score where c_id = ༽' )
and s_id not in (select s_id from score where c_id = ༾' );
-- 11、查詢沒有學全所有課程的同學的信息
select * from student where s_id not in
(select s_id from score group by s_id having count(c_id) = 3);
-- 12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息
select distinct a.* from student a left join score b
on a.s_id = b.s_id where b.c_id in
(select c_id from score where s_id = ༽') and a.s_id != ༽' ;
注意：distinct是去重的
-- 13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息
select * from student where s_id in
(select s_id from score group by s_id having count(c_id) =
(select count(c_id) from score where s_id = ༽') and s_id not in
(select s_id from score where c_id not in
(select c_id from score where s_id = ༽')) and s_id != ༽');
-- 14、查詢沒學過"張三"老師講授的任一門課程的學生姓名
select s_name from student where s_id not in
(select s_id from score where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name ='張三')));
-- 15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成績 from score a
left join student b on a.s_id = b.s_id
where s_score < 60 group by s_id having count(1) >=2;
或者試試
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成績 from score a
left join student b on a.s_id = b.s_id
where a.s_score < 60 group by a.s_id having count(*) >=2;
-- 16、檢索"01"課程分數小於60，按分數降序排列的學生信息
select a.* ,b.c_id ,b.s_score from student a
left join score b on a.s_id = b.s_id
where b.c_id = ༽' and b.s_score < 60
order by b.s_score desc;
-- 17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
select a.s_name ,
sum(case when b.c_id = ༽' then s_score else null end ) as 語文,
sum(case when b.c_id = ༾' then s_score else null end ) as 數學,
sum(case when b.c_id = ༿' then s_score else null end ) as 英語,
round(avg(s_score),2) as 平均成績
from student a left join score b on a.s_id = b.s_id group by a.s_name
order by 平均成績 desc;
-- 18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
--及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90
select b.c_id,b.c_name,
max(a.s_score) as 最高分,
min(a.s_score) as 最低分,
round(avg(a.s_score),2) as 平均分,
round(sum(case when a.s_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,
round(sum(case when a.s_score>= 70 and a.s_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,
round(sum(case when a.s_score>= 80 and a.s_score <90 then 1 else 0 end)/count(s_id),2) as 優良率,
round(sum(case when a.s_score>= 90 then 1 else 0 end)/count(s_id),2) as 優秀率
from score a
left join course b
on a.c_id = b.c_id group by b.c_id;
-- 19、按各科成績進行排序，並顯示排名
第一種：
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2;
如果看不懂用第二種方法：
SELECT a.c_id,a.s_id,a.s_score,COUNT(b.s_score)+1 AS 排名
FROM score a LEFT JOIN score b ON a.s_score<b.s_score AND a.c_id = b.c_id
GROUP BY a.c_id,a.s_id,a.s_score ORDER BY a.c_id,排名,a.s_id ASC
-- 20、查詢學生的總成績並進行排名
set @rank:=0;
select * ,(@rank:=@rank+1) as rank from
(select s_id ,sum(s_score) as 總成績 from score
group by s_id order by 總成績 desc) tb1;
-- 21、查詢不同老師所教不同課程平均分從高到低顯示
select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a
left join student b on a.s_id = b.s_id
left join course c on a.c_id = c.c_id
left join teacher d on c.t_id = d.t_id group by a.c_id
order by 平均分 desc;
-- 22、查詢所有課程的成績第2名到第3名的學生信息及該課程成績
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;
-- 23、統計各科成績各分數段人數：課程編號,課程名稱,[100-85],(85-70],(70-60],(0-60]及所佔百分比
select b.c_id,b.c_name ,
sum(case when a.s_score >=85 then 1 else 0 end) as 100-85 ,
concat(round(100 sum(case when a.s_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,
sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end) as 85-70 ,
concat(round(100 sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,
sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end) as 70-60 ,
concat(round(100 sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,
sum(case when a.s_score <60 and a.s_score >=0 then 1 else 0 end) as 60-0 ,
concat(round(100 sum(case when a.s_score <60 and a.s_score >=0 then 1
else 0 end)/count( ),2),'%') as 百分比
from score a left join course b on a.c_id = b.c_id group by b.c_id;
-- 24、查詢學生平均成績及其名次
select tb1.*,(@rank:=@rank +1 ) as rank from
(select s_id ,round(avg(s_score),2) as 平均成績 from score
group by s_id order by 平均成績 desc) tb1,(select @rank:=0) b;
-- 25、查詢各科成績前三名的記錄
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 <4;
-- 26、查詢每門課程被選修的學生數
select c_id ,count(s_id) as 選修人數 from score group by c_id;
-- 27、查詢出只有兩門課程的全部學生的學號和姓名
select a.s_id ,b.s_name from score a left join student b on a.s_id = b.s_id group by s_id having count(*) = 2;
-- 28、查詢男生、女生人數
select sum(case s_sex when '男' then 1 else 0 end) as 男生人數,
sum(case s_sex when '女' then 1 else 0 end) as 女生人數 from student;
-- 29、查詢名字中含有"風"字的學生信息
select * from student where s_name like '%風%'
-- 30、查詢同名同性學生名單，並統計同名人數
--略，不想寫
-- 31、查詢1990年出生的學生名單
select * from student where s_birth like %'
-- 32、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
select c_id ,round(avg(s_score),2) as 平均成績 from score group by c_id order by 平均成績 desc, c_id asc;
-- 33、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成績 from score a
left join student b on a.s_id = b.s_id group by a.s_id having 平均成績>=85;
-- 34、查詢課程名稱為"數學"，且分數低於60的學生姓名和分數
select b.s_name ,a.s_score from score a
left join student b on a.s_id = b.s_id
where a.c_id=(select c_id from course where c_name = '數學')and a.s_score < 60;
-- 35、查詢所有學生的課程及分數情況；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 語文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 數學,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英語
from score a right join student b on a.s_id = b.s_id group by b.s_name
-- 36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 語文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 數學,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英語
from score a right join student b on a.s_id = b.s_id group by b.s_name having 語文>= 70 or 數學>= 70 or 英語>= 70 ;
-- 37、查詢不及格的課程
select a.s_id,a.c_id,b.c_name,a.s_score from score a
left join course b on a.c_id = b.c_id where a.s_score<60;
--38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名；
select a.s_id,b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = ༽' and a.s_score>=80;
-- 39、求每門課程的學生人數
select c_id,count(*) as 學生人數 from score group by c_id ;
-- 40、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績
select a.*,b.c_id,max(b.s_score) as 最高成績 from student a
right join score b on a.s_id = b.s_id
group by b.c_id
having b.c_id = (select c_id from course
where t_id = (select t_id from teacher where t_name = '張三'));
-- 41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
--（這題我搞不清題目是什麼意思,是指查找學生個體參加了的所有課程的成績各不相同的那個學生信息呢？還是所有課程之間做對比呢，我更傾向於理解為前者）
--理解為前者的寫法
select * from
(select * from score group by s_id,s_score) tb1
group by s_id having count(*) = 1;
--理解為後者的寫法
select distinct a.s_id,a.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score;
-- 42、查詢每門課程成績最好的前兩名
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2
join student b on tb2.s_id = b.s_id where 排名 <3;
-- 43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人相同，按課程號升序排列
select c_id ,count(*) as 選修人數 from score group by c_id having 選修人數>5 order by 選修人數 desc , c_id asc;
-- 44、檢索至少選修兩門課程的學生學號
select s_id from score group by s_id having count(*) >= 2;
-- 45、查詢選修了全部課程的學生信息
select * from student where s_id in
(select s_id from score group by s_id having count(*) = 3)
--46、查詢各學生的年齡
select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age
from student
-- 47、查詢本周過生日的學生
---(實現得並不完全，因為例如出生月日為『01-01』在每一年可能會輸入不同周）
select * from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;
-- 48、查詢下周過生日的學生
select * from student
where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));
-- 49、查詢本月過生日的學生
select * from student where date_format(s_birth,'%m') = date_format(now(),'%m')
-- 50、查詢下月過生日的學生
select * from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')