sql阶层语句或自循环

如：

有一个交易信息表，该表有：交易日期（年月日），交易时间（时分秒），柜员编号，网点号，操作员id等。测试期间，没有建主键。

查询：同一天，在同一个网点上，一个操作员，给三个以上的用户进行查询或转账，十分钟之内的记录。

解答：这查询条件看起来其实不难，但是里面涉及阶层，或者说嵌套查询。

刚开始用start with ... connect by (具体用法，自己百度吧)，比较复杂。

我用了一个临时表的方式。

如下：

第一步：

建表：

create table JIAOYI_TABLE
(
  T_DATE     VARCHAR2(10),--日期
  T_TIME     VARCHAR2(20),--时间
  WANGDIAN   VARCHAR2(50),--网点
  CAOZUOYUAN VARCHAR2(50) --操作员
);

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '092908', '11', '111');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '093021', '11', '111');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '093209', '11', '111');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '100907', '11', '111');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '110909', '22', '222');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '110924', '22', '222');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '111208', '22', '222');

insert into JIAOYI_TABLE (T_DATE, T_TIME, WANGDIAN, CAOZUOYUAN)
values ('2015-10-14', '112123', '22', '222');

第二步：

查询结果存入临时表1中

CREATE TABLE tmp_001 AS
SELECT T1.T_DATE,
       T1.WANGDIAN,
       T1.CAOZUOYUAN,
       T1.T_TIME,
       ROW_NUMBER() OVER(PARTITION BY T1.T_DATE, T1.WANGDIAN, T1.CAOZUOYUAN ORDER BY T1.T_TIME) AS RN
  FROM JIAOYI_TABLE T1,
       (SELECT T.T_DATE, T.WANGDIAN, T.CAOZUOYUAN
          FROM JIAOYI_TABLE T
         GROUP BY T.T_DATE, T.WANGDIAN, T.CAOZUOYUAN) T2
 WHERE T1.T_DATE = T2.T_DATE
   AND T1.WANGDIAN = T2.WANGDIAN
   AND T1.CAOZUOYUAN = T2.CAOZUOYUAN

第三步：

自关联，通过rn字段阶层相减时间字段值（转换成分钟），得到结果

时分秒字段值转换分钟的算法（目前我用最笨的，会公式的求指点一下，谢谢）：时*60+分+秒/60 = 分钟；

 SELECT T1.T_TIME,
        T2.T_TIME,
        (TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 1, 2)) * 60 + TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 3, 2)) +
        ROUND(TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 5, 2)) / 60, 1)) A,
        (TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 1, 2)) * 60 + TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 3, 2)) +
        ROUND(TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 5, 2)) / 60, 1)) B,
        (TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 1, 2)) * 60 + TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 3, 2)) +
        ROUND(TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 5, 2)) / 60, 1)) -
        (TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 1, 2)) * 60 + TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 3, 2)) +
        ROUND(TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 5, 2)) / 60, 1)) CHAJI
 /*第二个阶层:时*60+分+秒/60 - 第一个阶层:时*60+分+秒/60 */
   FROM TMP_001 T1, TMP_001 T2
  WHERE T1.T_DATE || T1.WANGDIAN || T1.CAOZUOYUAN =
        T2.T_DATE || T2.WANGDIAN || T2.CAOZUOYUAN
    AND T1.RN - 1 = T2.RN
    AND (TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 1, 2)) * 60 + TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 3, 2)) +
        ROUND(TO_NUMBER(SUBSTR(LPAD(T1.T_TIME, 6, '0'), 5, 2)) / 60, 1)) -
        (TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 1, 2)) * 60 +TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 3, 2)) +
        ROUND(TO_NUMBER(SUBSTR(LPAD(T2.T_TIME, 6, '0'), 5, 2)) / 60, 1)) < 10 /*差集小于十分钟*/
结果如图：

转载于:https://my.oschina.net/meSpace/blog/517299