MySQL 中的案例：11 個實例

資訊科技數位 » 數據庫 » MySQL 中的案例：11 個實例

MySQL 中的 CASE 函數是一個強大的工具，可讓您在查詢中執行條件操作。使用 CASE，您可以評估不同的條件，並根據這些條件是否滿足傳回特定結果。我們將透過實際範例解釋此功能，幫助您掌握 MySQL 中 CASE 的使用並提高資料庫管理的技能。

MySQL 中的 CASE 函式是什麼？

MySQL 中的CASE 函數是一個條件表達式，它允許您評估不同的條件，並根據這些條件是否滿足傳回特定的結果。它是一個非常有用的工具，可以在查詢中執行邏輯運算，並根據特定條件獲得自訂結果。

CASE 的工作方式類似於一系列 IF-THEN-ELSE 語句，您可以在其中指定多個條件以及滿​​足這些條件時傳回的值。如果沒有滿足任何條件，則可以使用 ELSE 子句定義預設值。

MySQL 中的基本 CASE 語法

MySQL中CASE函數的基本語法如下：

CASE
    WHEN condición1 THEN resultado1
    WHEN condición2 THEN resultado2
    ...
    WHEN condiciónN THEN resultadoN
    ELSE resultado_predeterminado
END

以下是語法各部分的解釋：

• WHEN：指定要評估的條件。
• THEN：表示當對應條件滿足時，傳回的結果。
• ELSE：（可選）指定當上述條件均不滿足時傳回的結果。
• END：標記 CASE 表達式的結束。

現在您已經了解了基本語法，讓我們來探索一些實際的例子！

範例 1：根據平均成績對學生進行分類

假設您有一個名為「學生」的表，其中包含以下列：「id」、「name」和「avg」。您想要使用 CASE 函數根據學生的 GPA 對學生進行排名。你可以這樣做：

SELECT nombre,
       CASE
           WHEN promedio >= 90 THEN 'Sobresaliente'
           WHEN promedio >= 80 THEN 'Notable'
           WHEN promedio >= 70 THEN 'Bien'
           WHEN promedio >= 60 THEN 'Suficiente'
           ELSE 'Insuficiente'
       END AS clasificacion
FROM estudiantes;

在這個例子中，我們使用 CASE 來評估每個學生的平均成績並分配相應的排名。若平均分數大於或等於 90，則評為「優秀」。如果分數在 80 到 89 之間，則歸類為“卓越”，依此類推。如果平均值低於 60，則歸類為「不足」。

範例 2：分配產品類別

假設您有一個名為「產品」的表，其中包含「id」、「名稱」和「價格」欄位。您想要使用 CASE 函數根據產品價格為每個產品指派一個類別。你可以這樣做：

SELECT nombre,
       CASE
           WHEN precio > 1000 THEN 'Premium'
           WHEN precio > 500 THEN 'Gama alta'
           WHEN precio > 100 THEN 'Gama media'
           ELSE 'Económico'
       END AS categoria
FROM productos;

在這個例子中，我們使用 CASE 來評估每種產品的價格並分配相應的類別。如果價格高於 1000，則歸類為「高級」。如果在 500 到 1000 之間，則歸類為“高端”，依此類推。如果價格小於或等於 100，則歸類為「經濟」。

範例 3：根據購買數量計算折扣

假設您有一個名為「sales」的表，其中包含「id」、「product」和「quantity」欄位。您要使用 CASE 函數根據購買數量計算每次銷售的折扣。你可以這樣做：

SELECT producto,
       CASE
           WHEN cantidad >= 100 THEN 0.20
           WHEN cantidad >= 50 THEN 0.15
           WHEN cantidad >= 20 THEN 0.10
           ELSE 0
       END AS descuento
FROM ventas;

在這個例子中，我們利用CASE來評估每個產品的購買數量，並計算相應的折扣。如果數量大於或等於 100，則享有 20% 的折扣。如果在 50 到 99 之間，則適用 15% 的折扣，依此類推。如果數量少於 20，則不享有折扣。

