如何使用子查询设置计算列 [英] How to set a calculated column using a subquery

问题描述

我有一个要向其中添加计算列的表.我想将其设置为查询要比标准算术运算复杂，并且我不确定如何使用该查询设置计算列.我试图使用ALTER TABLE语句:

I have a table to which I would like to add a calculated column. The query I want to set it to is more complex than a standard arithmetic operation and I am unsure how to set up the calculated column using the query. I attempted to use an ALTER TABLE statement:

    ALTER TABLE shareholder.Amount
ADD CalculatedAmount As 
(SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)

但这会导致错误:'在这种情况下不允许子查询.仅允许使用标量表达式". 我知道子查询本身已经过测试，可以正常工作，所以只需要弄清楚如何将计算列设置为其结果即可.

But this results in an error: 'Subqueries are not allowed in this context. Only scalar expressions are allowed'. I know the sub-query itself works correctly having tested it on its own so it's just a matter of working out how to set the calculated column to be the result of it.

谢谢！ (我正在使用SQL Server 2014 Management Studio)

Thanks! (I am using SQL Server 2014 Management Studio)

推荐答案

不可能有带有子查询的计算列

It is not possible to have a Computed Column with a Sub Query,

计算列是根据可以使用其他表达式的表达式来计算的 同一张表中的列.

A computed column is computed from an expression that can use other columns in the same table.

因此不可能进行查询，但可以使用像

So it is not possible to have A Query but you can use Expressions Like

ColumnA-ColumnB+ColumnC

相反，您可以将其转换为视图"并在那里计算列"值

Instead, you can convert it as a View and Compute The Column values there

赞

CREATE VIEW MyComputedvIEW
AS
SELECT
  *,
  CalculatedAmount = (SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)
FROM YourTable

这篇关于如何使用子查询设置计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！