这样的一个需求。一table 中有一字段 codeNumber
选出 codeNumber 中出现最多 重复的记录 id codeNumber
1 123
2 32
3 123
4 32
5 12
6 123
1:把 id为1,3,6的选出来!2: 求 codeNumber 最多重复的 个数count(*)
选出 codeNumber 中出现最多 重复的记录 id codeNumber
1 123
2 32
3 123
4 32
5 12
6 123
1:把 id为1,3,6的选出来!2: 求 codeNumber 最多重复的 个数count(*)
SELECT A.* FROM TT A
INNER JOIN
(SELECT codeNumber FROM TT GROUP BY codeNumber HAVING COUNT(*)>=2) B
ON A.codeNumber=B.codeNumber2
SELECT * FROM (
SELECT codeNumber,COUNT(*) AS GS FROM TT GROUP BY codeNumber) A
ORDER BY GS DESC LIMIT 1
SELECT A.* FROM TT A
INNER JOIN
(SELECT codeNumber FROM TT GROUP BY codeNumber HAVING COUNT(*)>=2) B
ON A.codeNumber=B.codeNumber 不对啊。
我是需要最多的。。你把全部的给弄出来了!
第二句对的!
SELECT codeNumber,count(*) as gs FROM TT GROUP BY codeNumber
order by count(*) desc limit 1) a
inner join tt b
on a.codeNumber=b.codeNumber
where codeNumber=(select top 1 codeNumber,count(*) from table1 group by codeNumber order by 2 desc )
select top 1 codeNumber,count(*) from table1 group by codeNumber order by 2 desc
select * from table1 where codeNumber in(
select codeNumber from(
select codeNumber,count(1) from table1 group by codeNumber order by count(2) desc limit 1)t)
/**
1 123
3 123
6 123
**/--2: 求 codeNumber 最多重复的 个数count(*)
select codeNumber,count(1)
from table1
group by codeNumber
order by count(2) desc limit 1
/**
123 3
**/