範例4：將數值轉換為範圍

假設您有一個名為「員工」的表，其中包含「id」、「姓名」和「年齡」欄位。您想要使用 CASE 函數將員工年齡轉換為範圍。你可以這樣做：

SELECT nombre,
       CASE
           WHEN edad >= 60 THEN 'Senior'
           WHEN edad >= 40 THEN 'Mediana edad'
           WHEN edad >= 20 THEN 'Joven'
           ELSE 'Menor de edad'
       END AS rango_edad
FROM empleados;

在這個例子中，我們使用 CASE 來評估每位員工的年齡並分配相應的範圍。如果年齡大於或等於60歲，則歸類為「高級」。如果您的年齡在 40 至 59 歲之間，則您被歸類為“中年”，依此類推。如果年齡小於20歲，則歸類為「未成年人」。

範例 5：根據多個條件分配標籤

假設您有一個名為「orders」的表，其中包含「id」、「customer」、「total」和「status」欄位。您想要使用具有多個條件的 CASE 函數根據訂單總額和狀態為每個訂單指派標籤。你可以這樣做：

SELECT cliente,
       CASE
           WHEN total > 1000 AND estado = 'Entregado' THEN 'VIP'
           WHEN total > 500 AND estado = 'Entregado' THEN 'Prioritario'
           WHEN estado = 'Pendiente' THEN 'En proceso'
           ELSE 'Regular'
       END AS etiqueta
FROM pedidos;

在這個範例中，我們使用具有多個條件的 CASE 來評估每個訂單的總額和狀態並分配適當的標籤。如果總數大於 1000 且狀態為“已交付”，則標記為“VIP”。如果總數大於 500 且狀態為“已交付”，則標記為“優先”。如果狀態為“待定”，則標記為“處理中”。否則，它將被標記為“常規”。

範例6：使用CASE處理空值

假設您有一個名為「客戶」的表，其中包含「id」、「name」和「email」欄位。有些客戶可能沒有記錄電子郵件，這將導致「電子郵件」列中的值為空。可以使用CASE函數對這些空值進行適當的處理。例如：

SELECT nombre,
       CASE
           WHEN email IS NULL THEN 'Sin correo electrónico'
           ELSE email
       END AS informacion_contacto
FROM clientes;

在這個範例中，我們使用 CASE 來評估「email」欄位是否為空。如果為空，則顯示文字「沒有電子郵件」。否則，將顯示「電子郵件」列的實際值。這使我們能夠妥善處理缺少聯絡資訊的情況。

範例 7：將 CASE 與聚合函數結合

CASE 函數也可以與 SUM、AVG、COUNT 等聚合函數結合使用。假設您有一個名為「sales」的表，其中包含「id」、「product」、「quantity」和「price」欄位。您想要使用 CASE 和 SUM 計算按產品類別劃分的總銷售額。你可以這樣做：

SELECT
    SUM(CASE WHEN precio > 1000 THEN cantidad ELSE 0 END) AS ventas_premium,
    SUM(CASE WHEN precio <= 1000 THEN cantidad ELSE 0 END) AS ventas_regulares
FROM ventas;

在此範例中，我們在 SUM 函數中使用 CASE 來計算按產品類別劃分的總銷售額。如果價格高於 1000，則金額將會加到「premium_sales」。如果價格小於或等於 1000，則金額將會加到「regular_sales」。這使我們能夠根據特定條件獲得小計。

範例 8：在 WHERE 子句中使用 CASE

CASE 函數也可以用於 WHERE 子句中，根據特定條件篩選記錄。假設您有一個名為「employees」的表，其中包含「id」、「name」、「department」和「salary」欄位。您想要瞄準那些薪水高於其部門平均的員工。你可以這樣做：

SELECT nombre, departamento, salario
FROM empleados
WHERE salario > (
    SELECT AVG(CASE WHEN e.departamento = empleados.departamento THEN e.salario ELSE NULL END)
    FROM empleados e
);

