复制上个月的值并插入到新行中

发布于 2024-11-26 21:10:21 字数 606 浏览 3 评论 0原文

这是我当前表的一个示例：

1）表名称：TotalSales

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

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

2）表名称：TotalSales

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

不知道如何做，但这就是我一直在努力的事情：

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

感谢您的帮助

原文

Here is an example of the current table I have:

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) 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()))

Thanks for any help

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

听，心雨的声音 2024-12-03 21:10:21

我相信这应该有效...您需要在名称/上个月加入自身，但是由于年/月是单独存储的，所以您有上个月的 2 个测试用例。

select c.Name, c.Year, c.Month, c.Sales, p.Sales
from TotalSales c
left join TotalSales p
on c.Name = p.Name and (
    (c.Month > 1 and c.Year = p.Year and c.Month = p.Month + 1)
    or (c.Month = 1 and c.Year = p.Year + 1 and p.Month = 12))

I believe this should work...you need to join to itself on name/prior month, but you have 2 test cases for prior month since year/month are stored separately.

select c.Name, c.Year, c.Month, c.Sales, p.Sales
from TotalSales c
left join TotalSales p
on c.Name = p.Name and (
    (c.Month > 1 and c.Year = p.Year and c.Month = p.Month + 1)
    or (c.Month = 1 and c.Year = p.Year + 1 and p.Month = 12))

回复收藏 0 原文

小姐丶请自重 2024-12-03 21:10:21

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

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 是一个外部连接，以防他们上个月没有任何销售额（或者这是他们的第一个月）公司）。

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)
    )

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).

回复收藏 0 原文

睫毛上残留的泪 2024-12-03 21:10:21

尝试这样的方法，用您想要的值更新表......

UPDATE TotalSales
SET PriorMonthSales = 
(
    SELECT TS.Sales
    FROM TotalSales TS
    WHERE 
    (TotalSales.Month = TS.Month + 1 AND TotalSales.Year = TS.Year)
    OR 
    (TotalSales.Month = 1 AND TS.Month = 12 AND TS.Year = TotalSales.Year -1)
)

Try something like this to just update the table with the values you want...

UPDATE TotalSales
SET PriorMonthSales = 
(
    SELECT TS.Sales
    FROM TotalSales TS
    WHERE 
    (TotalSales.Month = TS.Month + 1 AND TotalSales.Year = TS.Year)
    OR 
    (TotalSales.Month = 1 AND TS.Month = 12 AND TS.Year = TotalSales.Year -1)
)

回复收藏 0 原文

~没有更多了~