删除除了id号不同,其他都相同的学生冗余信息

id号  学号   姓名课程编号课程名称分数
1        2005001  张三 0001     数学    69
2        2005002  李四 0001     数学    89
3        2005001  张三 0001     数学    69

 
delete from score where id not in (select bid from (select min(id) as bid from score group by name,kecheng,fenshu)as b)

/*
Navicat MySQL Data Transfer


Source Server         : connectionone
Source Server Version : 50625
Source Host           : localhost:3306
Source Database       : db_stu_course


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


Date: 2017-11-08 17:35:48
*/


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `kecheng` varchar(20) DEFAULT NULL,
  `fenshu` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', 'wang', 'math', '95');
INSERT INTO `score` VALUES ('2', 'tom', 'java', '75');
INSERT INTO `score` VALUES ('3', 'wang', 'math', '95');
INSERT INTO `score` VALUES ('4', 'tom', 'java', '75');

 
delete from score where id not in (select bid from (select min(id) as bid from score group by name,kecheng,fenshu)as b)