在此範例中，我們在子查詢中使用 CASE 來計算按部門劃分的平均工資。子查詢將每個員工所在的部門與當前部門進行比較，並且只考慮同一部門的員工的薪資來計算平均值。然後，在主查詢中，我們篩選出薪資高於其部門平均薪資的員工。

範例 9：使用 CASE 產生計算列

CASE函數也可用於根據特定條件產生計算列。假設您有一個名為「orders」的表，其中包含「id」、「customer」、「total」和「date」欄位。您想要建立一個名為「折扣」的附加列，根據訂單總額套用不同的折扣百分比。你可以這樣做：

SELECT id, cliente, total,
       CASE
           WHEN total > 1000 THEN total * 0.10
           WHEN total > 500 THEN total * 0.05
           ELSE 0
       END AS descuento,
       fecha
FROM pedidos;

在這個例子中，我們使用 CASE 來產生計算的「折扣」列。如果訂單總額超過 1000，則享有 10% 折扣。如果總數超過 500，則享有 5% 折扣。在任何其他情況下，不適用折扣。此計算列可用於進一步分析或在查詢結果中顯示附加資訊。

範例 10：使用巢狀 CASE 語句實現複雜邏輯

在某些情況下，您可能需要使用巢狀的 CASE 語句來實作更複雜的條件邏輯。假設您有一個名為「students」的表，其中包含「id」、「name」、「math_grade」和「language_grade」欄位。您想根據每個學生的數學和語言成績為他們分配一個類別。你可以這樣做：

SELECT nombre,
       CASE
           WHEN nota_matematicas >= 90 AND nota_lenguaje >= 90 THEN 'Excelente'
           WHEN nota_matematicas >= 80 AND nota_lenguaje >= 80 THEN 'Notable'
           ELSE
               CASE
                   WHEN nota_matematicas >= 70 OR nota_lenguaje >= 70 THEN 'Regular'
                   ELSE 'Necesita mejorar'
               END
       END AS categoria
FROM estudiantes;

在這個範例中，我們使用巢狀的 CASE 語句來實作更複雜的條件邏輯。首先，我們評估數學和語言成績是否都大於或等於 90。然後，我們評估兩個成績是否大於或等於 80。如果上述條件皆不滿足，我們將進入下一層巢狀 CASE。在這裡，我們評估至少有一個成績（數學或語言）是否大於或等於 70。如果任何條件均不滿足，則分配「需要改進」類別。

範例 11：使用 CASE 最佳化查詢

CASE 函數也可用於最佳化查詢並避免多個單獨的查詢。假設您有一個名為「sales」的表，其中包含「id」、「product」、「quantity」和「date」欄位。您想透過一次查詢獲得每月的總銷售額和每年的總銷售額。你可以這樣做：

SELECT
    SUM(CASE WHEN MONTH(fecha) = 1 THEN cantidad ELSE 0 END) AS ventas_enero,
    SUM(CASE WHEN MONTH(fecha) = 2 THEN cantidad ELSE 0 END) AS ventas_febrero,
    -- ... (continúa para los demás meses)
    SUM(CASE WHEN YEAR(fecha) = 2022 THEN cantidad ELSE 0 END) AS ventas_2022,
    SUM(CASE WHEN YEAR(fecha) = 2023 THEN cantidad ELSE 0 END) AS ventas_2023
FROM ventas;

在此範例中，我們在 SUM 函數中使用 CASE 在單一查詢中按月和按年計算銷售總額。對於每個月，我們評估銷售日期的月份是否與特定月份匹配，並添加相應的金額。同樣，對於每一年，我們評估銷售日期的年份是否與具體年份相符，並添加相應的金額。這使得我們能夠透過一次有效的查詢來獲得所有總數。

在 MySQL 中使用 CASE 的最佳實踐

• 僅在必要時使用 CASE，並避免過度使用，因為過度使用會影響查詢效能。
• 盡量使 CASE 表達式盡可能簡單且易讀。如果邏輯過於複雜，請考慮將其分解為多個 CASE 表達式或使用子查詢。
• 將 CASE 與其他 MySQL 子句和函數結合使用，以充分利用其潛力，例如在 WHERE、ORDER BY、 通過...分組 和聚合函數。
• 嵌套多個 CASE 表達式時要小心，因為它會使您的程式碼難以閱讀和維護。如果有必要，請添加解釋性評論。

