记录的最后X总和的SQL总和(SQL Sum of last X Sum of records)

我看了一会儿，我无法找到这个问题的答案（也许我不是寻找正确的术语或东西）。 基本上，我有一个具有任意数量的每个日期项的数据库。 我需要的包含条目（忽略天有没有条目）最后X天的总和。 我知道如何找到每一天的总和。

说我有一个所谓的“产量”表格：

UnitID  Date        TestTime    NumMeasured NumPassing
1       2013-04-05  10:15       25          3
2       2013-03-31  10:12       30          1
3       2013-03-30  10:12       26          2
4       2013-03-29  10:30       11          1
5       2013-03-31  10:20       6           2
6       2013-04-05  10:30       5           0

然后我用这个查询：

SELECT DISTINCT 
    Date, 
    (
        SELECT SUM([NumMeasured]) 
        FROM [dbo].[Yield] T1 
        WHERE T1.Date = T2.Date
    ) AS 'NumMeasured', 
    (
        SELECT SUM([NumPassing]) 
        FROM [dbo].[Yield] T1 
        WHERE T1.Date = T2.Date
    ) AS 'NumPassing' 
FROM [dbo].[Yield] T2 

以获得总的通过/每天测量：

Date        NumMeasured NumPassing
2013-03-29      11          1
2013-03-30      26          2
2013-03-31      36          3
2013-04-05      30          3

然后我需要，我可以约会调用（比如我叫它为4/05），其获得的最后一个X（如100）包含条目天，返回NumMeasured和NumPassing列的总和，许多查询天。 我不能让这部分的工作，我一直运行到，我没有与解决足够的经验SQL问题。 我在寻找类似

SELECT
    (
        SELECT TOP 100 SUM(T3.[NumMeasured]) 
        FROM T3 
        WHERE T3.Date <= '4/05/2013' 
        ORDER BY T3.Date
    ) AS 'NumMeasured', 
    (
        SELECT TOP 100 SUM(T3.[NumPassing]) 
        FROM T3 
        WHERE T3.Date <= '4/05/2013' 
        ORDER BY T3.Date
    ) AS 'NumPassing', 
    (
        SELECT DISTINCT 
            Date, 
            (
                SELECT SUM([NumMeasured]) 
                FROM [PhaseNoiseMonitoring].[dbo].[Yield] T1 
                WHERE T1.Date = T2.Date
            ) AS 'NumMeasured', 
            (
                SELECT SUM([NumPassing]) 
                FROM [PhaseNoiseMonitoring].[dbo].[Yield] T1 
                WHERE T1.Date = T2.Date
            ) AS 'NumPassing' 
        FROM [PhaseNoiseMonitoring].[dbo].[Yield] T2 
    )
AS T3

预计查询将返回：

NumMeasured NumPassing
103         9

我意识到这是完全错误的，但我真的不能弄清楚如何使它发挥作用。

Answer 1:

下面是使用相关子查询一个想法：

with bydays as (<your query here>)
select bd.*,
       (select sum(NumMeasured) from (select top 100 * from bydays bd2 where bd2.date <= bd.date order by date desc) t
       ) as Measured100,
       (select sum(NumPassingd) from (select top 100 * from bydays bd2 where bd2.date <= bd.date order by date desc) t
       ) as Measured100
from bydays

Answer 2:

为了让您的最后100个日期，

SELECT TOP(100) DISTINCT date FROM yield ORDER BY date desc

您可以用尽可能简单的东西简化您的第一个查询

SELECT date, SUM(passing) as numpassing, SUM(Measured) as nummeasured
FROM yield
GROUP BY date

如果你需要的总和从现在起直到100天

SELECT SUM(passing) as npassing, SUM(measured) as nmeasured
FROM yield
WHERE date >= DATEADD(d,100,CURRENT)

如果你从过去的100个日期需要的总和

SELECT SUM(passing) as npassing, SUM(measured) as nmeasured
FROM yield
WHERE date IN (SELECT TOP 100 date FROM yield WHERE date <= '04/05/2013' ORDER BY date desc) 

文章来源: SQL Sum of last X Sum of records