如何分别计算数据 [英] how to count data separately

问题描述

我在选择数据时遇到问题.数据是:_

hi there,

I have a problem to select data. The data are: _

ID     Date          Status
1      2008-01-01    A
1      2008-01-05    A
1      2008-01-11    A
1      2008-01-28    A
1      2008-02-02    A
1      2008-02-15    B
1      2008-02-26    A
1      2008-03-04    A
1      2008-03-17    B
1      2008-03-29    A
2      2008-01-01    A
2      2008-02-24    A
2      2008-03-26    B

数据应显示在以下_

The data should be shown in following _

ID    Status    Count      
1     A         1          
1     B         1
1     A         1          
1     B         1
1     A         1
2     A         1
2     B         1               

状态A应当计数为1，直到状态B为止.流程中有很多A，一次是B.

我需要帮忙.
谢谢大家.

The status A should be count as 1 until the status is B. The flow is many A, once B.

I need a hand.
Thanks all.

推荐答案

此解决方案有很多解释，但请尝试执行此查询并告诉我什么是缺失或错误.

假设1:ID列实际上可以不同，并且所有ID都应分开使用，否则，请省略o1.ID = o2.ID
假设2:count列只是用来告诉我们，在状态更改之前，第一个日期的结果集中应该只有一个条目
--description列也是如此

There''s a lot of interpretation in this solution, but try this query and tell me what''s missing or wrong.

Assumption 1: The ID column can actually be different and all id''s should be treaded separately, if not, omit the o1.ID = o2.ID
Assumption 2: The count column is there just for telling us that there should be only one entry in the result set for the first date until the status changes
And the same goes for the --description column

WITH ordered AS(
    SELECT  ID,Date,Status,ROW_NUMBER() OVER(ORDER BY Date) as rn
    FROM    MyTable
    )
SELECT  o1.ID,o1.DATE
FROM    ordered o1,ordered o2
WHERE   o1.ID = o2.ID
    AND o1.rn = o2.rn -1
    AND o1.Status <> o2.Status

这篇关于如何分别计算数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！