mysql 列合并_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;

阅读(5244) | 评论(0) | 转发(0) |