MySQL行转列的问题

背景

开发一个业务的时候遇到一个sql问题，mysql5.6版本。

规则表：

CREATE TABLE `rule` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `score` int(10) NOT NULL DEFAULT '0' COMMENT '规则分数',
  `basescore` int(10) NOT NULL DEFAULT '0' COMMENT '基础分 冗余字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

数据：

比如说现在有四个规则，记录了一个分数，还有一个基础分basescore是一个冗余字段。

积分表：

CREATE TABLE `score` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `otherid` bigint(20) NOT NULL DEFAULT '0' COMMENT '一个外键',
  `score1` int(10) NOT NULL DEFAULT '0' COMMENT '规则1 0-不满足 1-满足',
  `score2` int(10) NOT NULL DEFAULT '0' COMMENT '规则2 0-不满足 1-满足',
  `score3` int(10) NOT NULL DEFAULT '0' COMMENT '规则3 0-不满足 1-满足',
  `score4` int(10) NOT NULL DEFAULT '0' COMMENT '规则4 0-不满足 1-满足',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

数据：

有三个otherid，score1对应rule表里面id为1的规则，score2对应rule表里面id为2的规则。

最后41的分数就是60 + 1 * 10 + 0 * 5 + 1 * 20 + 1 * 15

思考

在写sql的时候有点头疼，首先两个表没有关联的字段，所以连表笛卡尔会有3*4条记录，通过连表的方法，肯定不是最优方法。

一开始想的：

SELECT otherid, score1, score2, score3, score4,
(SELECT basescore FROM rule WHERE id = 1) AS basescore,
(SELECT score FROM rule WHERE id = 1) AS rulescore1,
(SELECT score FROM rule WHERE id = 2) AS rulescore2,
(SELECT score FROM rule WHERE id = 3) AS rulescore3,
(SELECT score FROM rule WHERE id = 4) AS rulescore4
FROM score;

看效率也没问题
但是我想算出总数的时候：

SELECT otherid, score1, score2, score3, score4,
(SELECT basescore FROM rule WHERE id = 1) AS basescore,
(SELECT score FROM rule WHERE id = 1) AS rulescore1,
(SELECT score FROM rule WHERE id = 2) AS rulescore2,
(SELECT score FROM rule WHERE id = 3) AS rulescore3,
(SELECT score FROM rule WHERE id = 4) AS rulescore4,
(basescore + score1*rulescore1 + score2*rulescore2 + score3*rulescore3 + score4*rulescore4) AS total
FROM score;

报错：

1 queries executed, 0 success, 1 errors, 0 warnings

查询：select otherid, score1, score2, score3, score4, (SELECT basescore FROM rule WHERE id = 1) AS basescore, (select score from rule ...

错误代码： 1054
Unknown column 'basescore' in 'field list'

解决方法

首先发现SqlServer和Orcle中可以使用pivot和unpivot，mysql没有 =。=

另辟蹊径实现一波：

SELECT SUM(CASE WHEN id = 1 THEN basescore ELSE 0 END) AS basescore,
SUM(CASE WHEN id = 1 THEN score ELSE 0 END) AS rulescore1,
SUM(CASE WHEN id = 2 THEN score ELSE 0 END) AS rulescore2,
SUM(CASE WHEN id = 3 THEN score ELSE 0 END) AS rulescore3,
SUM(CASE WHEN id = 4 THEN score ELSE 0 END) AS rulescore4
FROM rule;

，这边SUM可以换成AVG、MAX等等

效果：

SELECT otherid, score1, score2, score3, score4, basescore, rulescore1, rulescore2, rulescore3, rulescore4,
(basescore + score1*rulescore1 + score2*rulescore2 + score3*rulescore3 + score4*rulescore4) AS total
FROM score AS a INNER JOIN 
(
	SELECT SUM(CASE WHEN id = 1 THEN basescore ELSE 0 END) AS basescore,
	SUM(CASE WHEN id = 1 THEN score ELSE 0 END) AS rulescore1,
	SUM(CASE WHEN id = 2 THEN score ELSE 0 END) AS rulescore2,
	SUM(CASE WHEN id = 3 THEN score ELSE 0 END) AS rulescore3,
	SUM(CASE WHEN id = 4 THEN score ELSE 0 END) AS rulescore4
	FROM rule
) AS b;

因为规则表很小所以all也没问题，延伸表效率也可以。

延伸

百度一下发现还有个group_concat函数也比较好用，做报表应该可以的，这边也记录下：

SELECT GROUP_CONCAT(score)
FROM rule;

默认逗号分隔