本文轉載自公眾號“讀芯術”(ID：AI_Discovery)

 本文將指出一些常見但卻總是被忽略的錯誤，請靜下心來，準備好提升查詢技能吧!

[[377879]]

讓我們以一個虛構的業務為例。假設你是亞馬遜電子商務分析團隊的一員，需要運行幾個簡單的查詢。你手頭有兩個表，分別為“product(產品)”和“discount (折扣)”。

 

 

1.計算NULL字段的數目

為了計算null字段的數目，要掌握COUNT函數的工作原理。假設計算產品數量時，要求計入表格“product”的“product id”主鍵列中遺漏的字段。

 

SELECT COUNT(product_id) 
FROM product;Result: 3 

 

由于要求計入“product id”列中的null值，查詢結果應該為4，但COUNT()函數不會將null值計數。

解決方法：使用COUNT(*)函數。該函數可以將null值計數。

 

Select Count(*) 
From product;Result: 4 

 

這個操作很簡單，但是在編寫復雜的查詢時總會被忽略。

2.使用保留字作為列名

 

SELECT product_id, 
RANK() OVER (ORDER BY price desc) As Rank 
FROM product; 

 

由于列名“Rank”是Rank函數的保留字，該查詢結果出錯。

解決方法：

 

SELECT product_id, 
RANK() OVER (ORDER BY price desc) As ‘Rank’ 
FROM product; 

 

加上單引號，即可得到想要的結果。

3.NULL的比較運算

 

SELECT product_name 
FROM product 
WHERE product_id=NULL; 

 

由于使用了比較運算符“=”，此處運算會出現異常，使用另一比較運算符“!=”運算也會出現異常。這里的邏輯問題在于，你編寫的查詢得出的是“product id”列的值是否未知，而無法得出這一列的值是否是未知的產品。

解決方法：

 

SELECT product_name 
FROM product 
WHERE product_id ISNULL; 

 

4.ON子句過濾和WHERE子句過濾的區別

這是一個非常有趣的概念，會提高你對于ON子句過濾和WHERE子句過濾之間區別的基本理解。這并不完全是一個錯誤，只是演示了兩者的用法，你可以根據業務需求選擇最佳方案。

 

SELECT d.product_id, 
price, 
discount 
FROM product p RIGHT JOIN discount d ON 
p.product_id=d.product_id 
WHERE p.product_id>1; 

 

結果：

 

 

在這種情況下，過濾條件在兩個表格連接之后生效。因此，所得結果不包含d.product_id≤1的行(同理，顯然也不包含p.product≤1的行)。

解決方法：使用AND，注意結果上的不同。

 

SELECT d.product_id, 
price, 
discount 
FROM product p RIGHT JOIN discount d ON 
p.product_id=d.product_id 
AND p.product_id>1; 

 

結果：

 

 

在這里，條件語句AND在兩個表格連接發生之前計算?？梢园汛瞬樵兛醋髦贿m用于一個表(“product”表)的WHERE子句?，F在，由于右連接，結果中出現了d.product_id≤1的行(顯然還有p.product_id>1的行)。

請注意，ON子句過濾和WHERE子句過濾只在左/右/外連接時不同，而在內連接時相同。

5.在同一查詢的WHERE子句中使用Windows函數生成的列&使用CASE WHEN子句

注意，不要在同一查詢的WHERE子句中使用通過Windows函數生成的列名以及CASE WHEN子句。

 

SELECT product_id, 
RANK() OVER (ORDER BY price desc) AS rk 
FROM product 
WHERE rk=2; 

 

因為列rk由Windows函數生成，并且在同一查詢的WHERE子句中使用了該列，這個查詢結果會出現異常。

解決方法：這一問題可以通過使用臨時表或者子查詢解決。

 

WITH CTE AS 
( 
SELECT product_id, 
 
 
 
 
RANK() OVER (ORDER BY price desc) AS rk 
FROM product 
) 
SELECT product_id 
FROM 
CTE 
WHERE rk=2; 

 

或

 

SELECT product_id 
FROM 
( 
SELECT product_id, 
RANK() OVER (ORDER BY price desc) AS rk 
FROM product; 
) 
WHERE rk=2; 

 

同樣的方法也適用于使用CASE WHEN子句創建的列。請記住，Windows函數只能出現在SELECT或ORDER BY子句中。

6.BETWEEN的使用不正確

如果不清楚BETWEEN的有效范圍，也許會得不到想要的查詢結果。BETWEEN x AND y語句的有效范圍包含x和y。

 

SELECT * 
FROM discount 
WHERE offer_valid_till BETWEEN ‘2019/01/01’ AND ‘2020/01/01’ 
ORDER BY offer_valid_till; 

 

結果：

 

 

在查詢中，也許我們只想得到2019年的所有日期，但是結果中還包含了2020年1月1日。這是因為BETWEEN語句的有效范圍包含2019/01/01和2020/01/01。

解決方法：相應地調整范圍可以解決這個問題。

 

SELECT * 
FROM discount 
WHERE offer_valid_till BETWEEN ‘2019/01/01’ AND ‘2019/12/31’ 
ORDER BY offer_valid_till; 

 

結果：

 

 

現在，所有查詢結果均為2019年的日期。

7.在GROUP BY語句后使用WHERE子句

在編寫GROUP BY語句時，請注意WHERE子句的位置。

 

SELECT category, 
AVG (price) 
FROM product p INNER JOIN discount d ON 
p.product_id=d.product_id 
GROUP BY category 
WHERE discount_amount>10; 

 

由于將WHERE子句放在GROUP BY語句后，此查詢是錯誤的。這是為什么呢?

WHERE子句用于過濾查詢結果，這一步要在將查詢結果分組之前實現，而不是先分組再過濾。正確的做法是先應用WHERE條件過濾減少數據，再使用GROUP BY子句通過聚合函數將數據分組(此處使用聚合函數AVG)。

解決方法：

 

SELECT category, 
AVG (price) 
FROM product p INNER JOIN discount d ON 
p.product_id=d.product_id 
WHERE discount_amount>10 
GROUP BY category; 

 

請注意主要SQL語句的執行順序：

• FROM 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• SELECT 子句
• ORDER BY 子句

以上包含了大部分讓人不解的錯誤，尤其是對初學者而言。正如亨利·福特所說：“唯一的錯誤是我們從中學不到任何東西”，希望這篇文章能幫助你精進查詢技能。