一道SQL題的7種解法

有這么一張表，表中有兩列數據，一列是用戶id，一列是訂單金額，一個用戶可能存在多個訂單，也就對應了多個訂單金額。

找出所有訂單金額都大于20元的用戶id。

先來提下不同的解題思路：

1.找出訂單小于20的id 再left join

2.聚合得到每個用戶的訂單數，和大于20的訂單數，比一比

3.用窗口函數找到min 再用where 篩選

4.均大于計數=用戶出現次數

5.找出每個用戶最小金額且最小金額大于20的

說完大概的解題思路，直接上7種解法，所謂的7種解法其實逃不脫上述解題思路，只是函數用的不一樣，我把我能想到的幾種可能寫法都寫了。

每個人可能都有自己首選的寫法，這7種都寫出來是為了輔助一些同學改變自己的思維，在下次遇到同樣的問題時，在面對考官問詢“有沒有其他解法？”，“你覺得最優(yōu)解法是什么？”時，有多個選擇。

解法1：（用時0.001641s）

select id from?test_ordrgroup by idhaving?min(amt)?>?20;

解法2：（用時0.00127s）

select id from (    select id,min(amt) as min_amt ????from?test_ordr?    group by id )as t where?min_amt?>?20;

解法3：（用時0.001s）

select id from (  select id         ,min(amt) over (partition by id) as min_amt ??from?test_ordr)as t where min_amt > 20 group by id;

解法1、2、3其實都是從找到每個用戶最小訂單金額出發(fā)，如果此用戶的最小金額都大于20元，那么此用戶所以訂單金額勢必都大于20元。只是有的用了用于組記錄篩選的having子句，有的用了from子查詢，有的用了窗口函數，殊途同歸吧。

不過需要注意，對于這種題目，如果只要求篩選出一個字段，不要求保留全體數據的話，窗口函數好像就沒什么必要了，免得被面試官說感覺有點復雜，讓你說別的方法，或者在你提及窗口函數后一看，呦，懂窗口函數？來，再出道題好好考考你！

解法4：（用時0.000525s）

select id from?test_ordr?where?id?not?in?(select?id?from?test_ordr?where?amt?<=?20)group?by?id;

解法4的思路是找出有訂單金額不大于20元的用戶，將這些用戶剔除，使用了關鍵字not in，in、not in都是常用的關鍵字，但向來口碑不太好，大多數人都建議盡量避免in和not in的使用，因為效率低且易出錯，可以使用exists、not exists代替，也可以用join代替，此處只是舉例說明，但是不建議用。

解法5：（用時0.001018s）

select id from??test_ordr?group by id having sum(1) = sum(case when amt > 20 then 1 else 0 end);

解法6：（用時0.001614s）

select id from (  select id         ,sum(1) as cnt ????    ,sum(case?when?amt?>?20?then?1?else?0?end)?as?cnt2??from??test_ordr???group?by?id?)as t where cnt = cnt2;

解法7：（用時0.002292s）

select t1.idfrom (  select?id???from?test_ordr?  group?by?id?) as t1left join (  select id ??from?test_ordr?  where amt <= 20  group by id ) as t2 on t1.id = t2.id where t2.id is null

解法5、6、7其實也是一個思路，比較用戶所有的訂單數與用戶所有大于20元的訂單數，如果相同則證明所有訂單都是大于20元的。只是有的用的是having子句，有的用from子查詢，有的用表關聯，此時一些執(zhí)行時間、數據量、效率問題就體現出來了。我本人是非常不建議表關聯的，能不關聯就不關聯。除此之外，在進行條件判斷時，千萬不要忘了好用的case when。

7種解法介紹完了，上述7種都是我隨手寫的，如果有誤，歡迎指正。除此外應該還有一些別的寫法或關鍵字或子句或函數的使用能解決上述問題，思路最重要。

每種解法都附帶了一個用時，此用時是我在mysql 10.0運行得到的，表中共有12條數據，本來我是想借用時來講一下效率的問題，但好笑的是，在這些數據條數情況下，用時最少的竟然是解法4，也就是所謂效率低的not in，但這并不能說明它效率高，應該只是數據量的問題。這里有見解的朋友，歡迎后臺私信我討論。

耗時最久的，不負眾望，left join！所以啊，朋友們，盡量減少滿腦子都是left join的想法吧，多學點不join就能解決問題的方法。