c# - 如何在不破坏订单的情况下进行 GROUP BY？

Question

我有一个非常简单的有序表，其中包含以下数据:

=============================
|   Timestamp  |    Count   |
=============================
|   12/30/2016 |    322     |
|   12/29/2016 |    322     |
|   12/28/2016 |    322     |
|   12/4/2016  |    541     |
|   12/3/2016  |    541     |
|   12/2/2016  |    541     |
|   12/1/2016  |    322     |
|   11/30/2016 |    322     |
|   11/29/2016 |    322     |
=============================

我想 SELECT MAX([Timestamp]), Count FROM [dbo].[Table] GROUP BY [Count] 而不破坏 [Timestamp] 顺序，即输出应该是:

=============================
|   Timestamp  |    Count   |
=============================
|   12/30/2016 |    322     |
|   12/4/2016  |    541     |
|   12/1/2016  |    322     |
=============================

所以 12/1/2016 | 322 未被 GROUP BY 隐藏。有人知道如何编写这样的查询吗？我需要一个 EntityFramework Linq 查询，如果知道一个 T-SQL 类似物就好了。

Answer 1

我认为您需要这样的查询:

select [Timestamp], [Count]
from (
    select *
        --//Step 3: Now just find the max value
        , row_number() over (partition by seqGroup order by [Timestamp] desc) as seq
    from (
        select *
            --//Step 2: you need to generate a group number for each sequence
            , sum(changed) over (order by [Timestamp] desc) as seqGroup
        from (
            select *
                --//Step 1: you need to track changes over [Count]
                , case when coalesce(lag([Count]) over (order by [Timestamp] desc), [Count]) = [Count] then 0 else 1 end as changed
            from yourTable ) t1
        ) t2
    ) t3
where seq = 1;