我们会遇到这样的需求:

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

例如有一张表叫reward_detail

group_idmember_countnamestotal_reward
14Tom,Jack,HanMeimei,LiLei200
24Jack,Rose,Mary,张三400
32张三,李四300

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

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

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

  • 聚合函数,可以将多行结果变为一行,如SUMCOUNT(DISTINCT())
  • 非聚合函数,一行结果还是一行,如CONCATREPLACESUBSTRING_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表输出为:

group_idnamesingle_rewardi
1Tom501
1Jack502
1HanMeimei503
1LiLei504
2Jack1001
2Rose1002
2Mary1003
2张三1004
3张三1501
3李四1502

标签: SQL

添加新评论

captcha
请输入验证码