求一个sql语句，oracle高手

发布网友发布时间：2022-04-20 11:53

共2个回答

热心网友时间：2023-12-24 20:32

CREATE TABLE test (
  A  INT
);

INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(5);

INSERT INTO test VALUES(7);
INSERT INTO test VALUES(8);
INSERT INTO test VALUES(9);
INSERT INTO test VALUES(10);

SELECT
  A,
  (SELECT MIN(a) FROM test t3
    WHERE t3.A > test.A
  AND NOT EXISTS( SELECT 1 FROM test t4 WHERE t4.A = t3.A + 1)) B
FROM
  test
WHERE
  NOT EXISTS( SELECT 1 FROM test t2 WHERE test.A = t2.A + 1);

查询结果：

SQL> SELECT

2 A,

3 (SELECT MIN(a) FROM test t3

4 WHERE t3.A > test.A

5 AND NOT EXISTS( SELECT 1 FROM test t4 WHERE t4.A = t3.A + 1)) B

6 FROM

7 test

8 WHERE

9 NOT EXISTS( SELECT 1 FROM test t2 WHERE test.A = t2.A + 1);

A B

---------- ----------

1 5

7 10

热心网友时间：2023-12-24 20:32

又是一个大神。。。信息给的太少了吧，生成的结果，有什么规律可循吗？