一个sql脚本

gistfile1.sql

/*
Navicat MySQL Data Transfer

Source Server         : mysql-db
Source Server Version : 50172
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50172
File Encoding         : 65001

Date: 2014-03-14 20:40:46
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gameName` varchar(100) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', 'helicopter', '521', '74', '2014-03-14 00:00:00');
INSERT INTO `grade` VALUES ('2', 'helicopter', '522', '33', '2014-03-14 00:00:00');
INSERT INTO `grade` VALUES ('3', 'helicopter', '522', '34', '2014-03-14 20:28:47');
INSERT INTO `grade` VALUES ('4', 'helicopter', '523', '54', '2014-03-14 20:28:47');
INSERT INTO `grade` VALUES ('5', 'helicopter', '521', '30', '2014-03-14 20:28:47');
INSERT INTO `grade` VALUES ('6', 'helicopter', '521', '14', '2014-03-14 20:28:47');
INSERT INTO `grade` VALUES ('7', 'helicopter', '21', '188', '2014-03-14 20:29:22');
INSERT INTO `grade` VALUES ('8', 'aircraft', '22', '200', '2014-03-14 20:30:36');

--查询
select a.id,a.gameName,a.userid,a.score,a.timestamp from grade a inner join (select userid,max(score) maxScore from grade group by userid) b on a.userid = b.userid and a.score = b.maxScore order by a.score desc;


姓名，课目，分数
张三，数学，81
张三，语文，67
李四，数学，92
李四，语文，78
王五，数学，90
王五，语文，88
王五，英语，82

2014/4/28 22:43:43
你是要把它转置嘛

2014/4/28 22:44:21
题目：只用一条sql查出每门课都大于80的姓名和每门课的平均分数

select name,count(course) course, avg(score) avg from tbscore group by name having course=3;


--求三门课的成绩都大于80的人
select name,count(course) course, avg(score) avg 
from tbscore 
where score>80 
group by name having course=3;



--求这个人每门课的平均分
select course, sum(score),sum(score)/count(course) avg from tbscore group by course;