Mysql合并列的两种方法

1、笨方法：游标+CONCAT_WS
示例：
 CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_Roster_SELECT_BY_ID`(
    IN pPK_RosterID bigint
    )
BEGIN

    DECLARE tempGN varchar(16);
    DECLARE rstGroupName varchar(128);  #联系人组结果列表，以“\n”分隔。   
    DECLARE noMore int;
   
    DECLARE curGN CURSOR FOR
            SELECT `F_GroupName` FROM `TS_RosterGroup` WHERE `PK_RosterID` = pPK_RosterID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = 1;

    ###获取联系人组列表
    SET rstGroupName = NULL;
    SET noMore = 0;  

    OPEN curGN;   
        FETCH curGN INTO tempGN;
                WHILE noMore <> 1 DO
                        SET rstGroupName = CONCAT_WS('\n', rstGroupName, tempGN);
                        FETCH curGN INTO tempGN;
                END WHILE;
    CLOSE curGN;
       
    ###返回结果。XMPP规定联系人可以不属于任何一个组。     
    SELECT TS_Roster.*, rstGroupName AS F_GroupName
    FROM
        `TS_Roster`
    WHERE
        `PK_RosterID` =  pPK_RosterID;
 
END;

2、Mysql特有函数：GROUP_CONCAT
示例：
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_Roster_SELECT_BY_ID`(
    IN pPK_RosterID bigint
    )
BEGIN
    SELECT TS_Roster.*, GROUP_CONCAT(F_GroupName SEPARATOR'\n')
    FROM TS_Roster LEFT JOIN TS_RosterGroup ON TS_Roster.PK_RosterID = TS_RosterGroup.PK_RosterID
    WHERE
        TS_Roster.PK_RosterID  =  pPK_RosterID
    GROUP BY TS_Roster.PK_RosterID
    ;
 
END;