我已经在StackOverflow链接上发现此问题：-SQL Server 2008“指定”列被多次指定，但对我没有太大帮助。
以下代码引发异常。我怎样才能解决这个问题？
--Calculating the average true range---------
WITH cteRange AS
(
SELECT
t.[Date],
ROUND(t.[BID_High] - t.[BID_Low], 5) AS [H-L],
ABS(ROUND(t.[BID_High] - t.[BID_Close], 5)) AS [H-Cp],
ABS(ROUND(t.[BID_Low] - t.[BID_Close], 5)) AS [L-Cp]
FROM
(SELECT
*,
[BID_Close] = LAG([BID_Close]) OVER (ORDER BY [Date])
FROM
[STOCK_DATA_FOR_ANALYSIS]) AS t
), cteTrueRange AS
(
SELECT *
FROM cteRange
CROSS APPLY (
SELECT MAX(v) AS v
FROM (VALUES ([H-L]), ([H-Cp]), ([L-Cp])) AS value(v)
) AS sub([TR])
), cteTrueRange10 AS
(
SELECT
*,
LAG([TR], 1) OVER (ORDER BY [Date]) AS [TR1],
LAG([TR], 2) OVER (ORDER BY [Date]) AS [TR2],
LAG([TR], 3) OVER (ORDER BY [Date]) AS [TR3],
LAG([TR], 4) OVER (ORDER BY [Date]) AS [TR4],
LAG([TR], 5) OVER (ORDER BY [Date]) AS [TR5],
LAG([TR], 6) OVER (ORDER BY [Date]) AS [TR6],
LAG([TR], 7) OVER (ORDER BY [Date]) AS [TR7],
LAG([TR], 8) OVER (ORDER BY [Date]) AS [TR8],
LAG([TR], 9) OVER (ORDER BY [Date]) AS [TR9]
FROM cteTrueRange
)
SELECT
[Date], [H-L], [H-Cp], [L-Cp], [TR], [TRA]
FROM
cteTrueRange10
CROSS APPLY
(SELECT CASE WHEN [TR9] IS NOT NULL THEN AVG(v) END AS v
FROM (VALUES ([TR]), ([TR1]), ([TR2]), ([TR3]), ([TR4]), ([TR5]), ([TR6]), ([TR7]), ([TR8]), ([TR9])) AS value(v)
) AS sub([TRA]);
错误：
消息8156，级别16，状态1，行17
为“ t”多次指定了“ BID_Close”列。
问题在这里：
(SELECT
*,
[BID_Close] = LAG([BID_Close]) OVER (ORDER BY [Date])
FROM
[STOCK_DATA_FOR_ANALYSIS]) AS t
STOCK_DATA_FOR_ANALYSIS表/视图似乎有一个名为的列BID_Close，该列由*您的SELECT语句中的选择。最重要的是，您还定义了一个别名为BID_Close（由that定义）的新列LAG(...)-因此，现在您选择的数据集中有两个列被调用BID_Close，这会导致错误。
您可以通过以下方法解决此问题：
*只选择您真正需要的BID_Close列（而不是选择列）：
SELECT
(list of columns, except BID_Close),
[BID_Close] = LAG([BID_Close]) OVER (ORDER BY [Date])
为该计算列定义一个不同的列别名，以[BID_Close_Last] = LAG([BID_Close]) OVER (ORDER BY [Date])使基础表BID_Close中的列为，而新计算的值称为BID_Close_Last
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句