急：SQL统计问题

Question

问题描述:
--用户表
 create table baseuser  
 (
 usercode varchar(50)  primary key , 
 fullname varchar(100) not null
 );--项目表
create table projects 
(
id int primary key auto_increment,
projectname varchar(100) unique key  not null
);--周工作表
create table weekly
(
id int primary key auto_increment,
usercode varchar(50),commitdate     datetime,--提交日期PROJECT VARCHAR(100) DEFAULT ""; --对应项目表中的projectname，十个字段表示一周最多参与十个项目
PROJECT2 VARCHAR(100) DEFAULT "";
PROJECT4 VARCHAR(100) DEFAULT "";
PROJECT5 VARCHAR(100) DEFAULT "";
PROJECT6 VARCHAR(100) DEFAULT "";
PROJECT7 VARCHAR(100) DEFAULT "";
PROJECT8 VARCHAR(100) DEFAULT "";
PROJECT9 VARCHAR(100) DEFAULT "";
PROJECT10 VARCHAR(100) DEFAULT "";WORKHOURS SMALLINT(6) DEFAULT 0; --工时，和上面项目一一对应
WORKHOURS2 SMALLINT(6) DEFAULT 0;
WORKHOURS3 SMALLINT(6) DEFAULT 0;
WORKHOURS4 SMALLINT(6) DEFAULT 0;
WORKHOURS5 SMALLINT(6) DEFAULT 0;
WORKHOURS6 SMALLINT(6) DEFAULT 0;
WORKHOURS7 SMALLINT(6) DEFAULT 0;
WORKHOURS8 SMALLINT(6) DEFAULT 0;
WORKHOURS9 SMALLINT(6) DEFAULT 0;
WORKHOURS10 SMALLINT(6) DEFAULT 0;
 );
 
 在某个时间段（提交日期）内，按项目，人员统计工时。
 项目名称 和 员工姓名都是从表里面取得的
 
 统计要求：
 
 项目  员工姓名1  员工姓名2 员工姓名3     合计 
 
 项目1   15          12        0            55
 项目2   15          12        0            66
 项目3   15          12        0            77
 项目4   15          12        0            88
 项目5   15          12        0            99 请高手门指点下拉拉拉

Answer 1

create table weekly
(
id int primary key auto_increment,
usercode varchar(50),commitdate     datetime,--提交日期PROJECT VARCHAR(100) DEFAULT ""; --对应项目表中的projectname，十个字段表示一周最多参与十个项目
PROJECT2 VARCHAR(100) DEFAULT "";
PROJECT4 VARCHAR(100) DEFAULT "";
PROJECT5 VARCHAR(100) DEFAULT "";
PROJECT6 VARCHAR(100) DEFAULT "";
PROJECT7 VARCHAR(100) DEFAULT "";
PROJECT8 VARCHAR(100) DEFAULT "";
PROJECT9 VARCHAR(100) DEFAULT "";
PROJECT10 VARCHAR(100) DEFAULT "";WORKHOURS SMALLINT(6) DEFAULT 0; --工时，和上面项目一一对应
WORKHOURS2 SMALLINT(6) DEFAULT 0;
WORKHOURS3 SMALLINT(6) DEFAULT 0;
WORKHOURS4 SMALLINT(6) DEFAULT 0;
WORKHOURS5 SMALLINT(6) DEFAULT 0;
WORKHOURS6 SMALLINT(6) DEFAULT 0;
WORKHOURS7 SMALLINT(6) DEFAULT 0;
WORKHOURS8 SMALLINT(6) DEFAULT 0;
WORKHOURS9 SMALLINT(6) DEFAULT 0;
WORKHOURS10 SMALLINT(6) DEFAULT 0;
  );楼主你的这个SQL语句能正确执行吗？

Answer 2

仅通过你的描述，需要用存储过程来实现。
建议你能给出测试数据 (不要高估你的汉语表达能力或者我的汉语理解能力)
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 3

基本上的实现方法你可以参考下贴。http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx
MySQL交叉表

Answer 4

--用户表
 create table baseuser   
 (
 usercode varchar(50) primary key ,  
 fullname varchar(100) not null
 );--项目表
