mysql之group_concat函数详解

group_concat语法如下

group_concat( [DISTINCT]  要连接的字段  [Order BY 排序字段 ASC/DESC]  [Separator ‘分隔符’] )

1.mysql中有如下这张表

CREATE TABLE `m_order`  (
  `id` int(10) NOT NULL,
  `user_id` int(10) NULL DEFAULT NULL,
  `order_pay` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `m_order` VALUES (1, 1001, 100);
INSERT INTO `m_order` VALUES (2, 1001, 200);
INSERT INTO `m_order` VALUES (3, 1001, 200);
INSERT INTO `m_order` VALUES (4, 1002, 50);
INSERT INTO `m_order` VALUES (5, 1002, 100);
INSERT INTO `m_order` VALUES (6, 1003, 500);

2.以user_id分组，把order_pay字段的值在同一行打印出来，逗号分隔(默认)

select user_id, group_concat(order_pay) order_pay from m_order group by user_id;  

 3.以user_id分组，把order_pay字段的值在一行打印出来，分号分隔

select user_id, group_concat(order_pay separator ';') order_pay from m_order group by user_id;  

 4.以id分组，把去除重复冗余的price字段的值打印在一行，逗号分隔

select user_id, group_concat(distinct order_pay ) order_pay from m_order group by user_id;

5.以id分组，把price字段的值去重打印在一行，逗号分隔，按照price倒序排列

select user_id, group_concat(distinct order_pay order by order_pay desc) order_pay from m_order group by user_id;