SQL计数按数字序列分组 [英] SQL Count Grouping by a sequence of numbers

问题描述

我有一个像这样的SQL表：

  id pNum 
 ----- ---- ---- 
 100 12 
 100 13 
 100 15 
 100 16 
 100 17 
 200 18 
 200 19 
 300 20 
 300 21 
 300 25 
  

我想要分组id和pNum序列，并计算行数。有这样的结果。

  id res 
 ----- -------- 
 100 2 
 100 3 
 200 2 
 300 2 
 300 1 
  

关于如何做这件事的任何想法？ Server 2005 +）
$ b $ pre $ SELECT id，
count（*）AS res
FROM（SELECT *，
[pNum] - ROW_NUMBER（）OVER（PARTITION BY [id] ORDER BY [pNum]）AS Grp
FROM YourTable）T
GROUP BY id，
Grp


SQL小提琴

i have a SQL table like this:

id      pNum
-----   --------
100     12
100     13
100     15
100     16
100     17
200     18
200     19
300     20
300     21
300     25

and i want to group by the id and the pNum sequences, and count the number of rows. having a result like this.

id      res
-----   --------
100     2
100     3
200     2
300     2
300     1

any idea on how to do it?

解决方案

If your DBMS supports window functions (e.g. SQL Server 2005+)

SELECT id,
       count(*) AS res
FROM   (SELECT *,
               [pNum] - ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [pNum]) AS Grp
        FROM   YourTable) T
GROUP  BY id,
          Grp 

SQL Fiddle

这篇关于SQL计数按数字序列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！