MSSQL 中的除以'Over'子句有效，但除以'Alias'则不起作用

在MS SQL Server中，我花了太多时间试图解决这个问题。我终于想通了，只是我不知道原因。怎么会，除以第 4 行中的演员表在下面工作

SELECT 
cast(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT AS decimal(18,8))
AS TOTAL_NET_AMOUNT_AMOUNT,
cast((SUM(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
OVER (PARTITION BY dbo.DIMPROJECT.PROJECT_KEY)) AS decimal(18,8))
AS ActualAmountPaidOnProjectGroupedByInvoice,
((dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
/
(cast((SUM(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
OVER (PARTITION BY dbo.DIMPROJECT.PROJECT_KEY)) AS decimal(18,8))))
AS 'Allocation_Amount',

但是当我尝试除以我创建的别名时，在第 3 行中，"实际金额已支付在事务分组按发票"，我收到一条错误消息：

Msg 207，级别 16，状态 1，第 131 行无效的列名称"实际金额已支付事项分组按发票">

示例错误代码：

SELECT 
cast(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT AS decimal(18,8))
AS TOTAL_NET_AMOUNT_AMOUNT,
cast((SUM(dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
OVER (PARTITION BY dbo.DIMPROJECT.PROJECT_KEY))
AS decimal(18,8))
AS ActualAmountPaidOnProjectGroupedByInvoice,
((dbo.FACTINVOICEHEADER.TOTAL_NET_AMOUNT_AMOUNT)
/
(ActualAmountPaidOnProjectGroupedByInvoice) AS decimal(18,8))))
AS 'Allocation_Amount'

怎么来了？谢谢大家！

无法在查询中使用别名的原因是查询引擎尚未识别别名。引擎按以下顺序分阶段评估查询：

从 -> 其中 -> 分组依据 ->具有 ->选择 -> 排序依据 -> 限制

SELECT阶段的最后步骤之一是将查询中指定的别名应用于生成的数据集。由于它们在SELECT阶段结束之前不会应用，因此它们在要返回的数据的评估中不可用，在WHERE、GROUP BY或HAVING阶段中也不可用。

此外，某些查询引擎确实允许在ORDER BY阶段使用别名(或序号位置)。正如Julian在评论中指出的那样，MSSQL确实允许序号位置排序语法。

我想您可能会误解别名列在哪里可用/可以通过别名引用，特别是因为您说(释义)"我在 sql 的第 3 行创建的别名在第 4 行不可用"：

错：

SELECT
1200 as games_won,
25 as years_played,
--can't use these aliases below in the same select block that they were declared in
games_won / years_played as games_won_per_year 
...

右：

SELECT
1200 as games_won,
25 as years_played,
--can use the values though 
1200 / 25 as games_won_per_year

右：

SELECT
games_won / years_played as games_won_per_year  --alias from inner scope is available in this outer scope
FROM
(
SELECT 
--these aliases only become available outside the brackets
1200 as games_won,
25 as years_played
) x

不能为列设置别名，也不能在同一选择块中再次使用该别名;只能在内部/子查询中设置别名，而在外部查询中使用该别名。SQL不像一种逐行操作的编程语言：

int gameswon = 1200;
int yearsplayed = 25;
int winsperyear = gameswon / yearsplayed;

在此 C# 中，您可以看到我们在前面的行上声明变量(别名)，并在后面的行中使用它们，但这是因为编程语言逐行操作。早期行执行的结果可用于后面的行。SQL不是那样工作的;SQL 一次处理查询的整个部分。在整个选择块处理完毕之前，您的列不会获取您提供给它们的别名，因此您无法为列或计算提供别名，然后在同一选择块中再次使用该别名。解决此问题并创建稍后将重复使用的别名的唯一方法是在子查询中创建别名。

这是另一个示例：

SELECT 
fih.tot_amt / fih.amt_per_proj AS allocation_amount
FROM
(
SELECT
CAST(f.total_net_amount_amount AS DECIMAL(18,8)) as tot_mt,
CAST(SUM(f.total_net_amoun_amount) OVER (PARTITION BY p.project_key)) AS DECIMAL(18,8)) AS amt_per_proj
FROM
dbo.factinvoiceheader f
INNER JOIN
dbo.dimproject p
ON ...
) fih

在这里你可以看到我拉取了我想要的列并在内部查询中为它们设置了别名，然后在外部查询中使用了别名 - 它之所以有效，是因为在内部块内贴花的别名可用于外部块

永远记住，SQL不是典型的编程语言，而是逐块。事实上，在大多数编程语言中，在内部代码块中声明的东西在外部代码块中不可用(除非它们是像javascript var这样的全球化的东西)，所以SQL与你习惯的有所不同。每次在SQL中创建指令块时，您都有机会重新指定数据列的别名。

因为 SQL 是基于逐块的，所以我将我的 SQL 缩进到块中，以便于查看一起处理的内容。可以为 SELECT 中的列和 FROM 中的表创建 SELECT、FROM、WHERE、GROUP BY 和 ORDER BY 等关键字表示块和别名。在以上面的示例为例，我不仅将别名应用于计算和列，还应用于表。当查询在整个过程中缩进和锯齿时，它使查询更容易阅读 - 为您的表名提供一个别名，而不是在每个列名之前写入dbo.factinvoiceheader.

以下是一组使 SQL 更整洁、更易于阅读和调试的提示：

不要将它们全部放在一行或相同的缩进级别 - 根据指令块的深度或浅度进行缩进
选择、从、从、在哪里、分组依据、排序依据等表示操作块的开始 - 将它们全部缩进到同一级别，并将其子指令缩进到另一个级别(如果您的选择是缩进级别 2，则选择的列应该是缩进级别 3)
当您有一个内部查询缩进时，除非它非常简单并且读起来像一行一样很好
列名和表名使用小写，保留字、函数、数据类型使用大写(有些人更喜欢函数的驼峰大小写)
决定是使用CanelCase还是underscore_style来拆分单词并坚持下去
表添加别名，并始终选择列作为 tablealias.columnname - 如果表添加的列与您选择的原始列同名，而未限定原始列来自哪个表，这将防止查询在将来中断
别名表允许另一个重要操作;重复将同一个表联接到查询中。如果您的 Person 表具有工作地址和家庭地址，则只需将地址表加入两次以获取人员的两个地址的唯一方法是为表添加别名 (person join address h on p.homeaddressid = h.id join address w on p.workaddressid = w.id)

相关内容

最新更新

热门标签：