几个sql题目，可能对学习sql有用处，感兴趣的朋友也可以试试看能不能写出来

[tangtangno1]

经沧海(qq179889551)出的sql题目(纯属原创，若有转贴请注明出处)
感兴趣的朋友可以相互交流，我不提供答案，若有疑问可以通过我的qq询问，但我只会给出sql写法的思路，直接给出答案可能你就失去了学习提高的机会

1、无中生有
表内有若干记录，要求写出这样一个查询语句：对于给定的条件，若表内存在相应的记录，要求查询结果是空；若表内不存在相应的记录，要求查询结果非空。
提示：
count(*)=0是一条记录，记录的内容是0，这不是空；count(*)=2也是一条记录，记录的内容是2，也不是空
例如有表t的列c是整数类型，表内记录举例如下：
select * from t;
c
------
1
3
------
共有2条记录

若给定的条件是1，要求查询的结果是空，也就是一条记录也查不出来；若给定的条件是2，要求查询的结果是非空，查询的结果可以是任意条的任何记录，只要结果不是空就可以。


2、波动率分析
现有居民用电情况表结构如下：
表名：yd
列名：yh(字符类型，记录用户名称)，ssny(日期时间类型，记录用户电费的所属年月)，df(数值类型，记录用户ssny所属年月的电费金额)
表内记录举例如下：
select * from yd;
yh ssny df
-------------------
张三 200501 100.00
张三 200502 200.00
张三 200503 300.00
李四 200501 100.00
李四 200502 200.00
李四 200503 50.00
-------------------
共有6条记录

要求写出这样一个查询语句：查询出所有用户当月电费比上月电费波动大于50%的用电情况记录。波动率公式：(本月电费 - 上月电费)/上月电费
查询结果举例如下：
yh ssny df sydf(上月电费) bdl(波动率)
---------------------------------------------------------
张三 200502 200.00 100.00 |(200-100)|/100
李四 200502 200.00 100.00 |(200-100)|/100
李四 200503 50.00 200.00 |( 50-200)|/200

提示：
日期时间相关函数(oracle语法)：add_months(日期时间,整数数值)
200502=add_months(200501,1)
200501=add_months(200503,-2)
也可是使用其他数据库系统相关日期时间函数，用的时候请注释说明


3、分组后的组内排序号
现有表t记录举例如下：
select * from t;
a b
-----
a1 c
a1 d
b2 g
b2 f
b2 e
------
共有5条记录
要求写出这样一个查询语句：查询表t的所有记录，查询结果中增加按照a分组后按照b排序的组内排序号。

查询结果举例如下：
a b xh
--------
a1 c 1
a1 d 2
b2 e 1
b2 f 2
b2 g 3
--------


4、qq号码回收
qq号码本来是从10000不断加1增长的，但是由于各种原因，造成qq号码中有断号现象，腾讯老板马化腾希望找出这些不存在号码，以便回收卖钱。qq号码表名是t，表内记录举例如下：
select * from txqq;
qq
----------
10000
10001
...
10000000
10000002 (缺少10000001这个号码)
...
300000000
500000000 (缺少300000001~499999999之间的号码)
...
----------

要求：请分析该需求，并说明如何用一个sql查询实现的思路

[zsfww1205]

第一个：select * from 表名 where
(select count(*) from 表名 condition)=0

[Latnok Han]

第一个看不太懂？！
第二个我的方法如下：
select a.yh,
a.ssny,
a.df,
a.sydf,
(a.df-a.sydf)/a.sydf bdl
from (
select yh,
to_char(ssny, 'yyyymm') ssny,
df ,
( select b.df
from yd b
where add_months(b.ssny, 1) = yd.ssny
and b.yh = yd.yh
) sydf
from yd
) a
where a.sydf is not null

[MIS_ECSM]

NO.1 select * from 表名 where
rownum = 1
and exists
(select * from 表名 where condition)

[beckhambobo]

问题1:
select 1 from dual where (select decode(count(1),0,0,1) from table_name where 条件)=0

