一道簡單的sql語句題

這是很早之前面的，第一次面數據分析的面試，當時還傻乎乎的以為數據分析和數據挖掘是一回事呢。結果才發現，數據分析崗位大多注重的是資料庫的能力，比如sql語句的考察，hive的考察，以及一些運營思維的考察，所以第一次面試就很悲劇啦，不過題目還是很有代表性的。其他的不寫了，這裡只分享一個關於sql的題目。

1、問題引出

現在有兩個數據表，一個數據表記錄司機的信息，比如司機id，司機姓名，司機註冊時間等等，一個數據表記錄一天的訂單情況，比如訂單ID，訂單司機id，訂單時間。寫sql語句，返回每個司機今天最早的一筆訂單。兩個數據表如下圖所示：

用戶表userinfo

訂單表orderinfo

2、錯誤思路

好了，模擬的數據我們準備完畢了，接下來我們就要開動腦筋解決這個問題了，想了半天，腦子裡蹦出這麼一個想法，這不很簡單麼，我們先把兩個表連結起來，然後按照用戶進行一個分組，然後對數據排序，最後輸出第一個記錄不就好了，所以，我們寫了如下的sql語句：

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid order by orderinfo.ordertime limit 1

信心滿滿地運行，發現 報錯了！

這個only_full_group_by是什麼鬼喲。百度了一下，這好像是mysql5.7版本的新特性，按照網上的方法，執行如下的sql語句就可以取消這個模式：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

再次執行我們上面的sql語句，就當我們以為大功告成之時，沒想到結果只輸出了一條，而不是所有的用戶的每一條記錄：

我們來探究一下原因，是什麼出現了問題，我們一步步分析，首先我們執行表連結語句：

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid

結果正確，輸出如下：

隨後我們加入group by 語句：

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid

在groupby語句的時候，已經是返回每個用戶的一條記錄了：

剩下的兩不就不用解釋了，order by將上面返回的三條記錄進行一個排序，最後limit 1返回了一條結果。所以我們找到了問題所在，就是這個group by的問題，它只能返回每一組的一行。

你可能會想，既然groupby只能返回一行，我們返回min(ordertime)不就好了：

select name,sex,register,min(ordertime) from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid

結果輸出為：

哇，結果是對的哎。結果真是對的麼？如果我們在orderinfo裡面加入了新的一列，乘客姓名，將orderinfo表變為如下的形式：

實在沒有多餘的腦細胞去想複雜的名字了，不過這已經足以讓我們來解決問題了。繼續運行上面的代碼,結果如下：

結果並不對，張三的第一個用戶應該是二號，細心的你可能已經發現問題了，還是group by的問題，它返回的是連結之後分組的第一條記錄，min(ordertime)相當於是不在表中的一個新加入的欄位，它的值通過min函數計算而來，所以會出現上面的結果。

3、看似正確的思路

那麼解決這個問題的正確姿勢是什麼呢？在融360面試的時候，我被問到了類似的問題，吸取在滴滴面試的教訓，我們用一個子查詢來解決這個問題：

select name,sex,register,ordertime,orderuser from userinfo,orderinfo where userinfo.id = orderinfo.userid and orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)

結果輸出如我們所預料：

可以看到，子查詢返回的每個用戶的當天最早的訂單時間，然後外層查詢用一個in，返回訂單時間在最早訂單時間列表裡的記錄。

當然，你可能會說，有的司機可能沒有訂單，但我們也想要返回這個司機的信息，比如我在userinfo表裡添加一條龍六的信息，這時候，用如上的語句就不行了，因為上面相當於內連結，我們這時候要考慮左外連結，語句變為：

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)

結果輸出如下：

咦，結果輸出好像跟我們之前沒有什麼差別？這是為什麼呢？細心的你可能發現了，我們用了where子句限定了ordertime的取值範圍，所以不會出現那些沒有訂單信息的用戶，所以我們還要對語句作如下修改，讓ordertime可以為Null值：

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid) or orderinfo.ordertime is Null

結果正確輸出：

4、再思考

就當我信心滿滿以為這樣就結束並把帖子發出去的時候，群裡的專家提出了疑問，如果有不同司機在同一時間接了訂單怎麼辦？記錄可能如下：

這時候我們就會發現，運行上面的代碼，某些用戶會返回兩條數據，因為兩個用戶在同一時間接到了訂單，對於其中一個用戶來說，是當天的第一筆，對於另一個用戶來說，不是當天的第一筆，這樣就會出現問題了：

5、真正的正確思路5.1 三表連結

大佬提供了兩種解決方案，一種是把子查詢的結果作為一個新表，然後利用三表連結：

select name,sex,register,ordertime,orderuser from userinfo,orderinfo,(select userid,min(ordertime) as ordertime2 from orderinfo group by userid) as orderinfo2 where userinfo.id = orderinfo.userid and orderinfo.ordertime = orderinfo2.ordertime2 and orderinfo.userid = orderinfo2.userid

結果如下：

5.2 使用over函數(mysql不支持)

上面的結果是正確的，不過太麻煩了吧，於是我們還有第二種解決方案，使用row_number()/rank()/dense_rank() over(partition by)，這個在mysql中並沒有實現，在oracle或者sql server中是有實現的。

