用SQL實現 excel常用操作（附一些面試原題）

01-26

SQL，數據分析崗的必備技能，你可以不懂Python，R，不懂可視化，不懂機器學習。但SQL，你必須懂。要不然領導讓你跑個數據來匯......，哦不，你不懂SQL都無法入職數據分析崗，更別說領導了。

SQL難嗎？說實話，要寫好，很難很難。但要通過SQL筆試這關，並不難。相信大夥都使用過Excel，用SQL實現excel 常用操作去學，感覺會比較具體。我自身也剛入數據崗不久，本文也是為自己鞏固一下SQL。

姊妹篇：用Python實現excel 14個常用操作

數據是網上找到的銷售數據，命名為sale，長這樣：

一、關聯公式:Vlookup

vlookup是excel幾乎最常用的公式，一般用於兩個表的關聯查詢等。所以我先創建一個新表：複製sale表並篩選出地區僅為廣州的，命名為sale_guang。

create table sale_guang SELECT * from sale where city="廣州";

需求：根據訂單明細號關聯兩表，並且sale_guang只有訂單明細號與利潤兩列

SELECT * from sale ainner JOIN(SELECT ordernum,profit from sale_guang) bon a.`ordernum`=b.`ordernum`;

二、對比兩列差異

需求：對比sale的訂單明細號與sale_guang訂單明細號的差異；

SELECT * from sale aWHERE a.ordernum not in (SELECT b.ordernum from sale_guang b);

三、去除重複值

需求：去除業務員編碼的重複值

SELECT * FROM salewhere salesnum not in (SELECT salesnum from sale GROUP BY salesman HAVING COUNT(salesnum)>1)

四、缺失值處理

需求：用0填充缺失值或則刪除有地區名稱缺失值的行。

#用0填充：update sale set city = 0 where city = NULL#刪除有缺失值的行：delete from sale where city = NULL;

五、多條件篩選

需求：想知道業務員張愛，在北京區域賣的商品訂單金額大於等於6000的信息。

SELECT * from sale where salesman = "張愛" and city = "北京"and orderaccount >=6000;

六、模糊篩選數據

需求:篩選存貨名稱含有"三星"或則含有"索尼"的信息。

SELECT * from sale where inventoryname like "%三星%" or 存貨名稱 like "%索尼%";

七、分類匯總

需求:北京區域各業務員的利潤總額。

SELECT city,sum(`profit`) from saleWHERE city = "北京"GROUP BY `city`;

八、條件計算

需求：存貨名稱含「三星字眼」並且稅費高於1000的訂單有幾個？這些訂單的利潤總和和平均利潤是多少？

#有多少個？SELECT COUNT(*) from sale where inventoryname like "%三星%"and `tax` > 1000 ;#這些訂單的利潤總和和平均利潤是多少？SELECT `ordernum`,SUM(profit),AVG(`profit`) from sale where inventoryname like "%三星%"and `tax` > 1000 GROUP BY `ordernum`;

九、刪除數據間的空格

需求：刪除存貨名稱兩邊的空格。

SELECT trim(inventoryname) from sale;

十、合併與排序列

需求：計算每個訂單號的成本並從高到低排序（成本 = 不含稅金額 - 利潤）

SELECT city,ordernum,(Nontaxamount - profit) as cost from saleorder by cost DESC;

總結：結構化查詢語言(Structured Query Language)簡稱SQL，果然和它名字一樣，查詢起來得心應手，但做想做數據處理方面，能明細感受到比Python和excel吃力（也可能是我還沒學好orz）。

貼一些我在面試時遇到過的SQL筆試題吧：

某數據服務公司：

Student

Score

（1）查詢Student表中的所有記錄的Sname、Ssex和Class列。

select sname,ssex,class from student;

（2）查詢Score表中成績在60到80之間的所有記錄。

select * from score between 60 and 80;

（3）查詢95033班和95031班的平均分。

select class,avg(degree) from Score ajoin student b on a.sno = b.snoGROUP BY CLASS；

總之是比較簡單的SQL筆試題了，當時很快就寫完了。實際上這不是原題，不過我有印象就是考察這幾個知識點，並且蠻簡單的。

某手游公司的SQL筆試題（原題）

（1）建立表Student的語句寫下來，表Student是由學好Sno,姓名Sname,性別Ssex,年齡Sage,所在系Sdept五個屬性組成，其中學號屬性不能為空，並且其值是唯一的。

create table Student_new (sno varchar(20) PRIMARY KEY,sname varchar(10),ssex char(2),sage int,sdept varchar(25))；

（2）在student 表中查詢Sdept是「計算機」的學生所有信息並按SNO列排序。

select * from student where sdept = "計算機" order by sno ;

（3）在以上三個表中查詢Ccredit為5並且Grade大於60的學生的學號、姓名和性別。

select a.sno,a.sname,a.ssex from student ajoin (Course b ,SC c)on a.sno=c.sno and b.cno =c.cno where Ccredit = 5 and Grade > 60；

某互聯網金融公司SQL筆試題（原題）

（1）表A和表B的交集：

SELECT a.cus_id from `表a` as aINNER JOIN `表b` as bon a.cus_id=b.cus_id；

（2）表A和表B的並集：

SELECT * from `表a`UNIONSELECT * from `表b`；

（3）表A和表B的對稱差：

SELECT * from `表a` where cus_id not in (SELECT * from `表b`)UNIONSELECT * from `表b` where cus_id not in (SELECT * from `表a`)；

（4）表A中存在但表B中不存在：

SELECT * from `表a`WHERE cus_id not in (SELECT cus_id from `表b`)；

哇，恭喜你看到最後，給一個彩蛋給你！

這篇技術博客總結的十分好，語句忘記了可以隨時翻閱：

數據分析中的SQL整理 - CSDN博客

-------------------------------------------------------------------------------------------------

感謝閱讀，覺得有用的麻煩點個贊！

另外，因各種原因，我不會在目前所在「數據運營」崗轉正。唔，下周開始在職找工作。如果有廣州或深圳的數據分析或數據運營崗甚至數據挖掘崗(哇！)職位的HR，覺得我還行的話（噗），請務必砸向我！

(2017.10.30已入職新公司)

感恩。