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', '内容1');
INSERT INTO `article` VALUES ('2', '标题2', '内容2');
INSERT INTO `article` VALUES ('3', '标题3', '内容3');
INSERT INTO `article` VALUES ('4', '标题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 ('1', '1', '1');
INSERT INTO `article_tag` VALUES ('2', '1', '2');
INSERT INTO `article_tag` VALUES ('3', '1', '3');
INSERT INTO `article_tag` VALUES ('4', '1', '4');
INSERT INTO `article_tag` VALUES ('5', '2', '2');
INSERT INTO `article_tag` VALUES ('6', '2', '3');
INSERT INTO `article_tag` VALUES ('7', '2', '4');
INSERT INTO `article_tag` VALUES ('8', '3', '1');
INSERT INTO `article_tag` VALUES ('9', '3', '3');
INSERT INTO `article_tag` VALUES ('10', '4', '1');
INSERT INTO `article_tag` VALUES ('11', '4', '2');
INSERT INTO `article_tag` VALUES ('12', '4', '4');

-- ----------------------------
-- 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 ('1', 'tag1', '标签1');
INSERT INTO `tag` VALUES ('2', 'tag2', '标签2');
INSERT INTO `tag` VALUES ('3', 'tag3', '标签3');
INSERT INTO `tag` VALUES ('4', '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相同的当做同一条记录，至于标签信息，我们也希望把它们合并到一起，在同一个字段展示，如右图所示

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

三、我们可以通过使用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

秒客网

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

相关文章