select deptno,ename,hiredate,sal,
coalesce(cast(sal-next_sal as char(10)),'N/A') as diff
from (
select e.deptno,
e.ename,
e.hiredate,
e.sal,
(select min(sal) from emp d
where d.deptno=e.deptno
and d.hiredate =
(select min(hiredate) from emp d
where e.deptno=d.deptno
and d.hiredate ＞ e.hiredate)) as next_sal
from emp e
) x
1.6.2. 使用标量子查询找出同一个部门里紧随当前员工之后入职的员工的HIREDATE
1.6.3. 使用了MIN(HIREDATE)来确保仅返回一个值
1.6.3.1. 即使同一天入职的员工不止一个人，也只会返回一个值
1.6.4. 另一个标量子查询来找出入职日期等于NEXT_HIRE的员工的工资
1.6.4.1. 使用MIN函数来确保只返回一个值
1.7. Oracle
1.7.1. sql
select deptno,ename,sal,hiredate,
lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
from (
select deptno,ename,sal,hiredate,
lead(sal)over(partition by deptno
order by hiredate) next_sal
from emp
)
create view v2
as
select a.*,
case
when (
select b.proj_id
from V b
where a.proj_start = b.proj_end
)
is not null then 0 else 1
end as flag
from V a
2.7.2.
select proj_grp,
min(proj_start) as proj_start,
max(proj_end) as proj_end
from (
select a.proj_id,a.proj_start,a.proj_end,
(select sum(b.flag)
from V2 b
where b.proj_id ＜= a.proj_id) as proj_grp
from V2 a
) x
group by proj_grp
2.8. Oracle
2.8.1. sql
select proj_grp, min(proj_start), max(proj_end)
from (
select proj_id,proj_start,proj_end,
sum(flag)over(order by proj_id) proj_grp
from (
select proj_id,proj_start,proj_end,
case when
lag(proj_end)over(order by proj_id) = proj_start
then 0 else 1
end flag
from V
)
)
group by proj_grp
3. 生成连续的数值
3.1. DB2
3.2. SQL Server
3.3. sql
with x (id)
as (
select 1
from t1
union all
select id+1
from x
where id+1 ＜= 10
)
select * from x
3.4. Oracle
3.4.1. sql
with x
as (
select level id
from dual
connect by level ＜= 10
)
select * from x
