Get a player's different opponents number-CodePudding

I have 3 tables : Player, Score and Match where a player can meet others in different matchs, the score for a player in a match is set in Score table.
I'd like to get each player's different opponents number like :

Player1 - 10 (like Player2, 3, 4, 5, 6, 7, 8, 9, 10 and 11 are going to meet Player1)
Player2 - 8 (like Player1, 3, 4, 5, 6, 7, 8, 9 are going to meet Player2)
Player3 - 9
Player4 - 10
Player5 - 9
Player6 - 10
Player7 - 11
Player8 - 11
Player9 - 10
Player10 - 9
Player11 - 10
Player12 - 9

Here is the schema :

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

Currently, I can get one player at a time with :

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

Here is some data :

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

CodePudding user response：

This should do.

Select 
    p.ID,p.Prénom,p.Nom,p.Telephone,COUNT(DISTINCT so.PlayerId) count
from 
    Player p
    JOIN Score s ON p.Id = s.PlayerId
    JOIN Score so On so.MatchId = s.MatchId and so.PlayerId != p.Id
Group by p.ID,p.Prénom,p.Nom,p.Telephone

To explain this query, first you need to select all of the players. After that join that to score table which contains all pairs of match-player that played that match. Now you got a list of players and what matches they participated in. Next, you need to connect those to all other players that played in same match. You do that by joining your query again to score table based on match ID, but you want to get just other players that played in that match. thats why you add so.PlayerId != p.Id

To finish, you need to count distinct playerId-s and use GROUP BY to group your data by rows you need.

CodePudding user response：

Provided that exactly two different players play in the match, the number of matches of a player is

select [PlayerId], count(*) n
from [Score]
group by [PlayerId]

select p.*, n
from (
    select [PlayerId], count(*) n
    from [Score]
    group by [PlayerId]
) cnt
join [Player] p on p.[Id] = cnt.[PlayerId]