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