壁球联赛结果-SQL查询

admin

壁球联赛结果-SQL查询

sql

我最近接手了我在壁球俱乐部打的内部联赛

我希望将这些内容放到网上，以便会员查看和添加所需的结果

联赛结构遵循以下格式，共有6个联赛

联赛1

|        | John | Mark | Peter | Martin | Paul |
|:------:|:----:|:----:|:-----:|:------:|:----:|
| John   | NULL |   3  |   0   |    1   |   2  |
| Mark   |   0  | NULL |   1   |    3   |   0  |
| Peter  |   3  |   3  |  NULL |    1   |   3  |
| Martin |   3  |   1  |   3   |  NULL  |   2  |
| Paul   |   3  |   3  |   0   |    3   | NULL |

联赛2

等

我将表结构设计为

CREATE TABLE [dbo].[Results](
    [ResultId] [int] IDENTITY(1,1) NOT NULL,
    [LeagueId] [int] NOT NULL,
    [Player1Id] [int] NOT NULL,
    [Player2Id] [int] NOT NULL,
    [Player1Result] [int] NULL,
    [Player2Result] [int] NULL)

CREATE TABLE [dbo].[Players](
    [PlayerId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [FirstName] [nvarchar](150) NULL,
    [LastName] [nvarchar](150) NULL)

CREATE TABLE [dbo].[Leagues](
    [LeagueId] [int] IDENTITY(1,1) NOT NULL,
    [LeagueName] [nvarchar](50) NULL)

我正在尝试编写一个查询，该查询在一个查询中为我提供每个细分的输出，而不是几个查询中的输出，任何人都可以对查询进行帮助吗？

我到目前为止所拥有的是

select p.FirstName, p1.player2result, p2.player2result, p3.player2result, p4.player2result
from
    (select player2Result from Results p1 where p.playerId = p1.Player2Id
    union
    select player2Result from Results p2 where p.playerId = p2.Player2Id
    union
    select player2Result from Results p3 where p.playerId = p3.Player2Id
    union
    select player2Result from Results p4 where p.playerId = p4.Player2Id) as opResult
LEFT JOIN Players p on opResult.Player2Result = p.PlayerId
GROUP BY p.FirstName, p1.player2result, p2.player2result, p3.player2result, p4.player2result

阅读 185

2021-06-07

共1个答案

admin

这是一个工作示例。

当您添加新名称等时，这将使它保持最新状态，因此无需在每次创建新播放器时都编辑SQL。

唯一的挫折是球员的名字将必须是唯一的！！！！

我认为您将能够适应显示每个联赛的情况，但是如果您需要帮助，那就问一下。

还请注意，我的测试数据与您的测试数据不同。我只是编造了随机数据。

  ------------------------------------------
  --Data setup
  ------------------------------------------

  CREATE TABLE [dbo].[Results]
  (
      [ResultId] [int] IDENTITY(1,1) NOT NULL,
      [LeagueId] [int] NOT NULL,
      [Player1Id] [int] NOT NULL,
      [Player2Id] [int] NOT NULL,
      [Player1Result] [int] NULL,
      [Player2Result] [int] NULL
  )

  CREATE TABLE [dbo].[Players]
  (
      [PlayerId] [int] IDENTITY(1,1) NOT NULL,
      [UserId] [int] NOT NULL,
      [FirstName] [nvarchar](150) NULL,
      [LastName] [nvarchar](150) NULL
  )

  CREATE TABLE [dbo].[Leagues]
  (
      [LeagueId] [int] IDENTITY(1,1) NOT NULL,
      [LeagueName] [nvarchar](50) NULL
  )

  INSERT INTO Players (UserId,FirstName)
  VALUES 
      (1,'John'),
      (2,'Mark'),
      (3,'Peter'),
      (4,'Martin'),
      (5,'Paul')

  INSERT INTO Leagues(LeagueName)
  VALUES
      ('League 1'),
      ('League 2')

  INSERT INTO Results(LeagueId,Player1Id,Player2Id,Player1Result,Player2Result)
  VALUES
      (1,1,2,3,0),
      (1,1,3,0,4),
      (1,1,4,1,2),
      (1,1,5,2,1),
      (1,2,3,1,4),
      (1,2,4,3,2),
      (1,2,5,0,1),
      (1,3,4,1,2),
      (1,3,5,3,1),
      (1,4,5,2,1)

  ------------------------------------------
  --Answer
  ------------------------------------------

  --Get a list of all the names in the system
  DECLARE @Names NVARCHAR(MAX)

  SET @Names = (SELECT '[' + STUFF((SELECT '],[' + FirstName FROM Players ORDER BY FirstName FOR XML PATH('')),1,3,'') + ']')

  DECLARE @SQL NVARCHAR(MAX)

  --Create the matrix
  SET @SQL = '
  SELECT FirstName1,' + @Names + '
  FROM
  (
      SELECT P1.FirstName AS FirstName1,P2.FirstName AS FirstName2,R.Player1Result AS Result
      FROM  Results AS R
      INNER JOIN Players AS P1 ON P1.PlayerId = R.Player1Id
      INNER JOIN Players AS P2 ON P2.PlayerId = R.Player2Id

      UNION ALL

      SELECT P2.FirstName AS FirstName1,P1.FirstName AS FirstName2,R.Player2Result AS Result
      FROM  Results AS R
      INNER JOIN Players AS P1 ON P1.PlayerId = R.Player1Id
      INNER JOIN Players AS P2 ON P2.PlayerId = R.Player2Id
  ) AS P
  PIVOT
  (
      MAX (Result)
      FOR FirstName2 IN
      ( ' + @Names + ' )
  ) AS pvt
  ORDER BY pvt.FirstName1;
  '

  EXEC(@SQL)

  ------------------------------------------
  --Cleanup
  ------------------------------------------

  DROP TABLE Results
  DROP TABLE Players
  DROP TABLE Leagues

2021-06-07