问题分析

最近遇到一个问题：

year	amount	num
1991	1	1.1
1991	2	1.2
1991	3	1.3
1992	1	2.1
1992	2	2.2
1992	3	2.3

把上面表格的数据查询成：

year	m1	m2	m3
1991	1.1	1.2	1.3
1992	2.1	2.2	2.3

看到这样的需求，首先想到的是用 case 去统计以及用 group by 来分组

第一版 sql 代码：

SELECT
	`year`,
	(CASE WHEN amount = 1 THEN num END) AS m1,
	(CASE WHEN amount = 2 THEN num END) AS m2,
	(CASE WHEN amount = 3 THEN num END) AS m3
FROM
	test
GROUP BY
	`year`

查询出来的结果有点不如人意：

year	m1	m2	m3
1991	1.1
1992	2.1

这么说明了分组之后只显示到第一行数据，那么我们去掉分组看看：

SELECT
    `year`,
    (CASE WHEN amount = 1 THEN num END) AS n1,
    (CASE WHEN amount = 2 THEN num END) AS n2,
    (CASE WHEN amount = 3 THEN num END) AS n3
FROM
    test

得到的结果是：

year	m1	m2	m3
1991	1.1	0	0
1991	0	1.2	0
1991	0	0	1.3
1992	2.1	0	0
1992	0	2.2	0
1992	0	0	2.3

有点像我们想要的了，只是没有分组以及去掉空值

而且我们可以看出，在分组的情况下 m1, m2, m3 的值都是一个最大值来的

所以我们可以用一个子查询来查询上面的结果集中分组的最大值最终版 sql：

SELECT
	`year`,
	MAX(n1) AS m1,
	MAX(n2) AS m2,
	MAX(n3) AS m3
FROM
(
    SELECT
        `year`,
        (CASE WHEN amount = 1 THEN num END) AS n1,
        (CASE WHEN amount = 2 THEN num END) AS n2,
        (CASE WHEN amount = 3 THEN num END) AS n3
    FROM
        test
) AS a
GROUP BY
	`year`

最终可以得到我们想要的结果：

year	m1	m2	m3
1991	1.1	1.2	1.3
1992	2.1	2.2	2.3

case when 语法

第一种用法：

CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

第二种用法：

CASE
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

两种用法的区别：

第一种CASE语法返回的是第一个value=compare_value为true的分支的结果。

第二种CASE语法返回的是第一个condition为true的分支的结果。

case when的使用场景

根据条件转换字段含义

SELECT
    name '姓名',
    age '年龄',
    CASE
        WHEN age < 18 THEN '少年'
        WHEN age < 30 THEN '青年'
        WHEN age >= 30 AND age < 50 THEN '中年'
        ELSE '老年'
    END '年龄段'
FROM
    user_info;