SQL将一行转为多行

我们会遇到这样的需求：

某一张表的某一列，存储的是用某个分隔符分割的字符串，我们现在要分割字符串的每个部分，求每个部分的汇总数据。

例如有一张表叫reward_detail：

group_id	member_count	names	total_reward
1	4	Tom,Jack,HanMeimei,LiLei	200
2	4	Jack,Rose,Mary,张三	400
3	2	张三,李四	300

这是一张设计得不那么好的表，有若干组，一个人可以同时在多个组，每组有奖金，组内成员平分奖金，成员存在一列，使用逗号分开。

我们已知每组不超过10个人，怎么仅使用SQL，求出每个人获得的奖金金额呢？

这里首先想到，使用字符串分割函数（对于MySQL来说，是SUBSTRING_INDEX），取出组内的每个成员。但是SQL的函数只有两种：

• 聚合函数，可以将多行结果变为一行，如SUM、COUNT(DISTINCT())等
• 非聚合函数，一行结果还是一行，如CONCAT、REPLACE、SUBSTRING_INDEX等

我们使用这样的查询：

SELECT SUBSTRING_INDEX(`names`, ',', 1)
FROM `reward_detail`

只能取出每组的第一个人，没办法取出每组的每个人，用于接下来的汇总。

所以要怎么做，才能把每组的每个人都取出来，并且放到每一行呢？

我们知道JOIN语句是用于联表的，比如LEFT JOIN，就是以左表为准，将右表符合条件的行拼接到左表对应的行；

CROSS JOIN则是求两表的笛卡尔积。

一看到笛卡尔积，我们就很容易想到它的结果就是两表行数相乘。

看到相乘，咦，这不就可以用来一行转多行嘛。

SELECT t1.a, t2.b FROM t1, t2 WHERE ...
或者写作
SELECT t1.a, t2.b FROM t1 CROSS JOIN t2 WHERE ...

那么对于上面的问题，可以怎么做呢？

我们先创建一个临时表（当然也可以直接写进数据库里面）：

(
SELECT 1 as i 
UNION SELECT 2 as i 
UNION SELECT 3 as i
...
UNION SELECT 10 as i
) tmp

然后，将左表与这个临时表CROSS JOIN，使用临时表的i作为SUBSTRING_INDEX的第三个参数，然后再加上i <= member_count的筛选，就大功告成啦。

SELECT `name`, SUM(`single_reward`) AS `single_reward`
FROM (
    SELECT `group_id`,
           SUBSTRING_INDEX(`names`, ',', `i`) AS `name`,
           `total_reward` / `member_count` AS `single_reward`,
           `i`
    FROM `reward_detail`
    CROSS JOIN (
        SELECT 1 as `i `
        UNION SELECT 2 as `i`
        UNION SELECT 3 as `i`
        ...
        UNION SELECT 10 as `i`
    ) `tmp`
    WHERE `tmp`.`i` <= `reward_detail`.`member_count`
) `single_reward_detail`
GROUP BY `name`

上面的查询，single_reward_detail表输出为：