對于數據科學家來講，SQL(結構化查詢語言)是其工具箱中比較重要的工具之一。掌握SQL不僅有助于你在面試中脫穎而出，而且通過解決復雜查詢達到對SQL的充分理解，還能在讓你許多競爭中保持領先地位。

[[344803]]

本文就將介紹5個有關SQL的棘手問題和其解決方法。注意，每個查詢都能以不同方式編寫。在參考本文解決方案之前，你可以先試著自己思考一下。

查詢1

下列表格由名字和職業兩列組成。需要查詢所有姓名，且使其后緊跟一個括號，括住“職業”列中對應的首字母。

• 本文解決方案

SELECT 
CONCAT(Name, ’(‘, SUBSTR(Profession, 1, 1), ’)’) 
FROM table; 

由于需要把名字和職業結合起來，可以使用CONCAT。而且因為括號內只需要一個字母，可以使用SUBSTR來傳遞列名、開始索引和結束索引。因為只需要首字母，所以我們將傳遞1，1(開始索引包括在內，結束索引不包括在內)。

查詢2

蒂娜需要從她創建的EMPLOYEES表中計算所有員工的平均工資，但結果顯示的平均值很低，這可能是鍵盤上的回零鍵失效了。她希望我們幫助找出錯誤計算的平均值和實際平均值之間的差異。我們須編寫一個查找錯誤的查詢(實際平均值-計算平均值)。

• 本文解決方案

SELECT 
AVG(Salary) - AVG(REPLACE(Salary, 0, ’’)) 
FROM table; 

需要注意，只有一個表包含了實際工資值。為了創建錯誤場景，使用REPLACE替換0。接著傳遞列名、替換值以及用于替換REPLACE方法的值。然后，使用聚集函數AVG來求平均值的差。

查詢3

給定一個表，它是由節點和父節點兩列組成的二元搜索樹。需要編寫一個查詢，以返回按節點值進行升序排序的節點類型。有3種類型：

• 根(Root)——如果節點是根
• 葉(Leaf)——如果節點是葉
• 內部(Inner)——如果節點既不是根也不是葉

• 本文解決方案

經過初步分析，可以得出結論：如果給定節點N的相應P值為NULL(空)，則它是根。而如果給定節點N存在于P列中，則它不是內部節點。基于此想法編寫一個查詢。

SELECT CASE 
    WHEN P IS NULL THENCONCAT(N, ' Root') 
    WHEN N IN (SELECTDISTINCT P from BST) THEN CONCAT(N, ' Inner') 
    ELSE CONCAT(N, ' Leaf') 
    ENDFROM BSTORDER BY N asc; 

可使用CASE作為開關函數。正如前文提到的，如果對于給定節點N，P為空值，則N是根。因此，我們使用CONCAT來組合節點值和標簽。

類似地，如果給定節點N存在于P列中，則它是內部節點。為了獲得P列中的所有節點，我們編寫了一個返回P列中所有不同節點的子查詢。由于要求按節點值升序對輸出進行排序，因此要使用ORDER BY子句。

查詢4

該事務表由transaction_id, user_id, transaction_date,product_id, and quantity(交易ID，用戶ID，交易日期，產品ID和數量)組成。需要查詢多天來購買產品的用戶數量(注意，給定用戶可以在一天內購買多個產品)。

• 本文解決方案

為了解決該查詢，不能直接計算user_id的出現次數，由于給定用戶在一天中可以多次購買，user_id或許會有多次返回。因此，只有當存在多個不同日期與給定的user_id相關聯時，才意味著該用戶多天購買了產品。按照相同方法，進行查詢編寫。(內部查詢)

SELECT COUNT(user_id) 
FROM 
(SELECT user_id 
 FROM orders 
 GROUP BY user_id 
 HAVING COUNT(DISTINCT DATE(date))> 1 
) t1 

由于問題詢問的是user_id的數量，而不是user_id本身，因此在外部查詢中使用 COUNT 。

查詢5

給定一個訂閱表，其中包含每個用戶訂閱的開始和結束日期。需要編寫一個查詢，根據與其他用戶的日期重疊情況，為每個用戶返回true/false。例如，如果user1的訂閱周期與其他任何用戶重疊，則查詢必須為user1返回true。

• 本文解決方案

經過初步分析，我們可以知道必須將每項訂閱與其他訂閱進行比較。將userA的開始和結束日期視為startA 和endA，類似地，userB也依此設為startB和endB。如果startA≤endB且endA≥startB，則可以說這兩個日期范圍重疊。我們來舉兩個例子，先比較一下U1和U3：

startA = 2020–01–01 
endA = 2020–01–31 
startB = 2020–01–16 
endB = 2020–01–26 

這里可以看出，startA(2020–01–01)小于endB(2020–01–26)，那么同樣，endA(2020–01–31)大于 startB(2020–01–16)，因此可以得出結論，日期重疊。類似地，如果比較U1和U4，上述條件就不成立，于是返回FALSE。

這里還必須確保不會將用戶與其自己的訂閱進行比較。同時希望運行一個左連接，能夠自行將用戶與滿足條件的其他用戶進行匹配。現在，我們將創建同一表的兩個副本S1和S2。

SELECT * 
FROM subscriptions AS s1 
LEFT JOIN subscriptions AS s2 
    ON s1.user_id != s2.user_id 
        AND s1.start_date <=s2.end_date 
        AND s1.end_date >=s2.start_date 

給定條件連接，在日期之間存在重疊的情況下，對于S1中的每個user_id，應該存在來自S2的user_id。

• 輸出

可以看到，以防日期重疊，每個用戶都有一個對應用戶。對于user1，有2行顯示其與2個用戶相匹配。對于用戶4，對應的ID為空，表示他與其他任何用戶都不匹配。現在，將其全部組合在一起，按照s1.user_ID字段進行分組，并檢查s2.user_ID不為空的用戶的值是否為真。

• 最終查詢

SELECT 
    s1.user_id    , (CASE WHEN s2.user_idIS NOT NULL THEN 1 ELSE 0 END) AS overlap 
FROM subscriptions AS s1 
LEFT JOIN subscriptions AS s2 
    ON s1.user_id != s2.user_id 
        AND s1.start_date <=s2.end_date 
        AND s1.end_date >=s2.start_date 
GROUP BY s1.user_id 

使用 CASE子句根據給定用戶的s2.user_id值來標記1和0。最終輸出如下：