愿所有的美好和期待都能如约而至

sql仅按顺序分组

发布时间:  来源:互联网  作者:匿名  标签:error sql group by only rows which are in sequence exception gaps-and-islands gr  热度:37.5℃

本文介绍了sql仅按顺序分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设我有下表:

MyTable
---------
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | B |
| 5 | B |
| 6 | B |
| 7 | A |
| 8 | A |
---------

我需要 sql 查询输出以下内容:

I need the sql query to output the following:

---------
| 3 | A |
| 3 | B |
| 2 | A |
---------

基本上我正在做一个 group by 但只针对在序列中在一起的行.有什么想法吗?

Basically I’m doing a group by but only for rows which are together in the sequence. Any ideas?

请注意,数据库位于 sql server 2008 上.有一篇关于此主题的帖子,但它使用了 oracle 的 lag() 函数.

Note that the database is on sql server 2008. There is a post on this topic however it uses oracle’s lag() function.

推荐答案

这被称为孤岛”问题.使用 Itzik Ben Gan 的方法:

This is known as the “islands” problem. Using Itzik Ben Gan’s approach:

;WITH YourTable AS
(
SELECT 1 AS N, 'A' AS C UNION ALL
SELECT 2 AS N, 'A' AS C UNION ALL
SELECT 3 AS N, 'A' AS C UNION ALL
SELECT 4 AS N, 'B' AS C UNION ALL
SELECT 5 AS N, 'B' AS C UNION ALL
SELECT 6 AS N, 'B' AS C UNION ALL
SELECT 7 AS N, 'A' AS C UNION ALL
SELECT 8 AS N, 'A' AS C
),
     T
     AS (SELECT N,
                C,
                DENSE_RANK() OVER (ORDER BY N) - 
                DENSE_RANK() OVER (PARTITION BY C ORDER BY N) AS Grp
         FROM   YourTable)
SELECT COUNT(*),
       C
FROM   T
GROUP  BY C,
          Grp 
ORDER BY MIN(N)

这篇关于sql仅按顺序分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,

勇敢去编程!

勇敢的热爱编程,未来的你一定会大放异彩,未来的生活一定会因编程更好!

TOP