返回 登录
0

面试中遇到的SQL语句(二)

阅读876

题1:有一学生成绩表
图片描述

DROP TABLE IF EXISTS `gao_info`;
CREATE TABLE `gao_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `course` varchar(100) DEFAULT NULL,
  `scouse` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of gao_info
-- ----------------------------
INSERT INTO `gao_info` VALUES ('1', '20140608001', '语文', '115');
INSERT INTO `gao_info` VALUES ('2', '20140608001', '数学', '120');
INSERT INTO `gao_info` VALUES ('3', '20140608001', '英语', '110');
INSERT INTO `gao_info` VALUES ('4', '20140608001', '政治', '130');
INSERT INTO `gao_info` VALUES ('5', '20140608002', '语文', '140');
INSERT INTO `gao_info` VALUES ('6', '20140608002', '数学', '120');
INSERT INTO `gao_info` VALUES ('7', '20140608002', '英语', '120');
INSERT INTO `gao_info` VALUES ('8', '20140608001', '化学', '135');
INSERT INTO `gao_info` VALUES ('9', '20140608001', '物理', '130');
INSERT INTO `gao_info` VALUES ('10', '20140608002', '物理', '120');
INSERT INTO `gao_info` VALUES ('11', '20140608003', '语文', '130');
INSERT INTO `gao_info` VALUES ('12', '20140608003', '数学', '110');
INSERT INTO `gao_info` VALUES ('13', '20140608003', '英语', '120');
INSERT INTO `gao_info` VALUES ('14', '20140608003', '政治', '135');
INSERT INTO `gao_info` VALUES ('15', '20140608003', '化学', '145');
INSERT INTO `gao_info` VALUES ('16', '20140608004', '物理', '135');
INSERT INTO `gao_info` VALUES ('17', '20140608004', '语文', '125');
INSERT INTO `gao_info` VALUES ('18', '20140608004', '数学', '145');

查询出总成绩高于600分的学生准考证。

select no,SUM(scouse) s from gao_info GROUP BY no HAVING s>600

图片描述

评论