[MIS_ECSM]

4.建一新表 y ,hm字段內容從start QQ 到 end QQ
select hm from y
where hm not in
(select qq from txqq)

[Latnok Han]

select a,
b,
rank() over(partition by a order by b) xh
from yd

[Latnok Han]

第一个:
select t.yh,
t.ssny,
t.df,
t.sydf,
(t.df-t.sydf)/t.sydf bdl
from (
select yh,
ssny,
df,
lag(df, 1, 0) over(partition by yh order by ssny) sydf
from yd
) t
where t.sydf != 0

[Latnok Han]

弄错了!
2005-04-13 10:47:00 的是第三个.
2005-04-13 10:57:00 的是第二个.
第一个看不太懂.

[maxtool]

进来学习~~~

[Latnok Han]

第四个:
select t.qq2+1 from_num, t.qq1-1 to_num
from (
select qq qq1,
lag(qq, 1, null) over(order by qq) qq2
from qq
) t
where t.qq1 - t.qq2 > 1

[tangtangno1]

先把原来的题解思路贴出来吧

题解思路：

1、先回顾一下select的语法说明：
select 列 from 表 where 条件;
循环表的每一条记录，对于本次循环的当前记录，如果条件为真就选出当前记录，否则继续。
在平时的写法中，一般条件包括了表的列，但select的语法并没有要求条件必须用到当前记录的某个列。这是第一个题目的关键所在；然后利用有符合条件的记录时count(*)>0，没有符合条件的记录时count(*)=0，由此构造where条件就可以得出符合题目要求的答案了。
这个题目有点生搬硬造，没有实际用处也就可能不好理解。这个题目的目的主要是想让学习者不拘泥于常规思路，灵活使用条件。

2、这是最简单的一个了，就是一个连续相关的两条记录的运算。注意不要把没有前驱记录或者前驱记录的df=0的记录选出来就可以了。

3、题目虽然叫做 分组后的组内排序号 ，但是写起来却没有分组group的语法出现，直接把例子给出来一看就明白了
select a,b,(select count(*) from t where a=t1.a and b<=t1.b) xh
from t t1
order by a,b;

4、这个题目也包含 无中生有 的意思，问题虽然是要求找出不存在的那些记录，但分析此类需求后可以知道，如果有 300000000 ~ 500000000 这样的大量连续缺号的情况出现，一是不可能一条一条记录都列出来，二是列出来也无法查看没有什么意义；分析结论是，如果把这些出现断号的范围能够列出来，也就是相当于找到了这些不存在号。那么这些断号的范围又应该是什么呢？显然就是那些存在号码范围的“非集”，也就是把存在号码的范围上下加减一个数组织起来，就是不存在号码的范围。剩下的就是怎么具体实现sql了，下边是我练习写的一个实现：
select a from a;
----
1
2
3
7
8
9
----
共有6条记录，其中缺少4-6，下边的写法用到了oracle的rownum伪列和dual伪表，用来排列组织范围的上下限以使结果看起来清晰明了；如果不用oracle的特定语法也是能实现的，只不过结果看起来可能不是那么直观明了。

select start_a,end_a from
(select rownum r,end_a from
(select a - 1 end_a from a t1 where not exists(select * from a where t1.a - 1=a)
union
select 0+null end_a from dual
order by end_a)) t1,
(select r,start_a from
(select rownum r,0+null start_a from dual)
union
select r,start_a from
(select rownum+1 r,a + 1 start_a from a t1 where not exists(select * from a where t1.a + 1=a) order by start_a)
order by r) t2
where t1.r=t2.r;

[tangtangno1]

to ： nebulaly(极高明而道中庸)
1.看不懂
2.分析函数LAG
3.分析函数RANK
4.用rownum获得一个连续的整数数列

一看这回贴就知道是高手，不过我仅仅知道oracle几个常用的函数，上边写的LAG和RANK还是头一次见，以后有时间我也多看看oracle这些功能强大的函数。你最后一个也提到了用rownum，但我理解有限，感觉应该和我写的不是一个思路，不知道可否把你的思路写出个具体实现来，以方便观摩学习：）