create table projects  
(
id int primary key auto_increment,
projectname varchar(100) unique key not null
);--周工作表
create table weekly
(
id int primary key auto_increment,
usercode varchar(50),
commitdate datetime,--提交日期PROJECT VARCHAR(100) DEFAULT "", --对应项目表中的projectname，十个字段表示一周最多参与十个项目
PROJECT2 VARCHAR(100) DEFAULT "",
PROJECT3 VARCHAR(100) DEFAULT "",
PROJECT4 VARCHAR(100) DEFAULT "",
PROJECT5 VARCHAR(100) DEFAULT "",
PROJECT6 VARCHAR(100) DEFAULT "",
PROJECT7 VARCHAR(100) DEFAULT "",
PROJECT8 VARCHAR(100) DEFAULT "",
PROJECT9 VARCHAR(100) DEFAULT "",
PROJECT10 VARCHAR(100) DEFAULT "",WORKHOURS SMALLINT(6) DEFAULT 0, --工时，和上面项目一一对应
WORKHOURS2 SMALLINT(6) DEFAULT 0,
WORKHOURS3 SMALLINT(6) DEFAULT 0,
WORKHOURS4 SMALLINT(6) DEFAULT 0,
WORKHOURS5 SMALLINT(6) DEFAULT 0,
WORKHOURS6 SMALLINT(6) DEFAULT 0,
WORKHOURS7 SMALLINT(6) DEFAULT 0,
WORKHOURS8 SMALLINT(6) DEFAULT 0,
WORKHOURS9 SMALLINT(6) DEFAULT 0,
WORKHOURS10 SMALLINT(6) DEFAULT 0
 );
 
 insert into baseuser values ('zhangsan','张三');
 insert into baseuser values ('lisi','李四');
 insert into baseuser values ('wangwu','王五');
  
 insert into projects  VALUES(1,'学生信息管理系统');
 insert into projects  VALUES(2,'图书管理系统');
 insert into projects  VALUES(3,'超市管理系统');
 insert into projects  VALUES(4,'仓库管理系统');
 insert into projects  VALUES(5,'PETSHOP');
 insert into projects  VALUES(6,'报表系统');
 insert into projects  VALUES(7,'通勤系统');
 insert into projects  VALUES(8,'会议室预定');
 
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,PROJECT3,WORKHOURS,WORKHOURS2,WORKHOURS3)  values (1,'zhangsan','2010-03-17 12:00:00','学生信息管理系统','图书管理系统','PETSHOP',20,30,15);
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,WORKHOURS,WORKHOURS2)  values (2,'lisi','2010-03-17 12:00:00','报表系统','PETSHOP',30,10);
 insert into  weekly(id,usercode,commitdate,PROJECT,WORKHOURS)  values (3,'wangwu','2010-03-17 12:00:00','超市管理系统',50);
 
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,PROJECT3,WORKHOURS,WORKHOURS2,WORKHOURS3)  values (4,'zhangsan','2010-03-24 12:00:00','学生信息管理系统','图书管理系统','PETSHOP',20,30,15);
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,WORKHOURS,WORKHOURS2)  values (5,'lisi','2010-03-24 12:00:00','报表系统','PETSHOP',30,10);
 insert into  weekly(id,usercode,commitdate,PROJECT,WORKHOURS)  values (6,'wangwu','2010-03-24 12:00:00','超市管理系统',50);
 
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,PROJECT3,WORKHOURS,WORKHOURS2,WORKHOURS3)  values (7,'zhangsan','2010-03-30 12:00:00','学生信息管理系统','通勤系统','PETSHOP',20,30,15);
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,WORKHOURS,WORKHOURS2)  values (8,'lisi','2010-03-30 12:00:00','报表系统','PETSHOP',30,10);
 insert into  weekly(id,usercode,commitdate,PROJECT,WORKHOURS)  values (9,'wangwu','2010-03-30 12:00:00','超市管理系统',50);
 
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,PROJECT3,WORKHOURS,WORKHOURS2,WORKHOURS3)  values (10,'zhangsan','2010-04-06 12:00:00','图书管理系统','通勤系统','会议室预定',20,30,15);
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,WORKHOURS,WORKHOURS2)  values (11,'lisi','2010-04-06 12:00:00','仓库管理系统','PETSHOP',30,10);
 insert into  weekly(id,usercode,commitdate,PROJECT,WORKHOURS)  values (12,'wangwu','2010-04-06 12:00:00','仓库管理系统',50);
 
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,PROJECT3,WORKHOURS,WORKHOURS2,WORKHOURS3)  values (13,'zhangsan','2010-04-13 12:00:00','图书管理系统','通勤系统','会议室预定',20,30,15);
 insert into  weekly(id,usercode,commitdate,PROJECT,PROJECT2,WORKHOURS,WORKHOURS2)  values (14,'lisi','2010-04-13 12:00:00','仓库管理系统','PETSHOP',30,10);
 insert into  weekly(id,usercode,commitdate,PROJECT,WORKHOURS)  values (15,'wangwu','2010-04-13 12:00:00','仓库管理系统',50);
 /*
 在某个时间段（提交日期）内，按项目，人员统计工时。
 项目名称 和 员工姓名都是从表里面取得的
  
 统计要求：
  
 项目             张三  李四  王五  合计  
 
 学生信息管理系统 15     12    0    55
 图书管理系统     15     12    0    66
 超市管理系统     15     12    0    77
 仓库管理系统     15     12    0    88
 PETSHOP          15     12    0    99
 报表系统         15     12    0    99
 通勤系统         15     12    0    99
 会议室预定       15     12    0    99
 */国庆放假 工作也放下来了，我这个和交差表有些不同吧 很伤脑筋。

