SQL Server：如何统计每位球员的不同对手数量

我有三张数据表：Player、Score和Match，球员会在不同的Match（比赛）中与其他球员相遇，球员在单场比赛中的分数记录在Score表内。需要统计每位球员的不同对手数量，预期结果示例如下：

Player1 - 10（例如Player2、3、4、5、6、7、8、9、10和11均与Player1有过对阵）
Player2 - 8（例如Player1、3、4、5、6、7、8、9均与Player2有过对阵）
Player3 - 9
Player4 - 10
Player5 - 9
Player6 - 10
Player7 - 11
Player8 - 11
Player9 - 10
Player10 - 9
Player11 - 10
Player12 - 9

数据表结构 #

CREATE TABLE [dbo].[Player](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Nom] [nvarchar](50) NOT NULL,
    [Prénom] [nvarchar](50) NOT NULL,
    [Telephone] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Match](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Nom] [nvarchar](50) NOT NULL,
    [DateFin] [datetime] NULL,
 CONSTRAINT [PK_Match] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Score](
    [MatchId] [bigint] NOT NULL,
    [PlayerId] [bigint] NOT NULL,
    [Points] [int] NULL,
 CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED 
(
    [MatchId] ASC,
    [PlayerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Score]  WITH CHECK ADD FOREIGN KEY([PlayerId])
REFERENCES [dbo].[Player] ([Id])
GO
ALTER TABLE [dbo].[Score]  WITH CHECK ADD FOREIGN KEY([MatchId])
REFERENCES [dbo].[Match] ([Id])
GO

当前单球员查询语句 #

目前只能单次查询单个球员的不同对手数量，SQL语句如下：

select count(distinct s.playerId) as nbDiffOpponents 
from score s
inner join player j on s.playerId = j.Id
where s.matchid IN (
    select matchid from score s 
    where s.playerid IN (1)
)
and playerId <> 1

测试数据 #

INSERT [dbo].[Player] ([Id], [Nom], [Prénom], [Telephone]) 
VALUES (1, N'Player1Lastname', N'Player1Firstname', N''),
    (2, N'Player2Lastname', N'Player2Firstname', N''),
    (3, N'Player3Lastname', N'Player3Firstname', N''),
    (5, N'Player5Lastname', N'Player5Firstname', N''),
    (6, N'Player6Lastname', N'Player6Firstname', N''),
    (7, N'Player7Lastname', N'Player7Firstname', N''),
    (8, N'Player8Lastname', N'Player8Firstname', N''),
    (11, N'Player11Lastname', N'Player11Firstname', N''),
    (12, N'Player12Lastname', N'Player12Firstname', N''),
    (13, N'Player13Lastname', N'Player13Firstname', N''),
    (14, N'Player14Lastname', N'Player14Firstname', N''),
    (15, N'Player15Lastname', N'Player15Firstname', N'')
GO
SET IDENTITY_INSERT [dbo].[Player] OFF
GO
SET IDENTITY_INSERT [dbo].[Match] ON 
GO
INSERT [dbo].[Match] ([Id], [Nom], [DateFin]) 
VALUES (81, N'Match 1', NULL),
(82, N'Match 2', NULL),
(83, N'Match 3', NULL),
(84, N'Match 4', NULL),
(85, N'Match 1', NULL),
(86, N'Match 2', NULL),
(87, N'Match 3', NULL),
(88, N'Match 4', NULL),
(89, N'Match 1', NULL),
(90, N'Match 2', NULL),
(91, N'Match 3', NULL),
(92, N'Match 4', NULL),
(93, N'Match 1', NULL),
(94, N'Match 2', NULL),
(95, N'Match 3', NULL),
(96, N'Match 4', NULL),
(97, N'Match 1', NULL),
(98, N'Match 2', NULL),
(99, N'Match 3', NULL),
(100, N'Match 4', NULL),
(101, N'Match 1', NULL),
(102, N'Match 2', NULL),
(103, N'Match 3', NULL),
(104, N'Match 4', NULL),
(105, N'Match 1', NULL),
(106, N'Match 2', NULL),
(107, N'Match 3', NULL),
(108, N'Match 4', NULL),
(109, N'Match 1', NULL),
(110, N'Match 2', NULL),
(111, N'Match 3', NULL),
(112, N'Match 4', NULL),
(113, N'Match 1', NULL),
(114, N'Match 2', NULL),
(115, N'Match 3', NULL),
(116, N'Match 4', NULL),
(117, N'Match 1', NULL),
(118, N'Match 2', NULL),
(119, N'Match 3', NULL),
(120, N'Match 4', NULL)
GO
SET IDENTITY_INSERT [dbo].[Match] OFF
GO
INSERT [dbo].[Score] ([MatchId], [PlayerId], [Points]) 
VALUES (81, 6, 0),
(81, 11, 0),
(81, 15, 0),
(82, 1, 0),
(82, 3, 0),
(82, 8, 0)
,(83, 5, 0)
,(83, 7, 0)
,(83,13, 0)
,(84, 2, 0)
,(84, 12, 0)
,(84, 14, 0)
,(85, 1, 0)
,(85, 2, 0)
,(85, 3, 0)
,(86, 5, 0)
,(86, 7, 0)
,(86, 15, 0)
,(87, 11, 0)
,(87, 12, 0)
,(87, 14, 0)
,(88, 6, 0)
,(88, 8, 0)
,(88, 13, 0)
,(89, 3, 0)
,(89, 5, 0)
,(89, 12, 0)
,(90, 8, 0)
,(90, 14, 0)
,(90, 15, 0)
,(91, 6, 0)
,(91, 7, 0)
,(91, 13, 0)
,(92, 1, 0)
,(92, 2, 0)
,(92, 11, 0)
,(93, 1, 0)
,(93, 2, 0)
,(93, 14, 0)
,(94, 6, 0)
,(94, 13, 0)
,(94, 15, 0)
,(95, 5, 0)
,(95, 7, 0)
,(95, 11, 0)
,(96, 3, 0)
,(96, 8, 0)
,(96, 12, 0)
,(97, 3, 0)
,(97, 8, 0)
,(97, 11, 0)
,(98, 6, 0)
,(98, 7, 0)
,(98, 13, 0)
,(99, 1, 0)
,(99, 2, 0)
,(99, 14, 0)
,(100, 5, 0)
,(100, 12, 0)
,(100, 15, 0)
,(101, 1, 0)
,(101, 6, 0)
,(101, 15, 0)
,(102, 2, 0)
,(102, 11, 0)
,(102, 12, 0)
,(103, 5, 0)
,(103, 8, 0)
,(103, 13, 0)
,(104, 3, 0)
,(104, 7, 0)
,(104, 14, 0)
,(105, 7, 0)
,(105, 13, 0)
,(105, 15, 0)
,(106, 5, 0)
,(106, 8, 0)
,(106, 14, 0)
,(107, 2, 0)
,(107, 3, 0)
,(107, 11, 0)
,(108, 1, 0)
,(108, 6, 0)
,(108, 12, 0)
,(109, 6, 0)
,(109, 11, 0)
,(109, 13, 0)
,(110, 1, 0)
,(110, 3, 0)
,(110, 7, 0)
,(111, 5, 0)
,(111, 14, 0)
,(111, 15, 0)
,(112, 2, 0)
,(112, 8, 0)
,(112, 12, 0)
,(113, 1, 0)
,(113, 7, 0)
,(113, 11, 0)
,(114, 13, 0)
,(114, 14, 0)
,(114, 15, 0)
,(115, 3, 0)
,(115, 6, 0)
,(115, 12, 0)
,(116, 2, 0)
,(116, 5, 0)
,(116, 8, 0)
,(117, 1, 0)
,(117, 12, 0)
,(117, 13, 0)
,(118, 2, 0)
,(118, 7, 0)
,(118, 8, 0)
,(119, 5, 0)
,(119, 6, 0)
,(119, 15, 0)
,(120, 3, 0)
,(120, 11, 0)
,(120, 14, 0)
GO

解决方案 #

可以通过自连接Score表，关联同一比赛下的其他球员，再分组统计每个球员的不同对手数量，最后关联Player表获取球员名称：

SELECT 
    CONCAT(p.[Prénom], ' ', p.[Nom]) AS PlayerName,
    COUNT(DISTINCT s2.PlayerId) AS nbDiffOpponents
FROM 
    Player p
LEFT JOIN 
    Score s1 ON p.Id = s1.PlayerId
LEFT JOIN 
    Score s2 ON s1.MatchId = s2.MatchId AND s2.PlayerId <> p.Id
GROUP BY 
    p.Id, p.[Prénom], p.[Nom]
ORDER BY 
    PlayerName;

如果需要和示例格式一致（只显示PlayerX），可以调整PlayerName的拼接逻辑：

SELECT 
    REPLACE(p.[Prénom], 'Firstname', '') AS PlayerName,
    COUNT(DISTINCT s2.PlayerId) AS nbDiffOpponents
FROM 
    Player p
LEFT JOIN 
    Score s1 ON p.Id = s1.PlayerId
LEFT JOIN 
    Score s2 ON s1.MatchId = s2.MatchId AND s2.PlayerId <> p.Id
GROUP BY 
    p.Id, p.[Prénom]
ORDER BY 
    PlayerName;

说明 #

• 使用LEFT JOIN确保没有参加过任何比赛的球员也会被统计（对手数为0）
• 自连接Score表s1和s2，关联同一比赛中除当前球员外的其他球员
• COUNT(DISTINCT s2.PlayerId)确保同一对手多次对阵只统计一次

内容的提问来源于stack exchange，提问作者madhatterx