我有这么张表想将纵表转成横表 时间是一个动态的每个月的每天都有信息(请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)
------------------------------ ----------------------------------------------
|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)
|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 |.............|
-----------------------------------------------
sum(case when logtime='2011-06-02' then lognum end),
...from tt group by `user`
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 ;
页面使用定义的变量替换 但是这个我不知道怎么去用?不是去使用它
set @asql=tempsql;
prepare stml from @asql;
execute stml;
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
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 ;逻辑部份自行修改
建议你学习一下MYSQL,看看其HELP,否则交流起来太困难,逻辑部份自行修改
[SQL] call ff1();
[Err] 1406 - Data too long for column 'tempsql' at row 1
MySQL交叉表