计算共享相同 FK ID 的重复值

Question

我正在尝试计算一列中有多少个共享相同 FK ID 的重复项。每个Shared_FK_id只能有一个类型。

table:
Shared_FK_ID : bigint
type: varchar(50)

示例数据：

831 Ford
831 Fiat
831 Honda
831 Honda
831 Dodge
831 Volvo
831 Volvo
831 Opel

Answer 1

应该适用于大多数sql DBMS。

  select Shared_FK_ID, count(*) nmbr_of_dbls
    from (
       select Shared_FK_ID, type , count(*)
       group by Shared_FK_ID, type
       having count(*) > 1
    ) t
  group by Shared_FK_ID

Answer 2

要详细说明Serg的答案，并使其特定于SQL服务器，您可以根据所需的输出执行以下任一操作。

1( 包含重复项的类型数量：

select Shared_FK_ID, count(*) nmbr_of_dbls
from (
   select Shared_FK_ID, [type] , count(*) nmbr
   from data
   group by Shared_FK_ID, [type]
   having count(*) > 1
) t
group by Shared_FK_ID

2( 整个 FK ID 中重复行的总数：

select Shared_FK_ID, sum(nmbr) nmbr_of_dbls
from (
   select Shared_FK_ID, [type] , count(*) nmbr
   from data
   group by Shared_FK_ID, [type]
   having count(*) > 1
) t
group by Shared_FK_ID

现场演示：https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=c8e1ed9607430fde16157367d59afc90