如何在表中指定基于 SQL Server 中另一列的计算列? [英] How do I Specify Computed Columns in a Table which are based on another Column in SQL Server?

问题描述

我在下面看到了以下捏造的数据.

I have the following fabricated data seen below.

我想更改 Total_Investment 列，使其成为计算列，用于计算该行中存在的单位名称的总 Dollar_Amount.

I would like to alter the Total_Investment Column to make it a Computed Column which computes the total Dollar_Amount for the Unit Name that is present in that row.

例如，对于第 1 行，Total_Investment 槽将显示列为Healthy_Communities"的两行的总和，因此第 1 行和第 6 行，例如，5899.00 + 1766.00.

For example, for Row 1, the Total_Investment slot would show the sum of both rows that are listed as 'Healthy_Communities', so rows 1 and 6, eg., 5899.00 + 1766.00.

第 2 行将对 Urban 条目的所有三个(2、5 和 9)求和，依此类推.如何在 SQL Sever 2012 中完成此操作?

Row 2 would sum all three (2, 5, and 9) of the Urban entries, and so on. How do I accomplish this in SQL Sever 2012?

Project_ID  Unit_Name             Fiscal_Year  Dollar_Amount  Total_Investment
1           Healthy Communities   2000-01-01   5899.00        0.00
2           Urban                 2009-01-01   6008.00        0.00
3           Rivers and Watersheds 2006-01-01   6835.00        0.00
4           Large Landscapes      2011-01-01   5216.00        0.00
5           Urban                 2015-01-01   3555.00        0.00
6           Healthy Communities   2014-01-01   1766.00        0.00
7           Youth Engagement      2004-01-01   4246.00        0.00
8           Rivers and Watersheds 2014-01-01   8253.00        0.00
9           Urban                 2000-01-01   5590.00        0.00
10          Outdoor Recreation    2013-01-01   5356.00        0.00

我知道用于计算列的更改表文档但不知道如何修改此代码以适应我的问题.

I'm aware of the alter table documentation for computing columns but don't know how to modify this code to fit my problem.

推荐答案

仅将下面的 MyTable 替换为您的表的名称.这种方法的缺点是，如果处理多行(例如 10000 行需要 40 秒)，它会变得非常昂贵.对于这种情况，您可以使用视图.(感谢@Amit 对UDF 的坚持.抱歉，性能太棒了)

Only replace MyTable below with the name of your table. The drawback of this method is that it becomes really expensive if working with many rows (e.g. 40 s for 10000). For such a case, you can use a view. (Thanks to @Amit for insisting on the UDF. Sorry, the performance is aweful)

在 Unit_Name 上添加了非聚集索引，包括 Dollar_Amount.性能提高了十倍.

Added nonclustered index on Unit_Name, including Dollar_Amount. Performance improved tenfold.

-- drop table MyTable;
-- drop function udfMyTable;

-- go

create table MyTable(
 project_id int identity(1, 1) primary key,
 Unit_Name varchar(120),
 Dollar_Amount decimal(19, 2),
)

go
create nonclustered index IX_Unit on dbo.MyTable(Unit_Name) include (Dollar_Amount);

create function udfMyTable (@pk as int)
returns decimal(19, 2)
as
begin
declare @res as decimal(19, 2);
select 
    @res=sum(Dollar_Amount) 
from 
    MyTable 
where Unit_Name in (select Unit_Name from MyTable where project_id=@pk);
return @res;
end
go
alter table MyTable add Total_Amount as dbo.udfMyTable(project_id)
go


insert into MyTable (unit_name, dollar_amount) values
('Healthy Communities',   '5899.00'),
('Urban',                 6008.00),
('Rivers and Watersheds', 6835.00),
('Large Landscapes',      5216.00),
('Urban',                 3555.00),
('Healthy Communities',   1766.00),
('Youth Engagement',      4246.00),
('Rivers and Watersheds', 8253.00),
('Urban',               5590.00),
('Outdoor Recreation', 5356.00)

select * from MyTable;

这篇关于如何在表中指定基于 SQL Server 中另一列的计算列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！