没有环境 不知道对不对啊 。select id , name from ( select a.id , a.name , cnt = (select count(*) from tableName where name = a.name and id < a.id) from tableName a ) where cnt < 2不对说 ,再改动 。
insert into testOn select 1 , '01' union select 2 , '02' union select 3 , '03' union select 4 , '01' union select 5 , '02' union select 6 , '03' union select 7 , '01' union select 8 , '02' union select 9 , '04' union select 10 , '05'select * from testOn /* -------------------------------- tid tname 1 01 2 02 3 03 4 01 5 02 6 03 7 01 8 02 9 04 10 05 --------------------------------- */ select c.tid , c.tname from ( select a.tid , a.tname , cnt = (select count(*) from testOn where tname = a.tname and tid < a.tid) from testOn a ) c where c.cnt < 2/* -------------------------------- tid tname 1 01 2 02 3 03 4 01 5 02 6 03 9 04 10 05 -------------------------------- */
select c.tid , c.tname from ( select a.tid , a.tname , countname = (select count(*) from testOn where tname = a.tname and tid < a.tid) from testOn a ) c where c.countname < 3
第二个数据源用 FOR循环遍历你的数据源。1从游标的第一个用户NAME2开始到FOR循环中遍历,直到找到两个则退出FOR循环。3取得游标的下一个用户name,返回2。OK?
(
select a.id , a.name , cnt = (select count(*) from tableName where name = a.name and id < a.id)
from tableName a
)
where cnt < 2不对说 ,再改动 。
insert into testOn
select 1 , '01'
union select 2 , '02'
union select 3 , '03'
union select 4 , '01'
union select 5 , '02'
union select 6 , '03'
union select 7 , '01'
union select 8 , '02'
union select 9 , '04'
union select 10 , '05'select * from testOn
/*
--------------------------------
tid tname
1 01
2 02
3 03
4 01
5 02
6 03
7 01
8 02
9 04
10 05
---------------------------------
*/
select c.tid , c.tname from
(
select a.tid , a.tname , cnt = (select count(*) from testOn where tname = a.tname and tid < a.tid)
from testOn a
) c
where c.cnt < 2/*
--------------------------------
tid tname
1 01
2 02
3 03
4 01
5 02
6 03
9 04
10 05
--------------------------------
*/
(
select a.tid , a.tname , countname = (select count(*) from testOn where tname = a.tname and tid < a.tid)
from testOn a
) c
where c.countname < 3
where c.countname < 2 是对的
不是 < 3 !!!!!