Answer 5

不同吗？结果不仍是个交叉表吗？不要轻易下结论。你外你提供的数据和你的希望结果并不匹配，你的 这个 张三， 15个小时是怎么来的？ 举例时请尽量给出准确的例子。
 项目             张三  
  学生信息管理系统 15mysql> select PROJECT,
    ->  sum(if(usercode='zhangsan',WORKHOURS,0)) as `张三`,
    ->  sum(if(usercode='lisi',WORKHOURS,0)) as `李四`,
    ->  sum(if(usercode='wangwu',WORKHOURS,0)) as `王五`,
    ->  sum(WORKHOURS) as `合计`
    -> from (
    ->  select id,usercode,commitdate,PROJECT,WORKHOURS from weekly where PROJECT !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT2,WORKHOURS2 from weekly where PROJECT2 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT3,WORKHOURS3 from weekly where PROJECT3 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT4,WORKHOURS4 from weekly where PROJECT4 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT5,WORKHOURS5 from weekly where PROJECT5 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT6,WORKHOURS6 from weekly where PROJECT6 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT7,WORKHOURS7 from weekly where PROJECT7 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT8,WORKHOURS8 from weekly where PROJECT8 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT9,WORKHOURS9 from weekly where PROJECT9 !=''
    ->  union all
    ->  select id,usercode,commitdate,PROJECT10,WORKHOURS10 from weekly where PROJECT10 !=''
    -> ) t
    -> group by PROJECT;
+------------------+------+------+------+------+
| PROJECT          | 张三 | 李四 | 王五 | 合计 |
+------------------+------+------+------+------+
| 通勤系统         |   90 |    0 |    0 |   90 |
| 图书管理系统     |  100 |    0 |    0 |  100 |
| 学生信息管理系统 |   60 |    0 |    0 |   60 |
| PETSHOP          |   45 |   50 |    0 |   95 |
| 报表系统         |    0 |   90 |    0 |   90 |
| 仓库管理系统     |    0 |   60 |  100 |  160 |
| 超市管理系统     |    0 |    0 |  150 |  150 |
| 会议室预定       |   30 |    0 |    0 |   30 |
+------------------+------+------+------+------+
8 rows in set (0.04 sec)mysql>

Answer 6

