問題描述

我希望在一系列游戲中計算所有玩家的上升點差異.我希望第一個列名 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

這篇關于使用變量命名列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！