mysql多表统计_MySQL多表汇总统计的SQL语句

--用户表

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

需求描述：

统计user表中departmentid为2、4的用户在2010年12月的任务得分和调查问卷得分的平均值，然后插入汇总表。若任一表中没有用户的记录，则表对应的得分项为0

注意事项：

1、只统计2010-12月数据

2、只统计abilityscore表中status字段为1的数据

3、taskscore为任务得分表score字段平均值

4、abilityscore为调查问卷成绩表score字段平均值

*/