单条SQL语句实现复杂逻辑几例~~

answerlkh

多谢了 收益很多

gonglimin

xiexie

xinghun82

第二题不知这样是否更简单些
先找出后一行的数据，在找出后两行的数据，并与当前行进行比较，如果都相等说明该行的后两行数据都相等，再找出后两行数据
select  t.*
from
(select t.* ,rownum rn from tmp2 t) t
where t.rn in
(select t.rn
from
(select t.*,rownum as rn,decode(t.a,lead(t.a,1,0)over(order by rownum),1,0) as nrow ,decode(t.a,lead(t.a,2,0)over(order by rownum),1,0) as nnrow from tmp2 t) t
where t.nrow = 1 and t.nnrow = 1)
or  t.rn in
(select t.rn+1
from
(select t.*,rownum as rn,decode(t.a,lead(t.a,1,0)over(order by rownum),1,0) as nrow ,decode(t.a,lead(t.a,2,0)over(order by rownum),1,0) as nnrow from tmp2 t) t
where t.nrow = 1 and t.nnrow = 1)
or  t.rn in
(select t.rn+2
from
(select t.*,rownum as rn,decode(t.a,lead(t.a,1,0)over(order by rownum),1,0) as nrow ,decode(t.a,lead(t.a,2,0)over(order by rownum),1,0) as nnrow from tmp2 t) t
where t.nrow = 1 and t.nnrow = 1)

谢谢分享！

xinghun82

第三题楼主的解题思路只适用于月份，如果要求求解天数，这种思路就不太合适了，推荐这种方法：
将员工的start_date排序 ORDER BY start_date
添加一列 前一行的start_date 作为start_date_p 这样start_date >= start_date_p
添加一列 前n行的end_date中最大值作为end_date_p
这样就可以分为三种情况
start_date >= end_date_p 工作的天数是 end_date - start_date
end_date >= end_date_p AND start_date < end_date_p 工作的天数是 end_date - end_date_p
end_date <= end_date_p 工作的天数是 0

Hopewell_Go

谢谢

xiaoe_7

学习，学习

熊猫儿

三思家的bb好可爱。。。

xieyihn

THANKS

特种兵孙皓

很有意思。。。