SQL 行值轮转

下面将详细讲解SQL行值轮转的攻略。

什么是SQL行值轮转？

SQL行值轮转（Row Value Transposition），也称为行转列、透视表，是将原始数据行转换为新的数据列的一个过程。通常情况下，我们从数据库中获取的数据是以行的形式呈现的，如果要将这些行数据“横向”展示，可以使用SQL行值轮转。

SQL行值轮转的语法

SQL行值轮转有不同的语法，此处介绍两种常见的语法：

1. 使用MAX()和CASE WHEN

SELECT
  SUM(CASE WHEN `属性列` = '属性值1' THEN `值列` END) AS `新列名1`,
  SUM(CASE WHEN `属性列` = '属性值2' THEN `值列` END) AS `新列名2`,
  ...
FROM `表名`
GROUP BY `某列`

1. 使用PIVOT

SELECT *
FROM `表名`
PIVOT(
  SUM(`值列`) -- 聚合函数，可选择AVG、MAX、MIN等
  FOR `属性列` IN (`属性值1`, `属性值2`, ...)
)

SQL行值轮转的实例

首先，我们创建一个示例表products，包含id、name、version和price四个字段。

CREATE TABLE `products` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `version` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10, 2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

插入一些示例数据：

INSERT INTO `products` (`id`, `name`, `version`, `price`)
VALUES (1, 'iphone', '12', 7999.00),
    (2, 'iphone', '11', 5999.00),
    (3, 'ipad', 'air', 5999.00),
    (4, 'ipad', 'pro', 7999.00);

示例1：使用MAX()和CASE WHEN

要将products表按照name列进行行值转列，将version列的值作为新列名，对应的price列的值作为新列的值。

SELECT
  MAX(CASE WHEN `version`='12' THEN `price` END) AS `iphone12`,
  MAX(CASE WHEN `version`='11' THEN `price` END) AS `iphone11`,
  MAX(CASE WHEN `version`='air' THEN `price` END) AS `ipadAir`,
  MAX(CASE WHEN `version`='pro' THEN `price` END) AS `ipadPro`
FROM `products`
GROUP BY `name`;

解释如下：

1. MAX()函数会返回指定条件下的最大值，此处只是方便聚合，不影响结果。
2. CASE WHEN语句把version列的值作为条件，当条件成立时，返回price列的值，否则返回NULL（如果没有ELSE语句）。
3. AS关键字可以指定新列的列名。
4. GROUP BY语句指定对name列进行聚合。

结果如下：

iphone12	iphone11	ipadAir	ipadPro
7999.00	5999.00	5999.00	7999.00

示例2：使用PIVOT

使用相同的数据和条件进行下一步示例，使用PIVOT语句将行值转列。

SELECT *
FROM (
  SELECT `name`, `version`, `price`
  FROM `products`
) AS `t`
PIVOT (
  MAX(`price`)
  FOR `version` IN (`12`, `11`, `air`, `pro`)
) AS `p`

解释如下：

1. 内部查询语句查询出满足条件的name、version和price列。
2. PIVOT语句指定对version列进行转置，使用MAX()函数对price列进行聚合。
3. IN语句指定了所有可能的属性值，对于实际情况，需要根据数据进行调整。

结果如下：

name	12	11	air	pro
ipad	NULL	NULL	5999.00	7999.00
iphone	7999.00	5999.00	NULL	NULL

通过以上两个实例，我们可以看到SQL行值轮转的两种语法，同时也掌握了如何将行转列，以及如何使用聚合函数和透视表对数据进行汇总。