DELETE FROM teacher WHERE ID = '10006'; SELECT * FROM teacher哈哈
在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;
这样也可以的: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;
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 * from teacher t -> where 2>(select count(*) from teacher where REGNAME=t.REGNAME and ID<t.ID);希望 ACMAIN_CHM 解释一句这个是什么意思啊 select count(*) from teacher where REGNAME=t.REGNAME and ID<t.ID 相关子查询吗 REGNAME=t.REGNAME and ID<t.ID 尤其是ID<t.ID是什么意思 ??先执行的前边的select 还是后边的select 啊??如果先执行的前边 。把前边记录的id 传给后边,当我检索到10006的时候 ------------------------------- 另外我的方法按姓名分组, 每一组内按id排序 然后取每组的前两条记录。也应该可以吧??
SELECT A.NAME,COUNT(*) FROM `teacher` A LEFT JOIN `teacher` B ON A.`NAME`=B.`NAME` AND A.`ID`>=B.`ID` 看看结果
SELECT * FROM `teacher` A WHERE 2>=(SELECT COUNT(*) FROM `teacher`
WHERE A.`NAME`=`NAME` AND A.`ID`>=`ID`)
SELECT * FROM teacher哈哈
查询所有有重复字段的所有记录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;
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;
[征集]分组取最大N条记录方法征集,及散分....
+-------+---------+--------+------+
| 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>
-> where 2>(select count(*) from teacher where REGNAME=t.REGNAME and ID<t.ID);希望
ACMAIN_CHM
解释一句这个是什么意思啊
select count(*) from teacher where REGNAME=t.REGNAME and ID<t.ID
相关子查询吗 REGNAME=t.REGNAME and ID<t.ID
尤其是ID<t.ID是什么意思 ??先执行的前边的select 还是后边的select 啊??如果先执行的前边 。把前边记录的id 传给后边,当我检索到10006的时候
-------------------------------
另外我的方法按姓名分组, 每一组内按id排序 然后取每组的前两条记录。也应该可以吧??
LEFT JOIN `teacher` B
ON A.`NAME`=B.`NAME` AND A.`ID`>=B.`ID`
看看结果