sum(column)=值时返回行，在SQL Server上

Question

我的表格：

id int(11) AUTO_INCREMENT
date date
number int(3)
+----+------------+--------+
| id | date       | number |
+----+------------+--------+    
| 1  | 2010-01-02 |   0    |
| 2  | 2010-01-03 |   3    |
| 3  | 2010-01-04 |   0    |
| 4  | 2010-01-05 |   2    |
| 5  | 2010-01-06 |   1    |
| 6  | 2010-01-07 |   3    |
+----+------------+--------+

我想返回数字总和为6和日期>'2010-01-04'

的日期

我要返回添加字段号等于6的日期（在此示例中，在2010-01-04之后）。示例：我的查询应返回2010-01-07，因为2010-01-05 1在2010-01-06 3中的2010-01-01-07

到目前为止，我已经在MySQL Server上使用了此查询：

select date 
    from 
      (select date, @n:=@n+total total 
          from 
            (select date, sum(number) total 
                from MyTable 
              where date > '2010-01-04' 
              group by date 
              order by date 
            ) t 
           cross join 
            (select @n:=0) n 
      ) tt 
   where total = 6

现在，我们在SQL Server上只有一台服务器，我在SQL Server上使用此查询有一些问题。

Answer 1

在这种情况下，您可以使用以下方式：

with calc as 
(
    select top 100 percent
                t1.id, t1.dt, SUM(t2.num) acm
    from        MyTable t1
    inner join  MyTable t2 on t1.id >= t2.id
    group by    t1.id, t1.dt
    order by    t1.id
)
select top 1 id, dt as Date, Acm
from   calc
where  Acm >= 6;

结果：

|id|Date               |Acm|
|-:|:------------------|--:|
| 5|06/01/2010 00:00:00|  6|

另一种方法：

with calc2 as
(
    select id, dt, sum(num) over (order by dt) acm
    from @csum
)
select top 1 id, dt as Date, Acm
from   calc2
where  Acm >= 6;

在此处检查 dbfiddle

update

如果您每次累积总和达到6时都需要一个重置点，则它更为复杂。

with calc2 as
(
    select id, dt, num,
           sum(num) over (order by dt) acm, 
           sum(num) over (order by dt) / 6 reset
    from @csum
)
select id, dt as Date
from   @csum
where  id in (select min(id) from calc2 t2 where reset > 0 group by reset);

第一个CTE每次累积总和到达6.

第二查询返回每个"重置"分区的第一行。

(select min(id) from calc2 t2 where reset > 0 group by reset)

尝试 dbfiddle在这里

Answer 2

为什么不尝试此操作：

WITH
-- input, don't use in real query
input(id,the_date,the_number) AS (
          SELECT 1,DATE '2010-01-02',0
UNION ALL SELECT 2,DATE '2010-01-03',3
UNION ALL SELECT 3,DATE '2010-01-04',0
UNION ALL SELECT 4,DATE '2010-01-05',2
UNION ALL SELECT 5,DATE '2010-01-06',1
UNION ALL SELECT 6,DATE '2010-01-07',3
)
-- end of input, start "real" WITH clause here
,
run_sum AS (
SELECT
  *
, SUM(the_number) OVER(ORDER BY id) AS the_sum
FROM input
WHERE the_date > '2010-01-04'
)
-- the above returns:
-- SELECT * FROM run_sum;
-- id|the_date  |the_number|the_sum
--  4|2010-01-05|         2|      2
--  5|2010-01-06|         1|      3
--  6|2010-01-07|         3|      6
SELECT
  the_date
FROM run_sum
WHERE the_sum = 6
;

Answer 3

如果您有MSSQL 2012，可以尝试以下操作：

SELECT DATE 
    FROM 
      (SELECT DATE, SUM(TOTAL) OVER (ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL
          FROM 
            (SELECT DATE, SUM(NUMBER) TOTAL 
                FROM MYTABLE
              WHERE DATE > CONVERT(DATE, '2010-01-04',121) 
              GROUP BY DATE               
            ) T         
      ) TT 
   WHERE TOTAL = 6