sql GROUP_CONCAT(... SEPARATOR)
SELECT post.ID, post.User_id, post.Title, GROUP_CONCAT( tag.Tag_value SEPARATOR ';' ) AS tags FROM post RIGHT JOIN tags_con_id ON tags_con_id.Post_ID = post.ID LEFT JOIN tag ON tag.Tag_id = tags_con_id.Tag_id GROUP BY post.ID LIMIT 0 , 30 -- -- 表的结构 `post` -- CREATE TABLE `post` ( `ID` int(11) NOT NULL, `Title` varchar(20) NOT NULL, `User_id` varchar(20) NOT NULL, `data` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- 导出表中的数据 `post` -- INSERT INTO `post` (`ID`, `Title`, `User_id`, `data`) VALUES (1, 'title1', '1', '2012-10-25'), (2, 'test2', '1', '2012-10-25'); -- -------------------------------------------------------- -- -- 表的结构 `tag` -- CREATE TABLE `tag` ( `Tag_id` int(11) NOT NULL, `Tag_value` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- 导出表中的数据 `tag` -- INSERT INTO `tag` (`Tag_id`, `Tag_value`) VALUES (1, 'Tag1'), (2, 'Tag2'), (3, 'Tag3'), (4, 'Tag4'), (5, 'Tag5'), (6, 'Tag6'), (7, 'Tag7'), (8, 'Tag8'); -- -------------------------------------------------------- -- -- 表的结构 `tags_con_id` -- CREATE TABLE `tags_con_id` ( `ID` int(11) NOT NULL, `Post_ID` int(11) NOT NULL, `Tag_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- 导出表中的数据 `tags_con_id` -- INSERT INTO `tags_con_id` (`ID`, `Post_ID`, `Tag_id`) VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 2, 4), (5, 2, 5), (6, 2, 6), (7, 2, 7);