使用变量命名列

2021-09-10 00:00:00 variables tsql sql-server columnname

我希望在一系列游戏中计算所有玩家的上升点差异.我希望第一个列名 PD1 的点差最小，PD2 次之，依此类推.我希望使用一个变量来执行此操作，该变量仅根据游戏数量将整数添加到PD"的末尾.将 AS @ColumnName 添加到计算末尾时，我不断收到错误消息.

I wish to calculate Ascending Points Diff for all players in a sweep over a series of games. I wish to have the first column name PD1 with the least Points Difference, PD2 next lowest and so on. I wish to do this using a variable which just adds the integer to the end of 'PD' based on the number of games. I keep getting an error when I add AS @ColumnName to the end of the Calculation.

USE [Rugby Pools]


DECLARE @counter int
DECLARE @MaxPlayer int
DECLARE @ColumnName varchar(50)

SET @counter = (SELECT MIN([Player_ID]) FROM [dbo].[Players])
SET @MaxPlayer = (SELECT MAX([Player_ID]) FROM [dbo].[Players])


DECLARE @gamecounter int
DECLARE @MaxGame int
SET @gamecounter = (SELECT MIN([Game_ID]) FROM [dbo].[Match])
SET @MaxGame = (SELECT MAX([Game_ID]) FROM [dbo].[Match])
SET @ColumnName='PD'+@gamecounter

WHILE @gamecounter <= @MaxGame

BEGIN


WHILE @counter <= @MaxPlayer

BEGIN



SELECT     TOP (@gamecounter)   dbo.Players.Player_ID, dbo.Entries.Game_ID, ABS(ABS(dbo.Entries.Home_Score-dbo.Entries.Away_Score)-(dbo.Match.Home_Score-dbo.Match.Away_Score)) AS @ColumnName
FROM            Entries INNER JOIN
                         Match ON Entries.Game_ID = Match.Game_ID INNER JOIN
                         Players ON Entries.Player_ID = Players.Player_ID

                         WHERE dbo.Match.Home_Score IS NOT NULL AND dbo.Players.Player_ID=@counter

                         ORDER BY Players.Player_ID, PointsDiff1 ASC

SET @Counter += 1

                         END                        

SET @gamecounter += 1

END

一旦工作，我将能够对其进行更改以更新表格，这将允许我提供由获胜决定的球员排名表，然后是锦标赛过程中的最佳分差.

Once working, I will be able to alter it to update a table which will allow me to provide a league table of players decided by wins, followed by best points difference over the course of the tournament.

推荐答案

像这样将您的最终查询部分更改为动态 sql.只能通过动态sql来实现.

Change your final query section to dynamic sql like this. It can be achieved only by dynamic sql.

 declare @query nvarchar(max)
 set @query = ' SELECT TOP (' + cast(@gamecounter as varchar(10)) + ') dbo.Players.Player_ID, 
               dbo.Entries.Game_ID, ABS(ABS(dbo.Entries.Home_Score-dbo.Entries.Away_Score)-(dbo.Match.Home_Score-dbo.Match.Away_Score)) AS ' + QUOTENAME(@ColumnName) + '
    FROM            Entries INNER JOIN
                             Match ON Entries.Game_ID = Match.Game_ID INNER JOIN
                             Players ON Entries.Player_ID = Players.Player_ID

                             WHERE dbo.Match.Home_Score IS NOT NULL AND dbo.Players.Player_ID= ' + cast(@counter as varchar(10)) + '
                             ORDER BY Players.Player_ID, PointsDiff1 ASC'

  sp_executesql @query

相关文章