(1)
-- Table structure for stuscore
DROP TABLE IF EXISTS stuscore
;
CREATE TABLE stuscore
(
name
varchar(20) DEFAULT NULL,
subject
varchar(20) DEFAULT NULL,
score
int(9) DEFAULT NULL,
stuid
int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of stuscore
INSERT INTO stuscore
VALUES ('张三', '语文', '81', '1');
INSERT INTO stuscore
VALUES ('张三', '数学', '78', '1');
INSERT INTO stuscore
VALUES ('张三', '物理', '77', '1');
INSERT INTO stuscore
VALUES ('李四', '语文', '80', '2');
INSERT INTO stuscore
VALUES ('李四', '数学', '91', '2');
INSERT INTO stuscore
VALUES ('李四', '物理', '79', '2');
(2)
select t.name
,sum(t.score) from stuscore t GROUP BY t.name
(3)
select t.name
,t.score,t.subject
from stuscore t ORDER BY t.score DESC LIMIT 0,2;
(4)
select t.name
,sum(t.score)/COUNT(t.score) as temp_score from stuscore t GROUP BY t.name ORDER BY temp_score desc;