排序函数:over([分组语句] 排序子句 [desc] [asc])
排序子句:order by col1,col2
分组语句:partition by col1,col2
—查询连续3天便秘的病人数据
—单一病人查询
SELECT CJH,CJSJ,ZYH,
CJSJ - ROW_NUMBER() OVER(partition BY ZYH ORDER by CJSJ) AS LXSJ
FROM BQ_SMTZ WHERE XMH = 31 AND TZNR = ‘0’ AND ZYH = 771118 AND ZFBZ = 0
—批量查询
SELECT DISTINCT A2.ZYH
FROM (
SELECT B.ZYH,A.CJSJ - ROW_NUMBER() OVER(PARTITION BY A.ZYH ORDER by A.CJSJ) AS LXSJ
FROM BQ_SMTZ A,ZY_BRRY B
WHERE A.ZYH = B.ZYH
AND A.XMH = 31 AND A.TZNR = ‘0’ AND A.ZFBZ = 0
AND B.CYRQ >= ‘2018-9-1’ AND B.CYRQ < ‘2018-9-2’
) A2
GROUP BY A2.LXSJ,A2.ZYH
HAVING COUNT(*) >= 3