oracle按时间段找出某个字段中所有0开始0结束的数，有几组

Question

本帖最后由 dangqinghua 于 2010-01-31 11:23:57 编辑

Answer 1

看不到你的图片。
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 2

  /*==============================================================*/
/* Table: FlowHistory     流量计历史数据                        */
/*==============================================================*/
create table FlowHistory  (
   FlowHisID            NUMBER(20)                      not null,
   FlowID               NUMBER(20),                  //流量计
   WUserID              NUMBER(20),                  //用户
   FlowHisValue         NUMBER(20,4),                //瞬时流量值
   FlowHisTotalValve    NUMBER(20,4),                //累计流量值
   FlowHisTime          DATE,
   FlowHisIP            VARCHAR2(100),
   FlowHisRe        VARCHAR2(1000),
   constraint PK_FLOWHISTORY primary key (FlowHisID)
);
/*==============================================================*/
/* Table: Flowmeter    流量计表                                 */
/*==============================================================*/
create table Flowmeter  (
   FlowID               NUMBER(20)                      not null,
   SiteID               NUMBER(20),
   FlowRunID            NUMBER(20),
   RTUCODE              VARCHAR2(100),
   PipelineCode         VARCHAR2(100),
   FlowName             VARCHAR2(100)                   not null,
   FlowType             VARCHAR2(100)                   not null,
   FlowState            VARCHAR2(100),
   FlowDesc             VARCHAR2(500)                   not null,
   FlowRe           VARCHAR2(1000),
   constraint PK_FLOWMETER primary key (FlowID)
);
/*==============================================================*/
/* Table: WaterUser    用水户表                                 */
/*==============================================================*/
create table WaterUser  (
   WUserID              NUMBER(20)                      not null,
   SiteID               NUMBER(20),
   WUserName            VARCHAR2(100)                   not null,
   WUserCorporate       VARCHAR2(100),
   WUserContact         VARCHAR2(100),
   WUserTel             VARCHAR2(100),
   WUserAddress         VARCHAR2(100),
   WUserLastRate        NUMBER(20,4),
   WUserChangeDate      DATE,
   WUserRate            NUMBER(20,4),
   WUserDesc            VARCHAR2(500),
   WUserRe          VARCHAR2(1000),
   constraint PK_WATERUSER primary key (WUserID)
);
===================================================================
                     模拟几条测试数据FlowHisID              FlowID             WUserID           FlowHisValue          FlowHisTime          
  1                      22                10                  0                   29-1月 -10 
  2                      22                10                  0                   29-1月 -10 
  3                      22                10                  0                   29-1月 -10  
  4                      22                10                  0                   29-1月 -10 
  5                      22                10                  0                   29-1月 -10 
  6                      22                10                  180                 29-1月 -10 
  7                      22                10                  128                 29-1月 -10 
  8                      22                10                  183.1               29-1月 -10 
  9                      22                10                   0                  29-1月 -10 
  10                     22                10                   0                  29-1月 -10 
  11                     22                10                   192                29-1月 -10 
  12                     22                10                   0                  29-1月 -10 
  13                     22                10                   0                  29-1月 -10 
  512                    22                10                   0                  29-1月 -10 
  513                    22                10                   181                30-1月 -10 
  514                    22                10                   112                30-1月 -10 
  515                    22                 10                  0                  30-1月 -10 
  516                    22                 10                  0                  30-1月 -10 
  517                    22                 10                  0                  30-1月 -10 
  518                    22                 10                  0                  30-1月 -10 
  519                    22                 10                  0                  30-1月 -10 
  520                    22                 10                  0                  30-1月 -10 
  521                    22                 10                  0                  30-1月 -10 
  522                    22                 10                  0                  30-1月 -10 
  523                    22                 10                  0                  30-1月 -10 
  524                    22                 10                  0                  30-1月 -10 
  525                    22                 10                  0                  30-1月 -10 
  526                    22                 10                  0                  30-1月 -10 统计出的结果
          
          WUserID           
          (用户 )    月用时/小时     月供水次数     月流量      水费
           
           10           400             3           29010     290100 月供水次数 0----0 为一次
 在Flowhisvalue 字段中按时间段找出0开始0结束的数看能分几组

Answer 3

呵呵,我明白楼主的意思了,由0跳变到>0的时候算一次供水\
数据:
FLOWHISID FLOWID WUSERID FLOWHISVALUE FLOWHISTOTALVALVE FLOWHISTIME
1 1 22 10.0000 0.0000 2010-02-01
2 2 22 10.0000 0.0000 2010-02-01
3 3 22 10.0000 21.5000 2010-02-01
4 4 22 10.0000 180.000 2010-02-01
5 5 22 10.0000 0.0000 2010-02-01
6 6 22 10.0000 20.0000 2010-02-01
7 7 22 10.0000 0.0000 2010-02-01
8 8 22 10.0000 0.0000 2010-02-01
9 9 22 10.0000 59.8000 2010-02-01select a.wuserid,
(select count(*) from (
select t.flowid,t.flowhistotalvalve pir_value ,lead(t.flowhistotalvalve) over(order by t.flowid) next_value from flowhistory t
)
where pir_value-next_value<0
and pir_value=0) as 供水次数,
sum(a.flowhistotalvalve)
from flowhistory a
group by a.wuserid结果:
WUSERID 供水次数 月流量
22 3 281.3