mysql concat的使用

创建数据表并添加数据

CREATE TABLE goods ( 
    id INT PRIMARY KEY auto_increment, 
    goods_name VARCHAR ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '商品名称' 
) ENGINE = INNODB charset = utf8mb4 COMMENT = '商品表';
INSERT INTO `goods` (`id`, `goods_name`) VALUES (NULL, '燕麦'), (NULL, '牛奶');

CREATE TABLE goods_spec (
    id INT PRIMARY KEY auto_increment,
    spec_name VARCHAR ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '规格名称',
    goods_id INT NOT NULL DEFAULT '0' COMMENT '商品id' 
) ENGINE = INNODB charset = utf8mb4 COMMENT = '规格表';
INSERT INTO `goods_spec` (`id`, `spec_name`, `goods_id`) VALUES (NULL, '500g', '1'), (NULL, '350g', '1');
INSERT INTO `goods_spec` (`id`, `spec_name`, `goods_id`) VALUES (NULL, '250ml/16包', '2'), (NULL, '125ml/24包', '2');

 

1.使用concat组合新数据

SELECT
    spec.id,
    goods.goods_name,
    spec.spec_name,
    concat( goods.goods_name, ' ', spec.spec_name ) AS spec_title 
FROM
    goods_spec AS spec
    LEFT JOIN goods ON goods.id = spec.goods_id;

执行结果：

 

  

2.使用concat_ws组合数据，并指定数据间的分隔符号

SELECT
    spec.id,
    goods.goods_name,
    spec.spec_name,
    concat_ws('#', goods.goods_name, spec.spec_name ) AS spec_title 
FROM
    goods_spec AS spec
    LEFT JOIN goods ON goods.id = spec.goods_id;

执行结果：

 

 

3.使用group_concat对数据分组，并显示每个分组的所有规格id数据，默认每个id之间用英文逗号分隔

SELECT
    goods_id,
    group_concat( id ) AS spec_id 
FROM
    goods_spec 
GROUP BY
    goods_id;

执行结果：

 

使用group_concat函数时，可以使用distinct可以去重，使用order by子句可以对数据排序，separator指定数据间分隔符

SELECT
    goods_id,
    group_concat( id ORDER BY id DESC SEPARATOR '#' ) AS spec_id 
FROM
    goods_spec 
GROUP BY
    goods_id;

执行结果：

 

【版权申明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权) https://www.cnblogs.com/facetwitter/p/16325926.html