还是先把数据导入到数据库

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50540
Source Host           : localhost:3306
Source Database       : test

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

Date: 2017-08-03 16:51:55
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `students`
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cid` int(11) DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`age` tinyint(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1', '7', '王小明', '18');
INSERT INTO `students` VALUES ('2', '8', '张小武', '19');
INSERT INTO `students` VALUES ('3', '19', '李凡达', '20');
INSERT INTO `students` VALUES ('4', '19', '李凡达', '20');
INSERT INTO `students` VALUES ('5', '20', '贤一', '20');
INSERT INTO `students` VALUES ('6', '21', '贤二', '20');
INSERT INTO `students` VALUES ('7', '21', '贤二', '20');
INSERT INTO `students` VALUES ('8', '21', '贤二', '20');

sql语句为

SELECT
*, COUNT(cid) AS num
FROM
students
GROUP BY
cid
HAVING
num > 1

运行结果我就不上图了,大家运行看看结果。

解读:
查询重复数据,首先的分组,此例是查询cid重复值,用GROUP BY cid,然后用HAVING限制重命名的字段num(HAVING num > 1)。

如未注明,均为原创,转载请注明来自Peak Xin's Blog

No Comments »