sql

 

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `subject` varchar(50) default NULL,
  `score` int(11) default NULL,
  `class` varchar(20) default NULL,
  `number` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

INSERT INTO `score` VALUES (1,'eric','math',90,'1','1'),(2,'ken','english',80,'1','2'),(3,'ben','chinese',70,'2','4'),(4,'tim','chinese',67,'2','3'),(5,'tim','english',87,'2','3'),(6,'tim','math',97,'2','3'),(7,'ben','math',63,'2','4'),(8,'ben','english',83,'2','4'),(9,'ken','chinese',73,'1','2'),(10,'ken','math',93,'1','2'),(11,'eric','english',83,'1','1'),(12,'eric','chinese',73,'1','1');

 

 

性能： 

怎樣用如下關鍵字:

compute 

cube

union           合併兩條sql的結果

分数统计系统sql

http://www.cnblogs.com/tenghoo/archive/2007/06/11/779240.html

 

各科的  最高分 和 次高分

select subject,name,score from score s1 where (select count(1) from score s2 where s1.subject=s2.subject and s1.name!=s2.name and s1.score>=s2.score ) >1  order by subject,score desc;

 

各科的最高分

select subject,name,score from score where (subject,score) in (select subject,max(score) from score group by subject);
+---------+------+-------+
| subject | name | score |
+---------+------+-------+
| english | tim  |    87 |
| math    | tim  |    97 |
| chinese | ken  |    73 |
| chinese | eric |    73 |
+---------+------+-------+
4 rows in set (0.01 sec)

 

個人最高分及科目

mysql> select subject,name,score from score where (name,score) in (select name,max(score) from score group by name);
+---------+------+-------+
| subject | name | score |
+---------+------+-------+
| math    | eric |    90 |
| math    | tim  |    97 |
| math    | ben  |    83 |
| english | ben  |    83 |
| math    | ken  |    93 |
+---------+------+-------+
5 rows in set (0.02 sec)

 

 select name,LEFT(group_concat(subject order by score desc),POSITION(',' IN group_concat(subject order by score desc))-1) as kemu,max(score) from score group by name;
+------+---------+------------+
| name | kemu    | max(score) |
+------+---------+------------+
| ben  | english |         83 |
| eric | math    |         90 |
| ken  | math    |         93 |
| tim  | math    |         97 |
+------+---------+------------+
4 rows in set (0.00 sec)

 

 

分組後對每個分組進行排序

mysql> select name,subject,score from score group by name,subject,score desc;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| ben  | chinese |    70 |
| ben  | english |    83 |
| ben  | math    |    83 |
| eric | chinese |    73 |
| eric | english |    83 |
| eric | math    |    90 |
| ken  | chinese |    73 |
| ken  | english |    80 |
| ken  | math    |    93 |
| tim  | chinese |    67 |
| tim  | english |    87 |
| tim  | math    |    97 |
+------+---------+-------+
12 rows in set (0.01 sec)

 

 

格式化统计

mysql> SELECT name, SUM( IF (subject ="chinese", score, 0) ) AS chinese, SUM(IF (subject ="english", score, 0) ) AS english, SUM( IF (subject="math", score, 0) ) AS math, SUM( score ) AS TotalSore FROM score GROUP BY name;

http://www.cnblogs.com/greatqn/archive/2006/11/03/548798.html

 

选择特定序号的纪录

select * from account_info limit 3,2       重第3條開始(不包含第3條),向後數的2條紀錄

 

 

基础sql    http://youxue.blog.51cto.com/231393/43571

 

学生成绩统计sql    http://www.cnblogs.com/tenghoo/archive/2007/06/11/779240.html