不過我們還是要來看一下這一語法的基本用法：

over()函數：

over不能單獨使用，要和分析函數：rank(),dense_rank(),row_number()等一起使用。
其參數：over（partition by columnname1 order by columnname2）
含義：按columname1指定的欄位進行分組,並按照
例如：employees表中，有兩個部門的記錄：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部門10中進行薪水的排名，在部門20中進行薪水排名。

row_number()函數

row_number()函數用於返回根據over函數分組排序結果的編號。例如row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組，在分組內部根據 COL2排序，而此函數計算的值就表示每組內部排序後的順序編號（組內連續的唯一的)
比如下面的例子中，我們按照部門進行分組，然後按照薪水進行降序排序，最後一列表示排序後的組內排名。

row_number()在我們這道題目的背景下是適用的，不過在其他的場景，比如按照每個部分進行分組，再按照工人的薪資進行降序排序，如果有兩個人的薪資相同，這兩個人的row_number值不會相同，這種情況下row_number()函數就不再適用，我們可以考慮rank()或者dense_rank()函數與over函數結合使用。

不過，這也引出了row_number()函數另一個比較有趣的作用，根據某幾列進行去重：假設表TAB中有a,b,c三列，可以使用下列語句刪除a,b,c都相同的重複行。

DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE )   WHERE ROW_NO>1  

rank()函數

rank() over()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）

dense_rank()函數

dense_rank() over()是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重複值的 ．

好了，回到我們的題目，在oracle等其他資料庫中，我們可以用下面的語法進行解決，但很遺憾，mysql不行(下面的語句沒有真正測試過，因為在mysql環境中無法執行，如果有錯誤，歡迎大家指正！）

select name,sex,register,ordertime,orderuser from userinfo,（select *,row_number() over(PARTITION by userid order by ordertime) as tn from orderinfo) as t1 where userinfo.id = t1.userid and t1.tn = 1

上面語句中的row_number()完全可以換做rank()或者dense_rank()。同時，使用上面這種語法，不僅僅是最早的一筆訂單，最早的5筆，10筆都可以計算出，功能十分強大。

5.3 mysql模擬實現rank_over

mysql沒有row_number()/rank()/dense_rank() over(partition by)這樣高級的sql語法，不過我們可以通過編程的方式來模擬實現類似的功能，下面給出了具體的代碼：

select u.name,u.sex,u.register,o.ordertime,o.orderuser from userinfo as u,(select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b order by orderinfo.userid,orderinfo.ordertime) as o where u.id = o.userid and o.rank = 1

輸出結果正確如下：

上面的代碼中用到了mysql變量的知識，首先，我們大體講一下mysql中變量的相關知識。

mysql變量

mysql中變量不用事前申明，在用的時候直接用「@變量名」使用就可以了。
第一種用法：set @num=1; 或set @num:=1; //這裡要使用變量來保存數據，直接使用@num變量
第二種用法：select @num:=1; 或 select @num:=欄位名 from 表名 where ……
注意上面兩種賦值符號，使用set時可以用「=」或「：=」，但是使用select時必須用「：=賦值」

使用變量添加行號

我們可以設置一個初始行號，接下來在 select語句中不斷改變行號的值即可：

set @i = 0;select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo

如果使用一句話，我們可以將設置初始值的過程放在from後面：

select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo,(select   @i:=0)   as  it

運行效果如下：

上面的效果得以實現，得益於mysql中變量在select被循環賦值的特性，即每取出一行，i的值都會變化一次，而在sql server中，i不會被循環賦值，所有列的值都是最後一次的i值。

if語句

mysql中if語句的語法如下：

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，則 IF()的返回值為expr2; 否則返回值則為 expr3。IF() 的返回值為數字值或字符串值，具體情況視其所在語境而定。
比如下面的例子,我們根據司機的註冊時間劃分司機類型：

select name,if(register > '2017-08-05','A','B') as type from userinfo

輸出結果為：

有了上面的知識儲備之後，我們之前實現的mysql語句也就不難理解了，我們首先在內部生成了一個新表o，新表o對司機進行了分組，並按照接單時間先後進行了排序：

select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b

可以看到，內部查詢的輸出如下，接下來就是簡單的兩表連結和篩選了：

6、總結

學習是一個不斷循環迭代的過程，這道題從最初的在滴滴面試中幼稚的group by 想法，到融360面試時自認為正確的解法，再到被大佬質疑從而繼續思考，最終到基本get到解題的正確姿勢，經歷了一系列迭代的過程。

希望我們在以後的學習過程中，能夠不斷的舉一反三，將知識一步步的掌握紮實。

參考文章：

ROW_NUMBER() OVER()函數用法：http://www.cnblogs.com/alsf/p/6344197.html
mysql實現oracle分析函數功能 over：http://blog.csdn.net/mengtianyalll/article/details/45767603
MySql 申明變量以及賦值：
http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html
mysql資料庫 實現類似標記序號的偽列：
http://blog.csdn.net/ystyaoshengting/article/details/6904627
MySQL的if，case語句使用總結：
http://outofmemory.cn/code-snippet/1149/MySQL-if-case-statement-usage-summary