MYSQL实现列拼接，即同一个字段，多条记录拼接成一条

一、首先，新建三张表

DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `title` varchar(255) NOT NULL COMMENT '标题',

  `content` text NOT NULL COMMENT '内容',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='文章表';

-- ----------------------------

-- Records of article

-- ----------------------------

INSERT INTO `article` VALUES ('', '标题1', '内容1');

INSERT INTO `article` VALUES ('', '标题2', '内容2');

INSERT INTO `article` VALUES ('', '标题3', '内容3');

INSERT INTO `article` VALUES ('', '标题4', '内容4');

-- ----------------------------

-- Table structure for article_tag

-- ----------------------------

DROP TABLE IF EXISTS `article_tag`;

CREATE TABLE `article_tag` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `article_id` int(10) unsigned NOT NULL COMMENT '文章ID',

  `tag_id` int(10) unsigned NOT NULL COMMENT '标签ID',

  PRIMARY KEY (`id`),

  KEY `idx_tag_id` (`tag_id`) USING BTREE,

  KEY `idx_article_id` (`article_id`) USING BTREE

) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='文章-标签关联表';

-- ----------------------------

-- Records of article_tag

-- ----------------------------

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

INSERT INTO `article_tag` VALUES ('', '', '');

-- ----------------------------

-- Table structure for tag

-- ----------------------------

DROP TABLE IF EXISTS `tag`;

CREATE TABLE `tag` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `tag_name` varchar(255) NOT NULL COMMENT '标签名',

  `tag_desc` varchar(255) NOT NULL COMMENT '标签注释',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='标签表';

-- ----------------------------

-- Records of tag

-- ----------------------------

INSERT INTO `tag` VALUES ('', 'tag1', '标签1');

INSERT INTO `tag` VALUES ('', 'tag2', '标签2');

INSERT INTO `tag` VALUES ('', 'tag3', '标签3');

INSERT INTO `tag` VALUES ('', 'tag4', '标签4');

二、接下来查询关联关系

SELECT

    t1.*,tag_name,tag_desc

FROM

    `article` t1

LEFT JOIN article_tag t2 ON t1.id = t2.article_id

LEFT JOIN tag t3 ON t2.tag_id = t3.id

这是我们正常查询得到的结果，但实际上我们一般是想要把文章id相同的当做同一条记录，至于标签信息，我们也希望把它们合并到一起，在同一个字段展示，如右图所示

                

三、我们可以通过使用GROUP_CONCAT()函数来得到这种结果

SELECT

    t1.*,GROUP_CONCAT(tag_name) tag_name,GROUP_CONCAT(tag_desc) tag_desc

FROM

    `article` t1

LEFT JOIN article_tag t2 ON t1.id = t2.article_id

LEFT JOIN tag t3 ON t2.tag_id = t3.id

GROUP BY t1.id