一个小sql 看看谁的最高效，谢谢

2011-05-14 08:01:14 408 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

T100服务端接口实战：从注册到日志排查的全链路开发指南本文提供了一份T100服务端接口开发的全链路实战指南。从程序注册与框架签出开始，详细讲解了接口的数据结构设计、核心业务逻辑编写，到最终的部署上线与调用验证。文章重点分享了如何利用日志文件高效定位和排查接口问题，帮助开发者快速掌握T100接口开发的关键步骤与实用技巧。阅读详情

题目是一个SQL查询的：一个表有一个ID字段，是主键并且是递增的，现在需要一条SQL把这个表没有的ID查出来(注意是一条SQL语句)。

例如ID的记录如下：
ID
1
2
4
5
7
9
我们SQL需要把3，6，8这三个值查出来

...全文

分享一些基于微信小程序开发的小游戏源码小游戏小程序是运行在微信生态系统（或其他支持小程序的平台）中的轻量级应用程序，专为休闲游戏体验而设计的小程序子类别，小游戏小程序通常体积较小，加载速度快，用户无需等待长时间即可开始游戏。用户无需下载或安装单独的游戏应用程序，即可通过小程序入口直接进入游戏，同时小游戏小程序通常包含排行榜、分享功能等社交元素，可以增强游戏体验并促进用户参与。game.js：游戏页面的逻辑文件，包含页面数据、生命周期函数、事件处理函数等。game.wxml：游戏页面的结构文件，定义了页面的布局和组件。阅读详情

amdgaming

基础和管理 17,377 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

dut703 2011-05-16

打赏
举报

[Quote=引用 5 楼 tangren 的回复:]

SQL code
--下面是上面的执行时间测试
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition R……
[/Quote]
膜拜。。。

一夜相思愁 2011-05-15

打赏
举报

[Quote=引用 5 楼 tangren 的回复:]
SQL code
--下面是上面的执行时间测试
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edi……
[/Quote]

如此的牛X ~~~

amdgaming 2011-05-15

打赏
举报

厉害呵呵

asggadfgadfg 2011-05-15

打赏
举报

[Quote=引用 4 楼 tangren 的回复:]
SQL code
--1.MINUS
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;

--2.NOT EXISTS
SELECT *
FROM (SELECT LEVEL id
……
[/Quote]

厉害啊

304的的哥 2011-05-15

打赏
举报

[Quote=引用 4 楼 tangren 的回复:]

SQL code
--1.MINUS
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;

--2.NOT EXISTS
SELECT *
FROM (SELECT LEVEL id
FROM dua……
[/Quote]
膜拜一下大牛！

oarcle lag()分析函数

tigde 2011-05-15

打赏
举报

[Quote=引用 6 楼 tangren 的回复:]

更正：第四种CONNECT BY rownum < gap 应为 CONNECT BY rownum <= gap
[/Quote]
--1.MINUS
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;
用connect by level时是不是行数大于100了只会取到100行啊

javatemptation 2011-05-15

打赏
举报

liuyyuns 2011-05-15

打赏
举报

不错，简单的问题方法很多种。

tangren 2011-05-15

打赏
举报

更正：第四种CONNECT BY rownum < gap 应为 CONNECT BY rownum <= gap

tangren 2011-05-15

打赏
举报

--下面是上面的执行时间测试

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011



Copyright (c) 1982, 2005, Oracle.  All rights reserved.





连接到: 

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options



SQL> select * from tt;



        ID

----------

         1

         2

         4

         5

         7

         9

      3000

    100000



已选择8行。



SQL> set timing on;



SQL> --1.MINUS

SQL> select count(*) from (

  2  SELECT LEVEL id

  3    FROM dual

  4  CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)

  5  MINUS

  6  SELECT id FROM tt

  7  );



  COUNT(*)

----------

     99992



已用时间:  00: 00: 00.18



SQL> --2.NOT EXISTS

SQL> select count(*) from (

  2  SELECT *

  3    FROM (SELECT LEVEL id

  4            FROM dual

  5          CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a

  6   WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id)

  7   ORDER BY 1

  8  );



  COUNT(*)

----------

     99992



已用时间:  00: 00: 00.21



SQL> --3.NOT IN

SQL> select count(*) from (

  2  SELECT *

  3    FROM (SELECT LEVEL id

  4            FROM dual

  5          CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a

  6   WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id)

  7  );



  COUNT(*)

----------

     99992



已用时间:  00: 00: 02.67



SQL> --4.LAG 

SQL> select count(*) from (

  2  WITH t1 AS

  3   (SELECT s, e

  4      FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt)

  5     WHERE e - s >= 0)

  6  SELECT a.s + b.rn - 1 h

  7    FROM t1 a,

  8         (SELECT rownum rn

  9            FROM (SELECT MAX(e - s + 1) gap FROM t1)

 10          CONNECT BY rownum < gap) b

 11   WHERE a.s + b.rn - 1 <= a.e

 12   ORDER BY 1

 13  );



  COUNT(*)

----------

     99991



已用时间:  00: 00: 01.96

tangren 2011-05-15

打赏
举报

--1.MINUS

SELECT LEVEL id

  FROM dual

CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)

MINUS

SELECT id FROM tt;



--2.NOT EXISTS

SELECT *

  FROM (SELECT LEVEL id

          FROM dual

        CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a

 WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id)

 ORDER BY 1;



-3.NOT IN

SELECT *

  FROM (SELECT LEVEL id

          FROM dual

        CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a

 WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id);



-4.LAG

WITH t1 AS

 (SELECT s, e

    FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt)

   WHERE e - s >= 0)

SELECT a.s + b.rn - 1 h

  FROM t1 a,

       (SELECT rownum rn

          FROM (SELECT MAX(e - s + 1) gap FROM t1)

        CONNECT BY rownum < gap) b

 WHERE a.s + b.rn - 1 <= a.e

 ORDER BY 1;

304的的哥 2011-05-14

打赏
举报



SQL> with ta as(

  2       select level id from dual connect by level<=9

  3       )

  4  ,tb as(

  5       select 1 id from dual union all

  6       select 2 from dual union all

  7       select 4 from dual union all

  8       select 5 from dual union all

  9       select 7 from dual union all

 10       select 9 from dual)

 11  select id

 12  from ta

 13  where not exists(

 14        select id

 15        from tb

 16        where ta.id=tb.id)

 17  /



        ID

----------

         8

         3

         6

304的的哥 2011-05-14

打赏
举报



with ta as(

     select 1 id from dual union all

     select 2 from dual union all

     select 3 from dual union all

     select 4 from dual union all

     select 5 from dual union all

     select 6 from dual union all

     select 7 from dual union all

     select 8 from dual union all

     select 9 from dual)

,tb as(

     select 1 id from dual union all

     select 2 from dual union all

     select 4 from dual union all

     select 5 from dual union all

     select 7 from dual union all

     select 9 from dual)

select id

from ta

where not exists(

      select id

      from tb

      where ta.id=tb.id)

/

        ID

----------

         8

         3

         6

秋雨飘落 2011-05-14

打赏
举报

begin
for x in 1..1000000 loop
select id into mm from biao where id=x;
when mm is null
then insert into biao values(x);
end loop;

end;

一个小sql 看看 谁 的最高效，谢谢

相关推荐

一个小sql 看看谁的最高效，谢谢