to ： zsfww1205(流芳百世)
正确

to ： MIS_ECSM(雖然起名字不要錢﹐但是混在社區﹐昵稱不宜太長。)
第一个答案不符合题目要求
第四个也不符合题目要求

to ：beckhambobo(beckham)
正确

to ： maxtool(≮From NingBoo≯)
还是csdn高手多，在别处贴的这几个题目都没有反应，呵呵，共同学习

to ： fosking(寒羽良天)
从来没有用过oracle的分析语法函数，不好评价你写的答案，但是感谢你的支持！



工作几年来，对于oracle仅仅知道dual、rownum、以及几个类型转换和日期时间函数，头一次来这里发贴子没想到有这么多高手支持，知道了还有那么多功能强大的分析函数，在此非常感谢各位！
对于数据库sql的学习，我的体会一个是要深入：灵活掌握标准通用sql的运用；一个是要广度，就是多掌握不同数据库的特定语法和函数。这样就可以根据工作的实际情况进行灵活运用。由于特定工作的关系，我只是对前者熟悉一些，对后者的了解实在是有限；有时候为了移植方便、负载平衡、优化效率等原因，必须有所限制，这也有不同的开发风格所至。希望以后在这里和大家一起向高手们多学习请教！

[tangtangno1]

to nebulaly(极高明而道中庸)

select * from (
select rownum as r from all_objects a,all_objects b where rownum<(select max(object_id) from all_objects)) t where
t.r>(select min(object_id) from all_objects) and
not exists(select * from all_objects where object_id=t.r)

以上语句以拜读，非常感谢！但发现有这么个问题，你是采用了利用表自身连接，可以产生表中记录总数的笛卡尔乘积条记录的方法产生一个连续序列，这样确实可以把表内不存在的数值号码选取出来，但这必须满足一个条件：
记录总数*记录总数>=记录中的最大数值
否则遇到跳号严重的极端情况或者号码初始值就比较大时，例如表里只有两条记录：
10000
20000
这样就无能为力了，是不是呢？

[tangtangno1]

to newjq(流金岁月)

对于用一个sql解决包括1和4此类无中生有的问题，总是没有最完美的方案，这几个题目只是用作开阔视野思路以学习数据库sql，如果是实际的应用，可能会视具体情况综合采用各种技术来实现，希望共同学习进步！


再次感谢一下nebulaly(极高明而道中庸)的解答
不是我故意吹毛求疵给你的解决方案找毛病哈，只是纯粹的技术探讨，呵呵

[nebulaly]

那就搞个大一点的连接好了：）

select rownum as r from all_objects a,all_objects b,all_objects c

或者rownum+select min(object_id) from all_objects as r 好了

手段是多样的么

[coolice_2004]

xue xi

[nebulaly]

to楼主：
正好昨天回答过别人的问题http://community.csdn.net/Expert/topic/3932/3932425.xml?temp=.0920679

以查出all_objects中不连续的object_id为例：

select * from (
select rownum as r from all_objects a,all_objects b where rownum<(select max(object_id) from all_objects)) t where
t.r>(select min(object_id) from all_objects) and
not exists(select * from all_objects where object_id=t.r)

[tangtangno1]

对，语法是死的，人是活的，哈哈

[newjq]

各位高手们：干我们这一行的，不能不考虑特例啊！
绝对挑战：
对于第一题，要考虑包含表为空表的情况，如何实现？

[jiazhij]

目前通过大家的帮助,我已经建立了查断号的视图,但是没有实现分组，大家来看看怎么在这个基础上实现分组.
CREATE VIEW "table"."VIEW_USENUM" AS select * from (select rownum as r from AC_WASTEBOOK a,AC_WASTEBOOK

b where rownum<(select max(usenum) from AC_WASTEBOOK)) t wheret.r>(select min(usenum) from AC_WASTEBOOK) and

not exists(select * from AC_WASTEBOOK where usenum=t.r) WITH READ ONLY