MySQL基础入门之Case语句用法实例(2)-木庄网络博客

带有 SELECT 查询的 CASE 语句

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

具有聚合函数的 CASE 语句

我们将使用 CASE 语句和 SUM() MySQL 函数按订单状态从 Orders 表中计算总销售额。

创建数据库表

CREATE TABLE Orders (OrderID int,SalesID int, OrderStatus varchar(255) );

插入数据

INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('10', '001', 'Shipped');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('11', '002', 'Shipped');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('12', '003', 'Not Developed');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('13', '004', 'Success');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('14', '005', 'In Process');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('15', '006', 'Cancelled');

SELECT 语句显示表数据

SELECT * FROM Orders;

得到的数据如下：

10   1   Shipped
11   2   Shipped
12   3   Not Developed
13   4   Success
14   5   In Process
15   6   Cancelled

在 SQL SELECT 查询中使用 SUM() 和 COUNT 作为聚合函数的 CASE 语句

SELECT
SUM(CASE
WHEN OrderStatus = 'Success' THEN 1
ELSE 0
END) AS 'Success Count',
SUM(CASE
WHEN OrderStatus = 'On Hold' THEN 1
ELSE 0
END) AS 'Hold Count',
SUM(CASE
WHEN OrderStatus = 'In Process' THEN 1
ELSE 0
END) AS 'Processing',
SUM(CASE
WHEN OrderStatus = 'Shipped' THEN 1
ELSE 0
END) AS 'Shipping count',
SUM(CASE
WHEN OrderStatus = 'Cancelled' THEN 1
ELSE 0
END) AS 'Cancellation Count',
SUM(CASE
WHEN OrderStatus = 'Not Developed' THEN 1
ELSE 0
END) AS 'Not Developed count',
COUNT(*) AS 'Sum Total'
FROM
Orders;

运行结果：

补充：case行转列用法

现有表数据如下图所示（性别列中，1表示男，2表示女）：

需统计各年级男女人数。虽然表中数据已有各年级的男女人数，但是还是不够一目了然。接下来使用case语句查询如下：

SELECT
    class,
    sum( CASE WHEN sex = '1' THEN population ELSE 0 END ) AS cnt_m,
    sum( CASE WHEN sex = '2' THEN population ELSE 0 END ) AS cnt_f 
FROM
    `school_test` 
GROUP BY
    class;

结果如下：

这是最经典的case行转列用法。