首先我有一张付款表，相关数据如下
id | price | type | ts
------ | -------|----------|---------------------------
1 | 50 | Payment | 2016-06-24 16:01:00.000000
2 | 15 | Payment | 2016-06-24 16:02:00.000000
3 | 5 | Refund | 2016-06-24 16:03:00.000000
4 | 10 | Payment | 2016-06-24 16:04:00.000000
5 | 20 | Payment | 2016-06-24 16:05:00.000000
6 | 40 | Withdraw | 2016-06-24 16:06:00.000000
7 | 30 | Withdraw | 2016-06-24 16:07:00.000000
8 | 15 | Payment | 2016-06-24 16:08:00.000000
9 | 25 | Payment | 2016-06-24 16:09:00.000000
我想要的是将类型为“Payment”的所有行折叠成sum、begin和end period的形式，所有其他行必须相同，所以结果如下
id | price | type | begin | end
------ | -------|----------|---------------------------|---------------------------
null | 65 | Payment | 2016-06-24 16:01:00.000000| 2016-06-24 16:02:00.000000
3 | 5 | Refund | 2016-06-24 16:03:00.000000|
null | 30 | Payment | 2016-06-24 16:04:00.000000| 2016-06-24 16:05:00.000000
6 | 40 | Withdraw | 2016-06-24 16:06:00.000000|
7 | 30 | Withdraw | 2016-06-24 16:07:00.000000|
null | 40 | Payment | 2016-06-24 16:08:00.000000| 2016-06-24 16:09:00.000000
另外，如果它有一些标志，比如is row is grouped，以及对限制最终结果的支持，那么它将非常有用
现在我不再尝试行数、分组、延迟和其他，找不到正确的方法
UPD：链接到sql fiddle，工作结果http://sqlfiddle.com/#!15/3cfea/1/0
最佳答案
这有点棘手。您可以使用行号差异技巧来获取付款的组。然后，您可以使用case仅将其应用于付款本身（而不是其他值）。这看起来像：
select (case when type <> 'payment' then id) as id,
sum(price) as price,
min(type) as type,
min(ts) as begin,
max(case when type = 'payment' then ts end) as end
from (select t.*,
(row_number() over (order by id) -
row_number() over (partition by type order by id)
) as grp
from t
) t
group by (case when type = 'payment' then grp end),
(case when type <> 'payment' then id end);
关于sql - 折和求和组仅适用于多种类型之一，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39333753/