問題描述

OrNo    CurrentDate         PreviousDate        Finished    Amount
G988    02.05.2013 14:00:47 NULL                False       1560
G988    02.05.2013 21:30:00 02.05.2013 14:00:47 False       3170
G988    03.05.2013 06:00:00 02.05.2013 21:30:00 False       5095
G988    03.05.2013 07:46:24 03.05.2013 06:00:00 True        5254

表名:oldDate

我有這個數據，我必須計算一天的總量，但我還需要減去前一天的數量，以便只計算今天(當前日期)生產的數量.

I have this data, and I have to calculate the total amount on a single day but I need to also subtract the previous day's amount, so that only the amount which was produced today (current date) is calculated.

當前日期是處理訂單的實際日期，上一個日期是處理該訂單的最后一天.如果我在解釋數據時不清楚，請指出我，我試過..

Current Date is the real date on which the order is processed and previous date is the last day's date on which this order was processed. Point me out if m not clear in explanation of the data, I tried ..

if t2.CurrentDate = t1.PreviousDate and datepart(t2.CurrentDate)= datepart(t1.CurrentDate) 

then
     if t1.CurrentDate>t2.CurrentDate

        then  @amount = t1.Amount

     else @amount = t2.Amount

我不擅長處理連接 .. :( 所以我對這個邏輯有問題，我嘗試了其他示例中的其他代碼但沒有成功，任何想法都將不勝感激..

I'm bad at dealing with joins .. :( so I have problems with this logic, I tried some other code from other examples but was not successful, any ideas will be really appreciated..

推薦答案

如果你能得到一個帶有列的表格:

If you could get a table with columns:

• 或否
• 當前日期
• 上一個日期
• CurrAmount
• 上一個金額

那么解決您的問題將是微不足道的.給定 AnonymousTable，這個查詢生成數據:

Then solving your problem would be trivial. Given the AnonymousTable, this query generates the data:

SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount AS CurrAmount,    b.Amount AS PrevAmount
  FROM AnonymousTable AS a
  JOIN AnonymousTable AS b
    ON a.OrNo = b.OrNo AND a.PrevDate = b.CurrDate
UNION
SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount AS CurrAmount,    0 AS PrevAmount
  FROM AnonymousTable AS a
 WHERE a.PreviousDate IS NULL

所以，大概，你可以寫:

So, presumably, you could write:

SELECT OrNo, CurrDate, CurrAmount - PrevAmount AS NewAmount
  FROM (SELECT a.OrNo,
               a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
               a.Amount AS CurrAmount,    b.Amount AS PrevAmount
          FROM AnonymousTable AS a
          JOIN AnonymousTable AS b
            ON a.OrNo = b.OrNo AND a.PrevDate = b.CurrDate
        UNION
        SELECT a.OrNo,
               a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
               a.Amount AS CurrAmount,    0 AS PrevAmount
          FROM AnonymousTable AS a
         WHERE a.PreviousDate IS NULL
       )

同樣清楚，如果你下定決心，你可以通過寫作來簡化事情:

Equally clearly, if you put your mind to it, you can simplify things by writing:

SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount - b.Amount AS NewAmount
  FROM AnonymousTable AS a
  JOIN AnonymousTable AS b
    ON a.OrNo = b.OrNo AND a.PrevDate = b.CurrDate
UNION
SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount AS NewAmount
  FROM AnonymousTable AS a
 WHERE a.PreviousDate IS NULL

這里的關鍵技術是 UNION 查詢和自聯接.您的數據具有一致的日期和時間線程化"，因此當前日期和上一個日期列之間的比較是微不足道的.

The key techniques here are the UNION query and the self-join. Your data has consistent 'threading' of the dates and times, so the comparison between the current date and previous date columns is trivial.

這篇關于減去同一表的兩行并求和的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！