mysql重复记录查询
1.一个字段重复,如果记录多于2天,最多只显示2条
2.sql语句
-
SQL code
CREATE TABLE `teacher` ( `ID` bigint(20) NOT NULL COMMENT 'ID', `REGNAME` char(200) DEFAULT NULL COMMENT '师教编号', `NAME` char(20) NOT NULL COMMENT '教师名称', `TYPE` int(1) DEFAULT '0' COMMENT '师教类型(0:内聘,1:外聘)', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `teacher` VALUES ('10001', 'allen', 'allen', '0'); INSERT INTO `teacher` VALUES ('10002', 'ruby', 'ruby', '0'); INSERT INTO `teacher` VALUES ('10003', 'sharon', 'sharon', '1'); INSERT INTO `teacher` VALUES ('10004', 'alpha', 'alpha', '0'); INSERT INTO `teacher` VALUES ('10005', 'alpha', 'alpha', '0'); INSERT INTO `teacher` VALUES ('10006', 'alpha', 'alpha', '0');
1.数据库记录
2.需要的结果
SELECT * FROM `teacher`;
SELECT * FROM `teacher` A WHERE 2>=(SELECT COUNT(*) FROM `teacher`
WHERE A.`NAME`=`NAME` AND A.`ID`>=`ID`
)
在Oracle中可以这样
查询所有有重复字段的所有记录
select * from teacher A where (select count(*) from teacher where A.NAME=NAME )>=2;
而后查询前两条记录
SELECT * FROM (select * from teacher A where (select count(*) from teacher where A.NAME=NAME )>=2) E1 WHERE
(SELECT COUNT(*) FROM (select * from teacher A where (select count(*) from teacher where A.NAME=NAME )>=2) E2 WHERE E1.NAME=E2.NAME AND E2.ID<E1.ID )<2 order by NAME,ID;
这样也可以的:
-
SQL code
SELECT a.id,a.regname,a.name,a.type FROM teacher a LEFT JOIN teacher b ON b.name=a.name AND b.id>a.id GROUP BY a.id,a.regname,a.name,a.type HAVING COUNT(b.id) < 2;
-
SQL code
mysql> select * from teacher; +-------+---------+--------+------+ | ID | REGNAME | NAME | TYPE | +-------+---------+--------+------+ | 10001 | allen | allen | 0 | | 10002 | ruby | ruby | 0 | | 10003 | sharon | sharon | 1 | | 10004 | alpha | alpha | 0 | | 10005 | alpha | alpha | 0 | | 10006 | alpha | alpha | 0 | +-------+---------+--------+------+ 6 rows in set (0.00 sec) mysql> select * from teacher t -> where 2>(select count(*) from teacher where REGNAME=t.REGNAME and ID<t.ID); +-------+---------+--------+------+ | ID | REGNAME | NAME | TYPE | +-------+---------+--------+------+ | 10001 | allen | allen | 0 | | 10002 | ruby | ruby | 0 | | 10003 | sharon | sharon | 1 | | 10004 | alpha | alpha | 0 | | 10005 | alpha | alpha | 0 | +-------+---------+--------+------+ 5 rows in set (0.02 sec) mysql>
SELECT A.NAME,COUNT(*) FROM `teacher` A
LEFT JOIN `teacher` B
ON A.`NAME`=B.`NAME` AND A.`ID`>=B.`ID`
看看结果