SQL 竖排转成横排 并根据属性分类,合并 mysql的group_concat用法 pgsql的string_agg用法

工作中遇到一个问题: 产品有许多属性, 这些属性并不保存在产品的基本信息表中, 而是保存在属性表中, 需要根据其中的一些属性做分页查询. 通过查资料, 找到解决方案, 例子如下:

MySQL语句

DROP TABLE IF EXISTS `attribute`;
CREATE TABLE `attribute` (
  `id` int(11) DEFAULT NULL,
  `attr_code` varchar(255) DEFAULT NULL,
  `attr_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of attribute
-- ----------------------------
INSERT INTO `attribute` VALUES ('1', 'productname', '小米');
INSERT INTO `attribute` VALUES ('1', 'productcode', 'xm123456');
INSERT INTO `attribute` VALUES ('1', 'scale', '100万吨');
INSERT INTO `attribute` VALUES ('2', 'productname', '汽车');
INSERT INTO `attribute` VALUES ('2', 'productcode', 'qc632521');
INSERT INTO `attribute` VALUES ('2', 'scale', '100万辆');
INSERT INTO `attribute` VALUES ('3', 'productname', '电脑');
INSERT INTO `attribute` VALUES ('3', 'productcode', 'dn852963');
INSERT INTO `attribute` VALUES ('3', 'scale', '100万台');
INSERT INTO `attribute` VALUES ('4', 'productname', '手机');
INSERT INTO `attribute` VALUES ('4', 'productcode', 'sj8524632');
INSERT INTO `attribute` VALUES ('4', 'scale', '100万部');
INSERT INTO `attribute` VALUES ('1', 'productname', '大米');
INSERT INTO `attribute` VALUES ('1', 'productname', '高粱');

以下为数据插入的结果

需求及解决方法

按照id分组, 相同属性,即attr_code相同的attr_name合并,以”,”隔开.

SELECT
    id,
    group_concat(
        (
            CASE attr_code
            WHEN 'productname' THEN
                attr_name
            ELSE
                NULL
            END
        )
    ) productname,
    max(
        CASE attr_code
        WHEN 'productcode' THEN
            attr_name
        ELSE
            NULL
        END
    ) productcode,
    max(
        CASE attr_code
        WHEN 'scale' THEN
            attr_name
        ELSE
            NULL
        END
    ) scale
FROM
    attribute
GROUP BY
    id;

结果如下,注意红框所示.

下面是pgsql的解决方法, pgsql没有group_concat, 可以用string_agg解决.

SELECT
    id,
    string_agg (
        (
            CASE attr_code
            WHEN 'productname' THEN
                attr_name
            ELSE
                NULL
            END
        ),
        ","
    ) productname,
    max(
        CASE attr_code
        WHEN 'productcode' THEN
            attr_name
        ELSE
            NULL
        END
    ) productcode,
    max(
        CASE attr_code
        WHEN 'scale' THEN
            attr_name
        ELSE
            NULL
        END
    ) scale
FROM
    attribute
GROUP BY
    id;

参考资料:
https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent