[MySQL]行列转换变化各种方法实现总结（行变列报表统计、列变行数据记录统计等）(二)
2015-11-21 01:34:33
·
作者:
·
浏览: 13
DURE csdn.`pro_flow_modify`(p_Number INT,p_city VARCHAR(10))
BEGIN
DECLARE v_column_name VARCHAR(10) DEFAULT '';
DECLARE v_exe_sql VARCHAR(1000) DEFAULT '';
DECLARE v_start_wm VARCHAR(10) DEFAULT '';
DECLARE v_end_wm VARCHAR(10) DEFAULT '';
DECLARE v_num DECIMAL(10,2) DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE v_Number INT DEFAULT 0;
SET v_Number=p_Number;
DELETE FROM csdn.flow_n_columns;
DELETE FROM csdn.flow_tmp WHERE Number=v_Number;
/*把测试表flow_table的所有字段都录入字段临时表中，这样就达到了从列变成行的目的*/
INSERT INTO flow_n_columns
SELECT t.`COLUMN_NAME` FROM information_schema.`COLUMNS` t WHERE t.`TABLE_NAME`='flow_table' AND t.`TABLE_SCHEMA`='csdn' AND t.`COLUMN_NAME` NOT IN('ID','Number','City');
SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1;
/*开始循环遍历字段临时表的字段数据，并且把字段值放入临时表flow_tmp里面*/
WHILE i>0 DO
SET v_exe_sql=CONCAT('INSERT INTO csdn.flow_tmp(Number,City,wm_str,Wm) select ',v_Number,',\'',p_city, '\',\'',v_column_name,'\',',v_column_name,' from csdn.flow_table WHERE flow_table.Number=',v_Number,';');
SET @sql=v_exe_sql;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
DELETE FROM csdn.flow_n_columns WHERE column_name=v_column_name;
SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1;
SELECT COUNT(1) INTO i FROM csdn.flow_n_columns ;
DELETE FROM csdn.flow_tmp WHERE Wm=0;
END WHILE;
/*由于触发器是不支持动态sql，所以不能使用while循环，动态遍历所有统计列的，只能写死列了，如下所示：
现在一个个insert只能写死了, flow_table表有多少个统计列就写多少个insert sql，以后新添加一个列，就在这里新添加一条insertsql语句
INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201403',wm201403 FROM flow_table WHERE Number=v_Number ;
INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201404',wm201404 FROM flow_table WHERE Number=v_Number ;
INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201405',wm201405 FROM flow_table WHERE Number=v_Number ;
INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201406',wm201406 FROM flow_table WHERE Number=v_Number ;
INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201407',wm201407 FROM flow_table WHERE Number=v_Number ;
INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201408',wm201408 FROM flow_table WHERE Number=v_Number ;
*/
/*清除掉不数据=0的列*/
DELETE FROM csdn.flow_tmp WHERE Wm=0 OR Wm IS NULL;
SELECT wm_str INTO v_start_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str ASC LIMIT 1;
SELECT wm_str INTO v_end_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str DESC LIMIT 1;
SELECT SUM(Wm) INTO v_num FROM csdn.flow_tmp WHERE Number=v_Number;
END$$
DELIMITER ;?
4，列变行结果展示
临时表的所有数据：
mysql> SELECT * FROM csdn.flow_tmp;
+--------+----------+----------+--------+
| Number | City | wm_str | Wm |
+--------+----------+----------+--------+
| 1 | shanghai | wm201403 | 100.20 |
| 1 | shanghai | wm201404 | 180.40 |
| 1 | shanghai | wm201405 | 141.00 |
| 1 | shanghai | wm201406 | 164.00 |
| 1 | shanghai | wm201407 | 124.00 |
| 1 | shanghai | wm201408 | 127.00 |
| 2 | shanghai | wm201403 | 110.23 |
| 2 | shanghai | wm201404 | 180.34 |
| 2 | shanghai | wm201405 | 141.23 |
| 2 | shanghai | wm201406 | 104.78 |
| 2 | shanghai | wm2014