select * from (select count(字段名) as num from 表 group by 字段名) order by num desc
检查重复值: select code from tablename group by code having count(code)>1
用代码循环检查不太合适。 如果只想知道有没有而不在乎哪些值重复的话: select top 1 count(code),* from table group by code order by count(code) desc
select * from 表 where 字段 in (select 字段 from (select 字段,count(字段) as num from 表 group by 字段) where num >1)
看来不少人对SQL不精, 还是要加强学习啊
SELECT code, SUM(m) AS m FROM (SELECT code, 1 AS m FROM Biao) A GROUP BY code HAVING SUM(m) > 1执行这句sql 就知道有没有重复的 重复了几个!
全部显示出来用: select code,count(code) from biao group by code having count(code)>1
标准答案 SELECT DISTINCT code.i_id, code.name FROM code WHERE (((code.i_id) In (SELECT [i_id] FROM [code] As Tmp GROUP BY [i_id] HAVING Count(*)>1 ))) ORDER BY code.i_id;
select code from biao
比较这两个记录集的recordcount就可以了
如果和select 字段名 from 表得到的条数一样多,就没有重复记录
在open记录集时要用adOpenStatic方式否则如果用adOpenDynamic
返回的是-1
select code from tablename group by code having count(code)>1
如果只想知道有没有而不在乎哪些值重复的话:
select top 1 count(code),* from table group by code order by count(code) desc
还是要加强学习啊
FROM (SELECT code, 1 AS m
FROM Biao) A
GROUP BY code
HAVING SUM(m) > 1执行这句sql 就知道有没有重复的 重复了几个!
select code,count(code) from biao group by code having count(code)>1
SELECT DISTINCT code.i_id, code.name
FROM code
WHERE (((code.i_id) In (SELECT [i_id] FROM [code] As Tmp GROUP BY [i_id] HAVING Count(*)>1 )))
ORDER BY code.i_id;
另开帖子了,但是这里的回答等我的问题解决了
我会结帐的另开的帖子在:http://expert.csdn.net/Expert/topic/1882/1882400.xml?temp=.2307093