复制上月的值并插入新行 [英] Copy prior month value and insert into new row

问题描述

以下是我现有表格的示例：

Here is an example of the current table I have:

1）表名称：TotalSales

1) Table name: TotalSales

Name    Year  Month  Sales
------  ----  -----  -----
Alfred  2011  1      100

我想要做的是创建一个这样的表，添加一个新行（上个月销售）：

What I want to do is create a table like this, add a new row(Prior month sales):

2）表名称：TotalSales

2) Table name: TotalSales

Name    Year  Month  Sales  Prior month sales
------  ----  -----  -----  -----------------
Alfred  2011  2      110    100

不知道怎么做，但这是我一直在做的：

Not sure how to this, but this is what I have been working on:

SELECT Name, Year, Month, Sales, Sales as [Prior Month sales]
FROM TotalSales
WHERE
DATEPART(month, [Prior Month sales]) = DATEPART(month, DATEADD(month, -1, getdate()))

$ b b

感谢任何帮助

Thanks for any help

推荐答案

要选择给定数据，您需要将表格连接到自身：

To select the given data you need to join the table to itself:

SELECT
    TS.name,
    TS.year,
    TS.month,
    TS.sales,
    COALESCE(TS2.sales, 0) AS prior_month_sales
FROM
    TotalSales TS
LEFT OUTER JOIN TotalSales TS2 ON
    TS2.name = TS.name AND
    (
        (TS2.year = TS.year AND TS2.month = TS.month - 1) OR
        (TS.month = 1 AND TS2.month = 12 AND TS2.year = TS.year - 1)
    )

LEFT OUTER JOIN 是外部联接，以防他们在上个月没有任何销售（或这是他们与公司的第一个月）。

The LEFT OUTER JOIN is an outer join in case they didn't have any sales the previous month (or this is their first month with the company).

这篇关于复制上月的值并插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！