mysql 行转列（存储过程）

直接sql(自己抽离代码，无法和order by rand() 复用)：

select shit from( select 要累加的字段, @tt:=CONCAT(@tt,',',a.要累加的字段) as shit from 某表 a,(select @tt:='') b ) c ORDER BY LENGTH(c.shit)
 desc limit 0,1

 

存储过程版本:

BEGIN
    DECLARE Is_End INT DEFAULT 0;
    DECLARE _str VARCHAR(50) DEFAULT '';
    DECLARE _id INT;
    DECLARE _answer VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT id,answer FROM question_library where id > 100 ORDER BY RAND() LIMIT 10;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Is_End = 1;
    OPEN cur;
    FETCH cur INTO _id,_answer;
    WHILE Is_End != 1 DO
        SELECT CONCAT(_id,',',_str) INTO _str;
        FETCH cur INTO _id,_answer;
    END WHILE;
    CLOSE cur;
    SELECT left(_str,LENGTH(_str)-1) AS question;  #去掉最后一个字符串“，”
END

 

转载于:https://www.cnblogs.com/CyLee/p/5648407.html