壁球联赛结果 - SQL 查询 [英] Squash League Results - SQL Query

问题描述

我最近接管了我所在的壁球俱乐部的内部联赛

I have recently taken over running the internal leagues at the squash club I play at

我希望把这些放到网上，供会员们根据需要查看和添加结果

I was hoping to put these online for members to view and add results as required

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

The league structure follows the below format with 6 leagues

联赛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)

我正在尝试编写一个查询，该查询在一个查询中为我提供每个部门的输出，而不是几个给我的输出有人可以帮忙查询吗?

I am trying to write a query which gives me the output of each divsion in one query rather than several to give me the output can anyone help with the query?

到目前为止我所拥有的是

what i have so far is

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

推荐答案

这是一个工作示例.

当您添加新名称等时，这将使其自身保持最新状态，因此无需在每次创建新玩家时编辑 SQL..

This will keep its self up to date as you add new names etc so no need to edit the SQL every time you make a new player..

唯一的缺点是玩家名称必须是唯一的！！！

The only setback is that the players name will need to be unique!!!!

我假设您可以调整此内容以显示每个联赛，但如果您需要这方面的帮助，请直接询问.

i assume you will be able to adapt this for showing each league but if you need help with this then just ask.

还要注意我的测试数据和你的不一样.我只是编造了随机数据.

also note my test data is not the same as yours. i just made up random data.

  ------------------------------------------
  --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

这篇关于壁球联赛结果 - SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！