使用 CASE 時的常見錯誤及其避免方法

• 忘記 ELSE 子句： 請務必包含 ELSE 子句來處理不滿足任何條件的情況。若未指定，則預設為 NULL。
• 不要用 END 終止 CASE 表達式： 請記得始終以 END 關鍵字結束 CASE 表達式。否則您將收到語法錯誤。
• 使用不相容的資料類型： 確保每個 WHEN 條件回傳的結果屬於相同的資料類型。如果混合使用資料類型，可能會得到意外的結果或錯誤。
• 不考慮條件順序：CASE 中的條件會依照它們出現的順序進行評估。確保將更具體的條件放在更一般的條件之前，以獲得所需的結果。

MySQL 中 CASE 的替代方案

儘管 CASE 是一個強大的函數，但在某些情況下您可能需要考慮一些替代方案：

• IF 表達式：該 MySQL 中的 IF 函數 允許您評估一個條件，如果條件為真則傳回一個值，如果條件為假則傳回另一個值。對於特殊情況來說，這是一個更簡單的替代方法。
• 查找表：在某些情況下，您可以使用單獨的查找表來儲存條件和相應的結果。然後，您可以將這些表與主表連接起來以獲得所需的結果。
• 儲存的視圖或函數：如果您有重複使用 CASE 的複雜查詢，您可以考慮建立視圖或 儲存函數 封裝該邏輯並簡化後續查詢。

有關 MySQL 中 CASE 的常見問題

1.我可以將 CASE 與其他 MySQL 函式結合使用嗎？

是的，您可以將 CASE 與其他 MySQL 函數結合使用，例如聚合函數（SUM、AVG、COUNT 等）、日期和時間函數（YEAR、MONTH、DAY 等）、字串函數（CONCAT、SUBSTRING、LENGTH 等）等等。

2. 在 CASE 表達式中可以使用的 WHEN 條件數量是否有限制？

在 CASE 表達式中可以使用的 WHEN 條件數量沒有具體限制。但是，請記住，大量的條件會影響程式碼的可讀性和查詢效能。如果您有許多條件，請考慮簡化邏輯或將其分解為多個 CASE 表達式。

3. 我可以在 CASE 表達式中使用子查詢嗎？

是的，您可以在 CASE 運算式中使用子查詢，無論是在 WHEN 條件中還是在 THEN 結果中。這使得您可以根據其他查詢的結果執行更複雜的計算或比較。

4. 如何處理 CASE 表達式中的空值？

您可以使用 IS NULL 或 IS NOT NULL 條件來處理 CASE 運算式中的空值。例如，可以使用 CASE WHEN column IS NULL THEN 'Null value' ELSE column END 在列為空時分配特定值，在列不為空時傳回實際值。

Mysql 中的案例結論

MySQL 中的 CASE 函數是一個強大且多功能的工具，可讓您在查詢中執行條件操作。使用 CASE，您可以評估不同的條件，並根據這些條件是否滿足傳回特定結果。本文中提供的範例為您提供了堅實的基礎，讓您可以開始在自己的查詢中使用 CASE 並使其適應您的特定需求。

使用 CASE 語句時，請務必遵循最佳實踐，例如保持表達式簡潔易讀，將 CASE 與其他MySQL 子句和函數結合使用，並在適當的時候考慮其他替代方案。透過實作和實驗，您將能夠充分利用 MySQL 中的 CASE 語句，並提高查詢的效率和可讀性。

如果您有任何其他問題或需要更多範例，請隨時搜尋其他資源或查閱官方 MySQL 文件。繼續在您的資料庫專案中探索和利用 CASE 的強大功能！

Recursos adicionales：

• 有關 CASE 函數的官方 MySQL 文檔
• 在 MySQL 中使用 CASE 的其他範例