問題描述

我正在嘗試對該表進行查詢:

I'm trying to do a query on this table:

Id       startdate     enddate       amount
1        2013-01-01   2013-01-31      0.00
2        2013-02-01   2013-02-28      0.00
3        2013-03-01   2013-03-31      245
4        2013-04-01   2013-04-30      529
5        2013-05-01   2013-05-31      0.00
6        2013-06-01   2013-06-30      383
7        2013-07-01   2013-07-31      0.00
8        2013-08-01   2013-08-31      0.00

我想得到輸出:

2013-01-01          2013-02-28          0
2013-03-01          2013-06-30          1157
2013-07-01          2013-08-31           0

我想得到那個結果，這樣我就能知道錢什么時候開始進來，什么時候停止.我還對資金開始流入之前的月數(這解釋了第一行)和資金停止的月數感興趣(這解釋了為什么我也對 2013 年 7 月至 2013 年 8 月的第三行感興趣)).

I wanted to get that result so I would know when money started to come in and when it stopped. I am also interested in the number of months before money started coming in (which explains the first row), and the number of months where money has stopped (which explains why I'm also interested in the 3rd row for July 2013 to Aug 2013).

我知道我可以在日期上使用 min 和 max 并在金額上使用總和，但我不知道如何以這種方式劃分記錄.
謝謝！

I know I can use min and max on the dates and sum on amount but I can't figure out how to get the records divided that way.
Thanks!

推薦答案

這是一個想法(和 a小提琴一起去):

Here's one idea (and a fiddle to go with it):

;WITH MoneyComingIn AS
(
    SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, 
        SUM(amount) AS amount
    FROM myTable
    WHERE amount > 0
)
SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, 
    SUM(amount) AS amount
FROM myTable
WHERE enddate < (SELECT startdate FROM MoneyComingIn)
UNION ALL
SELECT startdate, enddate, amount
FROM MoneyComingIn
UNION ALL
SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, 
    SUM(amount) AS amount
FROM myTable
WHERE startdate > (SELECT enddate FROM MoneyComingIn)

<小時>

還有一秒鐘，不使用 UNION (fiddle):

SELECT MIN(startdate), MAX(enddate), SUM(amount)
FROM
(
    SELECT startdate, enddate, amount,
    CASE 
        WHEN EXISTS(SELECT 1 
                    FROM myTable b 
                    WHERE b.id>=a.id AND b.amount > 0) THEN
            CASE WHEN EXISTS(SELECT 1 
                             FROM myTable b 
                             WHERE b.id<=a.id AND b.amount > 0) 
                 THEN 2 
                 ELSE 1 
            END
        ELSE 3
    END AS partition_no
    FROM myTable a
) x
GROUP BY partition_no

雖然我認為它假設 Id 是有序的.你可以用 ROW_NUMBER() OVER(ORDER BY startdate) 代替它.

although I suppose as written it assumes Id are in order. You could substitute this with a ROW_NUMBER() OVER(ORDER BY startdate).

這篇關于在 t-sql 中按日期范圍分組的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！