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

 

mysql 行轉列（預存程序）