|
/* 成绩表 */
CREATE TABLE `tld_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score` int(11) DEFAULT NULL,
`subject` varchar(32) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/* 学生表 */
CREATE TABLE `tld_user` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(1, 78, '语文', 1);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(2, 78, '数学', 1);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(3, 78, '英语', 1);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(4, 50, '数学', 2);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(5, 50, '英语', 2);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(6, 50, '语文', 2);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(7, 80, '数学', 3);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(8, 80, '英语', 3);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(9, 80, '语文', 3);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(10, 99, '数学', 4);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(11, 99, '英语', 4);
INSERT INTO tld_score
(`id`, `score`, `subject`, `userid`)
VALUES
(12, 99, '语文', 4);
INSERT INTO tld_user
(`id`, `name`)
VALUES
(1, '王');
INSERT INTO tld_user
(`id`, `name`)
VALUES
(2, '张');
INSERT INTO tld_user
(`id`, `name`)
VALUES
(3, '李');
INSERT INTO tld_user
(`id`, `name`)
VALUES
(4, '贺');
select a.name , b.score
from tld_user a ,
(select sum(score) score,userid from tld_score group by userid ) b
where a.id = b.userid
|
