书写sql案例

SELECTCOUNT(*)CNT_CS,SUM(CNT_HOUR)BB_SC FROM (

SELECTHOURDIF,COUNT(*)CNT_HOURFROM (

SELECTROUND((TO_DATE(ATIMEPOINT,'YYYY-MM-DD HH24:MI:SS')- TO_DATE(BTIMEPOINT,'YYYY-MM-DD HH24:MI:SS'))* 24 - ROWNUM)HOURDIF

FROM(

SELECTTIMEPOINT ATIMEPOINT,(SELECTTIMEPOINTfrom AIR_CITYHOURAQI_PUBLISH BWHERE ROWNUM = 1)BTIMEPOINT

FROMAIR_CITYHOURAQI_PUBLISH A

WHERETO_DATE(TIMEPOINT,'YYYY-MM-DD HH24:MI:SS')>= TO_DATE('2017-05-23 12:00:00','YYYY-MM-DD HH24:MI:SS')

ANDTO_DATE(TIMEPOINT,'YYYY-MM-DD HH24:MI:SS')<= TO_DATE('2017-05-24 6:00:00','YYYY-MM-DD HH24:MI:SS')

))GROUP BY HOURDIFHAVING COUNT(HOURDIF)> 1

)

上面的CNT_CS显示的是连续的次数,BB_SC显示的是时长(就是上面连续的时间减去后相加的结果)

---

获取内容和行号的语句:

SELECTHOURDIF,ROWNUMFROM (

SELECTHOURDIF FROM (

SELECTROUND((TO_DATE(ATIMEPOINT,'YYYY-MM-DD HH24:MI:SS')- TO_DATE(BTIMEPOINT,'YYYY-MM-DD HH24:MI:SS'))* 24 - ROWNUM)HOURDIF

FROM(

SELECTTIMEPOINT ATIMEPOINT,(SELECTTIMEPOINT FROM AIR_CITYHOURAQI_PUBLISH B WHERE ROWNUM = 1)BTIMEPOINT

FROMAIR_CITYHOURAQI_PUBLISH A

WHERETO_DATE(TIMEPOINT,'YYYY-MM-DD HH24:MI:SS')>= TO_DATE('2017-05-23 12:00:00','YYYY-MM-DD HH24:MI:SS')

ANDTO_DATE(TIMEPOINT,'YYYY-MM-DD HH24:MI:SS')<= TO_DATE('2017-05-24 6:00:00','YYYY-MM-DD HH24:MI:SS')

))

GROUPBY HOURDIF

HAVINGCOUNT(HOURDIF)> 1

ORDERBY HOURDIF

)

---

获取行号的两种方式

SELECTHOURDIF,ROW_NUMBER()OVER (ORDERBY C.HOURDIFASC)AS RN

FROM(

SELECTHOURDIF

FROM(

SELECTROUND((TO_DATE(ATIMEPOINT,'YYYY-MM-DD HH24:MI:SS')- TO_DATE(BTIMEPOINT,'YYYY-MM-DD HH24:MI:SS'))* 24 - ROWNUM)HOURDIF

FROM(

SELECTTIMEPOINT ATIMEPOINT,(SELECTTIMEPOINTFROM AIR_CITYHOURAQI_PUBLISH BWHERE ROWNUM = 1)BTIMEPOINT

FROMAIR_CITYHOURAQI_PUBLISH A

WHERETO_DATE(TIMEPOINT,'YYYY-MM-DD HH24:MI:SS')>= TO_DATE('2017-05-23 12:00:00','YYYY-MM-DD HH24:MI:SS')

ANDTO_DATE(TIMEPOINT,'YYYY-MM-DD HH24:MI:SS')<= TO_DATE('2017-05-24 6:00:00','YYYY-MM-DD HH24:MI:SS')

))

GROUPBY HOURDIF

HAVINGCOUNT(HOURDIF)> 1

ORDERBY HOURDIF

)C

ROW_NUMBER()OVER (PARTITIONBY C.HOURDIF ORDERBY C.HOURDIF ASC)AS RN

注意上面的PARTITIONBY C.HOURDIF的话就是先按照C.HOURDIF分组再进行ORDERBY排序这样RN列显示的都是1了