现有表上的派生/计算列 [英] Derived/calculated column on existing table

问题描述

一段时间以来，我一直在解决这个问题，我一直在寻求帮助。

I have been going nuts over this issue for some time and I am seeking help.

我有带有值的SQL Server表，如下所示：

I have SQL Server table with values, as follows:

Account - Date - Amount - Summary 
10000 - 2010-1-1 - 50.00 - 0.00 
10000 - 2010-2-1 - 50.00 - 0.00 
10000 - 2010-3-1 - 50.00 - 0.00 
10000 - 2010-4-1 - 50.00 - 0.00 
10000 - 2010-5-1 - 50.00 - 0.00 
10000 - 2010-6-1 - 50.00 - 0.00 
10000 - 2010-7-1 - 50.00 - 0.00 
10000 - 2010-8-1 - 50.00 - 0.00 
10000 - 2010-9-1 - 50.00 - 0.00 
10000 - 2010-10-1 - 50.00 - 0.00 
10000 - 2010-11-1 - 50.00 - 0.00 
10000 - 2010-12-1 - 50.00 - 600.00 
10000 - 2011-1-1 - 25.00 - 0.00
10000 - 2011-2-1 - 25.00 - 0.00
10000 - 2011-3-1 - 50.00 - 0.00
10000 - 2011-4-1 - 50.00 - 0.00
10000 - 2011-5-1 - 50.00 - 0.00
10000 - 2011-12-1 - 25.00 - 825.00
10000 - 2012-1-1 - 100.00 - 0.00
10000 - 2012-2-1 - 200.00 - 0.00
10000 - 2012-3-1 - 100.00 - 0.00
10000 - 2012-5-1 - 100.00 - 0.00
10000 - 2012-6-1 - 100.00 - 0.00
10000 - 2012-8-1 - 100.00 - 0.00
10000 - 2012-12-1 - 100.00 - 1625.00
10001 - 2010-1-1 - 50.00 - 0.00 
10001 - 2010-2-1 - 60.00 - 0.00 
10001 - 2010-12-1 - 60.00 - 170.00 
10001 - 2011-1-1 - 50.00 - 0.00
10001 - 2011-2-1 - 50.00 - 0.00
10001 - 2011-3-1 - 50.00 - 0.00
10001 - 2011-4-1 - 50.00 - 0.00
10001 - 2011-6-1 - 50.00 - 0.00
10001 - 2011-8-1 - 50.00 - 0.00
10001 - 2011-10-1 - 50.00 - 0.00
10001 - 2011-12-1 - 50.00 - 570.00

这是表的基本快照。 摘要列提供了年底的金额总数（基于日期列），但仅当MONTH（Date）= 12时。这种方式适用于数百个帐户，还有大约4年的时间。我想在此现有表中添加一列，称为 SummaryPreviousYear。 SummaryPreviousYear列应包含MONTH（Date）= 12与上一年的金额之和。我想在帐户号上加入此列，以便它位于摘要列旁边，并提供一个值，就像摘要值一样，但是，SummaryPreviousYear值需要在列的整个下方显示，而不是恰好是月份的第12个位置。例如，以下行：

This is a basic snapshot of the table. The "Summary" column gives the total for the "Amounts" at the end of the year (based on "date" column), but only when the MONTH(Date) = '12'. It goes on this way for hundreds of accounts, with about 4 more years as well. I would like to add a column to this existing table, called "SummaryPreviousYear". The SummaryPreviousYear column should have the sum of the amounts from MONTH(Date) = '12' and the previous year. I'd like to join this column on the account number, so that it sits next to the Summary column and gives a value just like the Summary value does, but the SummaryPreviousYear value would need to be present the whole way down the column, not just where the month is 12. For example, the following row:

之前：

Account - Date - Amount - Summary 
10001 - 2011-10-1 - 50.00 - 0.00
10001 - 2011-12-1 - 50.00 - 570.00

之后：

Account - Date - Amount - Summary - SummaryPreviousYear
10001 - 2011-10-1 - 50.00 - 0.00 - 170.00
10001 - 2011-12-1 - 50.00 - 570.00 - 170.00

有人可以帮助我吗？我在这里花了2天的时间，需要创建此数据集，以便继续进行报表开发。不幸的是，DBA不在现场。从字面上看我的机智。

Can anyone help me with this? I am pulling my hair out here for 2 days and need to get this dataset created so I can proceed with my report development. Unfortunately, the DBA's off site. Literally at my wit's end. Any help would be greatly appreciated.

推荐答案

SELECT    l.*, 
          q.summary AS SummaryPreviousYear
FROM      lists l
LEFT JOIN 
     (
          SELECT    Date, 
                    Summary
          FROM      lists 
          WHERE     MONTH(Date) = 12
     ) AS q  
          ON YEAR(l.Date) = YEAR(q.Date) + 1

这篇关于现有表上的派生/计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！