如何在sql中获取列值的连续出现计数? [英] how to fetch continuous occurrence count of a column value in sql?

问题描述

如果表格是这样的，我还添加了一个id列用于订购目的

I have added one more id column for the order purpose, if the table is like this

+-----+-------+
| id  | cs_id |
+-----+-------+
|   1 | a     |
|   2 | b     |
|   3 | a     |
|   4 | a     |
|   5 | a     |
|   6 | b     |
|   7 | b     |
|   8 | b     |    
|   9 | b     | 
+-----+-------+ 

我希望ID列不断出现cs_id顺序

i want the continuous occurrence of cs_id order by id column

+-----+-------+---------------------------------
| id  | cs_id |    continuous_occurrence_cs_id 
+-----+-------+---------------------------------|
|   1 | a     |    1 
|   2 | b     |    1 
|   3 | a     |    1 
|   4 | a     |    2 
|   5 | a     |    3 
|   6 | b     |    1 
|   7 | b     |    2
|   8 | b     |    3
|   9 | b     |    4 
+-----+-------+---------------------------------+

推荐答案

首先，在SQL中，除非使用ORDER BY，否则定义数据没有任何顺序.
请参阅: Wikipedia-订购依据

First of all, in SQL by the definition data has no any order unless the ORDER BYis used.
See: Wikipedia - Order By

ORDER BY是对结果集中的行进行排序的唯一方法.
无 此子句，关系数据库系统可以在任何情况下返回行 订单.

ORDER BY is the only way to sort the rows in the result set.
Without this clause, the relational database system may return the rows in any order.

您必须在表中提供一个附加列来确定顺序，并且可以在ORDER BY子句中使用，例如以下示例中的RN列:

You must provide an additional column to your table that determines the order, and can be used in ORDER BY clause, for exampleRN column in the below example:

        RN CS_ID     
---------- ----------
         1 a         
         2 b         
         3 a         
         4 a         
         5 a         
         6 b         
         7 b         
         8 b         
         9 b   

---

对于以上数据，您可以使用通用表表达式(递归查询)来获取所需的结果，例如，以下查询适用于Oracle数据库:

---

For the above data you can use Common Table Expression (recursive query) to get required result, for example the below query works on Oracle database:

WITH my_query( RN, cs_id , cont ) AS (

    SELECT t.rn, t.cs_id, 1
        FROM My_table t
        WHERE rn = 1
    UNION ALL
    SELECT t.rn, t.cs_id,
         case when t.cs_id = m.cs_id
              then m.cont + 1
              else 1
         end
        FROM My_table t
        JOIN my_query m
        ON t.rn = m.rn + 1
)
select * from my_query
order by rn;

        RN CS_ID            CONT
---------- ---------- ----------
         1 a                   1
         2 b                   1
         3 a                   1
         4 a                   2
         5 a                   3
         6 b                   1
         7 b                   2
         8 b                   3
         9 b                   4

这篇关于如何在sql中获取列值的连续出现计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！