一个用户记录表,有重复的记录,搜索比某条记录数少的第一条记录。
比如下面,user1有3条记录,搜索比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
比如下面,user1有3条记录,搜索比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
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
+-------+-----------+
| 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>
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-1orsELECT 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 )
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;
`iUKey`是主键,无法插入;
2、去掉6
insert into user values(null,'user1');
在楼主的测试数据中,没有COUNT(*)=3的记录,故为空集