求一个sql，搜索比某条记录数少的第一条记录。

[BILLSSJONE]

一个用户记录表，有重复的记录，搜索比某条记录数少的第一条记录。
比如下面，user1有３条记录，搜索比user1记录数少的一个用户，有user3和user2，但只取最多的一条记录，就是只取user3，请问sql怎么写？谢谢！！

CREATE TABLE `user` (
`iUKey` int(8) NOT NULL auto_increment,
`cUserName` varchar(30) NOT NULL default '',
PRIMARY KEY (`iUKey`)
) ENGINE=MyISAM;
INSERT INTO `user` VALUES (1, 'user1');
INSERT INTO `user` VALUES (2, 'user1');
INSERT INTO `user` VALUES (3, 'user1');
INSERT INTO `user` VALUES (4, 'user3');
INSERT INTO `user` VALUES (5, 'user3');
INSERT INTO `user` VALUES (6, 'user6');
INSERT INTO `user` VALUES (7, 'user6');
INSERT INTO `user` VALUES (8, 'user6');
INSERT INTO `user` VALUES (9, 'user6');
INSERT INTO `user` VALUES (10, 'user2');

结果：user3

[BILLSSJONE]

我想出来了，谢谢
select distinct A.user from user A where (select count(iUKey) as getcount from user where user=A.user)<=3 order by (select count(iUKey) as getcount from user where user=A.user) desc limit 1

[ACMAIN_CHM]

mysql> select * from user;
+-------+-----------+
| iUKey | cUserName |
+-------+-----------+
|     1 | user1     |
|     2 | user1     |
|     3 | user1     |
|     4 | user3     |
|     5 | user3     |
|     6 | user6     |
|     7 | user6     |
|     8 | user6     |
|     9 | user6     |
|    10 | user2     |
+-------+-----------+
10 rows in set (0.09 sec)

mysql> select cUserName
    -> from user
    -> group by cUserName
    -> having count(*)=(select count(*)-1 from user where cUserName='user1')
    -> limit 1;
+-----------+
| cUserName |
+-----------+
| user3     |
+-----------+
1 row in set (0.00 sec)

mysql>

[WWWWA]

自己解决更好

SELECT a1.* FROM (
SELECT a.cUserName,COUNT(*) AS ma FROM `user` a GROUP BY a.cUserName ) a1
INNER JOIN
(SELECT a.cUserName,COUNT(*) AS mb FROM `user` a WHERE a.cUserName='user1' GROUP BY a.cUserName ) a2
ON a1.ma=a2.mb-1

or

sELECT a.cUserName,COUNT(*) FROM `user` a GROUP BY a.cUserName
HAVING COUNT(*)=(SELECT COUNT(*)-1 FROM `user` a WHERE a.cUserName='user1' GROUP BY a.cUserName )

[sjzzy]

如果 ‘user1’的记录再加多一条

insert into user values(6,'user1');

楼上的查询就是Empty set了
这条更通用：

select cusername 
from user
group by cusername
having count(*) < 
  (select count(*) 
   from user
   where cusername='user1'
   )
order by count(*) desc
limit 1;

[WWWWA]

[Quote=引用 4 楼 sjzzy 的回复:]
如果 ‘user1’的记录再加多一条
SQL code
insert into user values(6,'user1');

楼上的查询就是Empty set了
这条更通用：
SQL code
select cusername
from user
group by cusername
having count(*) <
(select count(*)
……
[/Quote]
1、insert into user values(6,'user1');
`iUKey`是主键，无法插入；
2、去掉6
insert into user values(null,'user1');
在楼主的测试数据中，没有COUNT(*)=3的记录，故为空集