如何获取重复计数SQL

我有这样的桌子

declare @Product table  (ProductId int)
insert into @Product select 1155
insert into @Product select 1155  
insert into @Product select 1159 
insert into @Product select 1154  
insert into @Product select 1154  
insert into @Product select 1152 
insert into @Product select 1151 

我需要将其重复计数分配给变量

DECLARE @duplicateCount INT;

我写了一个查询来检查重复项

SELECT count(c.ProductId)
    FROM @Product c
GROUP BY c.ProductId
  HAVING COUNT(*) > 1

上面的查询返回以下输出

+------------------+
| (No column name) |
+------------------+
| 2                |
+------------------+
| 2                |
+------------------+

Instead of that, I need to set duplicate records count to @duplicateCount, as the above example count should be 4

评论
hipsum
hipsum

您可以使用子查询:

SELECT @duplicateCount = SUM(cnt)
FROM (SELECT count(c.ProductId) as cnt
      FROM @Product c
      GROUP BY c.ProductId
      HAVING COUNT(*) > 1
     ) p
点赞
评论