本帖最后由 chengccy2010 于 2015-6-15 10:45 编辑
需求:求最近的连续次数;
例子:create tableTMP ASSELECT LEVEL AS L,'A' AS COL FROM DUAL WHERE LEVEL NOT IN(999,233)CONNECT BY LEVEL 复制代码查询A,B 在 L=1233566 已经连续多少次了?
我写的SQL,但是感觉效率太低了,因为业务数据远不止列子这点... 求大神们支招SELECT COL, 1233566 - MAX(L) + 1FROM (SELECT T.*,
NVL((L - LAG(L) OVER(PARTITION BY COL ORDER BY L)), 0) AS L1 FROM TMP T WHERET.L 复制代码
|
