Question

mysql 存储过程把一列分多列

有数据表 myDat
NameID AllC
2003 满意
2003 满意
2004 满意
2004 未平
2003 一般
2004 不满意
2005 满意

查询后 生成新表New_MyData并插入查询数据
NameID AllC1(满意) AllC2(一般) AllC3（不满意） AllC4（为评价） All
2003 2 1 0 0 3
2004 1 0 1 1 3
2005 1 0 0 0 1

用存储过程 怎么写

Answer 1

不需要写过程的，就是个行转列查询，使用group by分组即可，语句如下：
INSERT INTO New_Mydata
(Nameid, Allc1, Allc2, Allc3, Allc4, ALL)
SELECT Nameid,
SUM(CASE Allc WHEN '满意' THEN 1 ELSE 0 END),
SUM(CASE Allc WHEN '一般' THEN 1 ELSE 0 END),
SUM(CASE Allc WHEN '不满意' THEN 1 ELSE 0 END),
SUM(CASE Allc WHEN '未平' THEN 1 ELSE 0 END),
SUM(*)
FROM Mydat
GROUP BY Nameid

Answer 2

CREATE PROCEDURE test_mydat ()
BEGIN
DECLARE record_not_found INT DEFAULT 0; DECLARE _allc VARCHAR (10);
DECLARE strSql VARCHAR (8000);
DECLARE addSql VARCHAR (8000);
DECLARE Qi_sql1 VARCHAR (100);
DECLARE Qi_sql2 VARCHAR (100);
DECLARE Qi_sqlC VARCHAR (300);
DECLARE cca_cursor CURSOR FOR (SELECT DISTINCT md.allc FROM myDat md );
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;

SET @num = 0;
SET @s = "'";
SET @addSql=' ';
SET @Qi_sql1=CONCAT(',MAX(CASE WHEN md.allc=',@s);
SET @Qi_sql2=CONCAT(@s,' THEN md.num ELSE 0 END) AS ',@s);
OPEN cca_cursor;
all_course:LOOP
FETCH cca_cursor INTO _allc;
IF record_not_found THEN
LEAVE all_course;
END IF;
SET @num:=@num+1;
SET @Qi_sqlC = CONCAT(@Qi_sql1,_allc,@Qi_sql2,'Allc',@num,'(',_allc,')',@s);
SET @addSql = CONCAT(@addSql,@Qi_sqlC);
END LOOP all_course;
CLOSE cca_cursor;

SET @strSql = CONCAT(
'CREATE TABLE new_mydata SELECT md.nameId',
@addSql,',COUNT(0) AS "All" FROM (SELECT md.nameId,md.allc,COUNT(0) AS "num" FROM mydat md GROUP BY md.nameId,md.allc) md ',
'GROUP BY md.nameId'
);

PREPARE stmt FROM @strSql;
EXECUTE stmt;

END

我没有在新生成的表做过多操作。包括主键等等。需要的话可以翻翻资料。那个挺简单的。