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;
阅读(6324) | 评论(0) | 转发(0) |