SELECT COUNT(*) FROM TB GROUP BY ID HAVING COUNT(ID)>=2
SELECT ID,COUNT(*) FROM TB GROUP BY ID HAVING COUNT(*)>=2
比如取a,b字段组合重复的条数select a,b,count(1) as cnt from tb group by a,b having count(1)>1
排除重复记录select * from TB A where not exists(select top 1 0 from TB where ID=A.ID)
如果是比如表tb中有id重复的记录,建议使用这样的语句:select * from tb where id in (select id from tb group by id having count(*)>1)使用到了一个子句,可以马上直接把表的重复记录给取出来
SELECT ID, COUNT(1) FROM TB GROUP BY ID HAVING COUNT(ID)>=2
having count(col) > 1
SELECT COUNT(*) FROM TB GROUP BY ID HAVING COUNT(ID)>1
--来自联机丛书 --以下示例使用简单 HAVING 子句从 SalesOrderDetail 表中检索超过 $100000.00 的每个 SalesOrderID 的总计。 USE AdventureWorks ; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail sod GROUP BY SalesOrderID HAVING SUM(LineTotal) > 100000.00 ORDER BY SalesOrderID ;
from tb
group by a,b
having count(1)>1
where id in
(select id from tb group by id having count(*)>1)使用到了一个子句,可以马上直接把表的重复记录给取出来
having count(col) > 1
SELECT COUNT(*) FROM TB GROUP BY ID HAVING COUNT(ID)>1
--以下示例使用简单 HAVING 子句从 SalesOrderDetail 表中检索超过 $100000.00 的每个 SalesOrderID 的总计。
USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;