56,679
社区成员
发帖
与我相关
我的任务
分享
--用户表
CREATE TABLE `user` (
`userid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`departmentid` INT(11) DEFAULT NULL COMMENT '部门ID',
) ENGINE=MYISAM DEFAULT CHARSET=utf8
INSERT INTO USER SELECT 1,1
INSERT INTO USER SELECT 2,2
INSERT INTO USER SELECT 3,2
INSERT INTO USER SELECT 4,4
INSERT INTO USER SELECT 5,5
-- 任务得分表
CREATE TABLE `points` (
`USERID` INT(11) NOT NULL COMMENT '人员',
`SCORE` DOUBLE NOT NULL COMMENT '分数',
`operatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
) ENGINE=MYISAM DEFAULT CHARSET=utf8
INSERT INTO points SELECT 1,10,'2010-12-01'
INSERT INTO points SELECT 2,8,'2010-11-01'
INSERT INTO points SELECT 2,8,'2010-12-01'
INSERT INTO points SELECT 2,10,'2010-12-01'
INSERT INTO points SELECT 5,10,'2010-12-01'
INSERT INTO points SELECT 3,10,'2010-11-02'
INSERT INTO points SELECT 4,10,'2010-12-02'
-- 调查问卷成绩表
CREATE TABLE `questionnaire_form` (
`userid` INT(11) NOT NULL,
`status` INT(11) NOT NULL DEFAULT '0',
`score` DOUBLE NOT NULL DEFAULT '0',
`performancemonth` VARCHAR(10) DEFAULT NULL,
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO questionnaire_form SELECT 2,1,18,'2010-11'
INSERT INTO questionnaire_form SELECT 2,1,20,'2010-12'
INSERT INTO questionnaire_form SELECT 2,1,18,'2010-12'
INSERT INTO questionnaire_form SELECT 3,1,20,'2010-12'
INSERT INTO questionnaire_form SELECT 2,0,0,'2010-12'
INSERT INTO questionnaire_form SELECT 4,0,0,'2010-12'
INSERT INTO questionnaire_form SELECT 4,0,0,'2010-12'
-- 汇总表
CREATE TABLE `performances` (
`performancemonth` VARCHAR(10) NOT NULL,
`userid` INT(11) NOT NULL,
`taskscore` DOUBLE NOT NULL,
`abilityscore` DOUBLE NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 要将这样的报表插入上表中
/*
performancemonth userid taskscore abilityscore
2010-12 2 9 19
2010-12 3 0 20
2010-12 4 10 0
描述:
1、只统计user表中departmentid为2、4的用户
2、只统计2010-12月数据
3、只统计abilityscore表中status字段为1的数据
3、taskscore为points表score字段平均值
4、abilityscore为questionnaire_form表score字段平均值
*/
CREATE TABLE `user` (
`userid` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
`departmentid` INT(11) DEFAULT NULL COMMENT '部门ID'
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO `USER` SELECT 1,1;
INSERT INTO `USER` SELECT 2,2;
INSERT INTO `USER` SELECT 3,2;
INSERT INTO `USER` SELECT 4,4;
INSERT INTO `USER` SELECT 5,5;
CREATE TABLE `points` (
`USERID` INT(11) NOT NULL COMMENT '人员',
`SCORE` DOUBLE NOT NULL COMMENT '分数',
`operatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO points SELECT 1,10,'2010-12-01';
INSERT INTO points SELECT 2,8,'2010-11-01';
INSERT INTO points SELECT 2,8,'2010-12-01';
INSERT INTO points SELECT 2,10,'2010-12-01';
INSERT INTO points SELECT 5,10,'2010-12-01';
INSERT INTO points SELECT 3,10,'2010-11-02';
INSERT INTO points SELECT 4,10,'2010-12-02';
CREATE TABLE `questionnaire_form` (
`userid` INT(11) NOT NULL,
`status` INT(11) NOT NULL DEFAULT '0',
`score` DOUBLE NOT NULL DEFAULT '0',
`performancemonth` VARCHAR(10) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO questionnaire_form SELECT 2,1,18,'2010-11';
INSERT INTO questionnaire_form SELECT 2,1,20,'2010-12';
INSERT INTO questionnaire_form SELECT 2,1,18,'2010-12';
INSERT INTO questionnaire_form SELECT 3,1,20,'2010-12';
INSERT INTO questionnaire_form SELECT 2,0,0,'2010-12';
INSERT INTO questionnaire_form SELECT 4,0,0,'2010-12';
INSERT INTO questionnaire_form SELECT 4,0,0,'2010-12';
SELECT p_time AS performancemonth, a.*,IFNULL(b.abilityscore,0),IFNULL(c.taskscore,0) FROM `user` a
LEFT JOIN
(SELECT userid,AVG(score) AS abilityscore,MAX(performancemonth) AS p_time FROM questionnaire_form WHERE STATUS=1 AND performancemonth='2010-12' GROUP BY userid) b
ON a.userid=b.userid
LEFT JOIN
(SELECT userid,AVG(score) AS taskscore FROM points WHERE DATE(`operatetime`) BETWEEN '2010-12-01' AND '2010-12-31' GROUP BY userid) c
ON a.userid=c.userid
WHERE a.`departmentid` IN (2,4)