特定于SQL的分组/计数(避免游标)

Question

我正试图在没有游标的情况下进行特定的分组/计数。我知道这是有可能的，用游标，但我试图避免这一点。我的桌子是这样的：

ID | Amount
1  | 1200
2  | 1200
3  | 2500
5  | 1200
6  | 1200
7  | 1200
8  | 2500

结果应该是

Count| Amount
2    | 1200
1    | 2500
3    | 1200
1    | 2500

Answer 1

基本上，这是一个缺口和岛屿的情况。

• 数据存储在TEST表中。
• 使用INTER解析函数选择上一行的AMOUNT值
• INTER2 CTE创建组(使用SUM解析函数)
• 最后的查询按组(GRP列)按数量对行进行计数。我加入了MIN(RN)，只是为了设置正确的顺序。你可以把它移走。

。

with 
inter as
  (select rn, amount, 
     isnull(lag(amount) over (order by rn), amount) l_amount
   from test
  ),
inter2 as
  (select rn, amount,
     sum(case when amount <> l_amount then 1 else 0 end) over (order by rn) grp
   from inter
  )
select min(rn) rn, amount, count(*) cnt
from inter2
group by amount, grp
order by 1;

SQL Fiddle在这里。

Answer 2

这是一个空隙和岛屿问题。我会使用行号的差异来解决这个版本：

select amount, count(*)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by amount order by id) as seqnum_a
      from t
     ) t
group by amount, (seqnum - seqnum_a);

通常情况下，当你第一次看到它的时候，它的工作原理并不明显。目标是将一个组分配给具有相同数量的相邻元素。

如果您盯着子查询的结果，您将看到当数量相同且相邻时，这两个序列之间的差异是如何保持不变的。剩下的只是聚合。

如果您知道id没有空白，那么您可以使用id而不是seqnum。