SET @EE=''; 
SELECT @EE:=CONCAT(@EE,'SUM(IF(usercode=\'',usercode,'\'',',WORKHOURS,0)) AS ',usercode,',') 
             FROM (SELECT DISTINCT usercode FROM baseuser) A;SET @QQ=CONCAT('SELECT PROJECT,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(WORKHOURS) AS TOTAL  from (
      select id,usercode,commitdate,PROJECT,WORKHOURS from weekly where PROJECT !=''
      union all
     select id,usercode,commitdate,PROJECT2,WORKHOURS2 from weekly where PROJECT2 !=''
      union all
      select id,usercode,commitdate,PROJECT3,WORKHOURS3 from weekly where PROJECT3 !=''
      union all
      select id,usercode,commitdate,PROJECT4,WORKHOURS4 from weekly where PROJECT4 !=''
     union all
     select id,usercode,commitdate,PROJECT5,WORKHOURS5 from weekly where PROJECT5 !=''
      union all
      select id,usercode,commitdate,PROJECT6,WORKHOURS6 from weekly where PROJECT6 !=''
      union all
      select id,usercode,commitdate,PROJECT7,WORKHOURS7 from weekly where PROJECT7 !=''
      union all
      select id,usercode,commitdate,PROJECT8,WORKHOURS8 from weekly where PROJECT8 !=''
      union all
      select id,usercode,commitdate,PROJECT9,WORKHOURS9 from weekly where PROJECT9 !=''
      union all
      select id,usercode,commitdate,PROJECT10,WORKHOURS10 from weekly where PROJECT10 !=''
     ) t
     group by PROJECT WITH ROLLUP');PREPARE stmt2 FROM @QQ;EXECUTE stmt2;mysql> EXECUTE stmt2;
+------------------+----------+-------+
| PROJECT          | zhangsan | TOTAL |
+------------------+----------+-------+
|                  |        0 |     0 |
| PETSHOP          |       45 |    45 |
| 仓库管理系统     |        0 |   160 |
| 会议室预定       |       30 |    30 |
| 图书管理系统     |       40 |    40 |
| 学生信息管理系统 |       60 |    60 |
| 报表系统         |        0 |    90 |
| 超市管理系统     |        0 |   150 |
| NULL             |      175 |   575 |
+------------------+----------+-------+
9 rows in set (0.00 sec)
还有点问题请指点下：
1、动态的情况结果怎么不对啊
2、还有你那个静态的结果，包括表头，正是我要生成的Excel，也是我的最终目的；但是执行sql得到的结果集好像没有存储列名吧，就是PROJECT | 张三 | 李四 | 王五 | 合计  这些信息，关于这方面有什么好的方法呢

Answer 7

SET @EE=''; 
SELECT @EE:=CONCAT(@EE,'SUM(IF(usercode=\'',usercode,'\'',',WORKHOURS,0)) AS `',fullname,'`,') 
             FROM baseuser;SET @QQ=CONCAT('SELECT PROJECT,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(WORKHOURS) AS TOTAL  from (
      select id,usercode,commitdate,PROJECT,WORKHOURS from weekly where PROJECT !=\'\'
      union all
     select id,usercode,commitdate,PROJECT2,WORKHOURS2 from weekly where PROJECT2 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT3,WORKHOURS3 from weekly where PROJECT3 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT4,WORKHOURS4 from weekly where PROJECT4 !=\'\'
     union all
     select id,usercode,commitdate,PROJECT5,WORKHOURS5 from weekly where PROJECT5 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT6,WORKHOURS6 from weekly where PROJECT6 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT7,WORKHOURS7 from weekly where PROJECT7 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT8,WORKHOURS8 from weekly where PROJECT8 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT9,WORKHOURS9 from weekly where PROJECT9 !=\'\'
      union all
      select id,usercode,commitdate,PROJECT10,WORKHOURS10 from weekly where PROJECT10 !=\'\'
     ) t
     group by PROJECT WITH ROLLUP');PREPARE stmt2 FROM @QQ;EXECUTE stmt2;
mysql> EXECUTE stmt2;
+------------------+------+------+------+-------+
| PROJECT          | 张三 | 李四 | 王五 | TOTAL |
+------------------+------+------+------+-------+
| 通勤系统         |   90 |    0 |    0 |    90 |
| 图书管理系统     |  100 |    0 |    0 |   100 |
| 学生信息管理系统 |   60 |    0 |    0 |    60 |
| PETSHOP          |   45 |   50 |    0 |    95 |
| 报表系统         |    0 |   90 |    0 |    90 |
| 仓库管理系统     |    0 |   60 |  100 |   160 |
| 超市管理系统     |    0 |    0 |  150 |   150 |
| 会议室预定       |   30 |    0 |    0 |    30 |
| NULL             |  325 |  200 |  250 |   775 |
+------------------+------+------+------+-------+
9 rows in set (0.00 sec)mysql>

Answer 8

帮人帮到底,这个怎么写成存储过程，还要传2个日期参数，就是   where PROJECT !=\'\'后面应该加个条件 and commitdate > 参数1 and commitdate < 参数2