我的存储过程中有一个表变量,执行时给出以下结果
SELECT * FROM @TableVariable
Team | Score | Change
Team1 213 46
Team2 244 51
Team1 345 48
Team2 256 45
Team3 346 75
我的存储过程在执行时应以以下格式给出结果。
SELECT T.[TeamName] as 'TeamName', @ScoreChange AS 'ScoreMetric', '' AS 'SeriesMetric'
FROM dbo.[Team] T WHERE T.[OwnerId] = @OwnerId
TeamName | ScoreMetric | SeriesMetric
Team1 34
Team2 34
Team3 34
我遇到的问题是计算SeriesMetric
,因为在计算时涉及计算,SeriesMetric
因此我需要将该计算逻辑嵌入到以下存储在我的存储过程中的选择查询中
SELECT T.[TeamName] as 'TeamName', @ScoreChange AS 'ScoreMetric', '' AS 'SeriesMetric'
FROM dbo.[Team] T WHERE T.[OwnerId] = @OwnerId
以下是用于计算“ SeriesMetric”的逻辑,该逻辑需要嵌入上述选择查询中
我需要将我的Table变量@TableVariable中的所有Teams分组,并按team计算平均值(变化)
每个团队都有一个ChangeAverage值
Team | ChangeAverage
Team1 47
Team2 48
Team3 75
每个团队的“ SeriesMetric”为 ChangeAverage*100 / 25
你可以使用窗口功能和分区来实现
SELECT
*
, AVG(Change) over (parition by Team) as ChangeAverage
, (AVG(Change) over (parition by Team)) * 4 as SeriesMetric
FROM @TableVariable
但是,如果要与其他表结合使用以显示最终结果,则需要执行以下操作:
SELECT T.[TeamName] as 'TeamName'
, @ScoreChange AS 'ScoreMetric'
, AVG(TV.Change)* 4 AS 'SeriesMetric'
FROM dbo.[Team] T
LEFT JOIN @TableVariable TV
on T.TeamName = TV.TeamName -- or whatever FK-PK is
WHERE T.[OwnerId] = @OwnerId
GROUP BY
T.NAME ,ScoreMetric