mysql 纵表转化横表

[cjoy4856]

我有这么张表想将纵表转成横表 时间是一个动态的每个月的每天都有信息(请mysql的大侠给以帮助)
------------------------------ ----------------------------------------------
|username | lognum | logtime | | user |2011-06-01|2011-06-02|.............|
-------------------------------- ----------------------------------------------
| A | 2 |2011-06-01| | A | 2 | 3 |.............|
------------------------------------ ======> --------------------------------------------------
| A | 3 |2011-06-02| | B | 0 | 0 |.............|
------------------------------------ -----------------------------------------------
| B | 0 |2011-06-01|
------------------------------------
| B | 0 |2011-06-02|
------------------------------------
数据库是mysql 需求是我查询的时候是查询上个月所有天的信息。如我查询2月份的28天的所有信息 从1~28（29）

[WWWWA]

导出到CSV文件，在EXCEL中行列转置，再导入MYSQL中，SQL比较复杂

[cjoy4856]

----------------------------------
|username | lognum | logtime |
--------------------------------
| A | 2 |2011-06-01|
------------------------------------
| A | 3 |2011-06-02|
------------------------------------
| B | 0 |2011-06-01|
------------------------------------
| B | 0 |2011-06-02|
------------------------------------
成为：
----------------------------------------------
| user |2011-06-01|2011-06-02|.............|
----------------------------------------------
| A | 2 | 3 |.............|
--------------------------------------------------
| B | 0 | 0 |.............|
-----------------------------------------------

[WWWWA]

select `user`,sum(case when logtime='2011-06-01' then lognum end),
sum(case when logtime='2011-06-02' then lognum end),
...

from tt group by `user`

[cjoy4856]

现在就是如果我转化成了Excel excel-->mysql 这个怎么创建表？

[WWWWA]

参考3楼，也可以用SP生成SQL语句，再执行

[cjoy4856]

   DECLARE i INT DEFAULT 2;
	DECLARE tempsql VARCHAR(2000);
	DECLARE yearLocal int DEFAULT YEAR(NOW());
	DECLARE monthLocal int DEFAULT MONTH(NOW());
	tempsql = 'select username ,sum(nlognum),max(case logtime WHEN \'2011-06-01\' THEN nlognum else 0 END) as \'2011-06-01\'';
	WHILE i<=DAY(LAST_DAY(NOW()))
	BEGIN
		tempsql = tempsql+ ',max(case logtime WHEN \'2011-06-'+i+'\' THEN nlognum else 0 END) as \'2011-06-'+i+'';
		i=i+1;
	END
	tempsql = tempsql+FROM hpre_common_userconfig where MONTH(logtime)=06 GROUP BY username ;

页面使用定义的变量替换 但是这个我不知道怎么去用？不是去使用它

[cjoy4856]

我使用了上面的ping到之后不知道怎么去用 晕死....请赐教

[WWWWA]

你将tempsql中的内容显示出来看看，再动态执行
set @asql=tempsql;
prepare stml from @asql;
execute stml;

[cjoy4856]

   DECLARE i INT DEFAULT 2;
DECLARE tempsql VARCHAR(2000);
DECLARE yearLocal int ;
DECLARE monthLocal int;
  yearLocal=YEAR(NOW());
  monthLocal=MONTH(now);
tempsql = 'select username ,sum(nlognum),max(case logtime WHEN \''+yearLocal+'-'+monthLocal+'-01\' THEN nlognum else 0 END) as \''+yearLocal+'-'+monthLocal+'-01\'';
WHILE i<=DAY(LAST_DAY(NOW()))
BEGIN
	tempsql = tempsql+ ',max(case logtime WHEN \''+yearLocal+'-'+monthLocal+'-'+i+'\' THEN nlognum else 0 END) as \''+yearLocal+'-'+monthLocal+'-'+i+'';
	i=i+1;
END
tempsql = tempsql+FROM hpre_common_userconfig where MONTH(logtime)=monthLocal GROUP BY username ;

set @asql=tempsql;
prepare stml from @asql;
execute stml;

报异常 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE i INT DEFAULT 2' at line 11

[WWWWA]

改动比较多
DELIMITER $$
CREATE PROCEDURE ff1()
BEGIN
DECLARE i INT DEFAULT 2;
DECLARE tempsql VARCHAR(2000);
DECLARE yearLocal INT ;
DECLARE monthLocal INT;
SET yearLocal=YEAR(NOW());
SET monthLocal=MONTH(NOW());
SET tempsql =CONCAT('select username ,sum(nlognum),max(case logtime WHEN \'',yearLocal,'-',monthLocal,'-01\' THEN nlognum else 0 END) as \'',yearLocal,'-',monthLocal,'-01\'');
WHILE i<=DAY(LAST_DAY(NOW())) DO
SET tempsql = CONCAT(tempsql, ',max(case logtime WHEN \'',yearLocal,'-',monthLocal,'-',i,'\' THEN nlognum else 0 END) as \'',yearLocal,'-',monthLocal,'-',i,'');
SET i=i+1;
END WHILE;
SET tempsql = CONCAT(tempsql,'FROM hpre_common_userconfig where MONTH(logtime)=monthLocal GROUP BY username ');

SET @asql=tempsql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;

逻辑部份自行修改

[cjoy4856]

10L 我copy的 运行没有问题，我是不是call PROCEDURE ff1？如果是的话，没有任何结果？我数据库不懂...谢谢

[WWWWA]

call ff1()
建议你学习一下MYSQL，看看其HELP，否则交流起来太困难，逻辑部份自行修改

[cjoy4856]

真不好意思！！！确实 我对数据库的知识不是很了解。
[SQL] call ff1();
[Err] 1406 - Data too long for column 'tempsql' at row 1

[ACMAIN_CHM]

http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx
MySQL交叉表