34,593
社区成员
发帖
与我相关
我的任务
分享
select 1 as id, '矿级' as jibie, '2010-01-01 08:30:25' as jcsj, '提升车间' as departname, '是' as sfcf
into #t
union select 2, '采区级', '2009-12-31 23:20:20', '提升车间', '是'
union select 3, '矿级', '2010-01-02 08:10:10', '排尾车间', '是'
union select 4, '采区级', '2010-01-03 10:30:30', '排尾车间', '是'
--select * from #t
select a.*
from
(
select * from #t where jibie='矿级'
) a join
(
select * from #t where jibie='采区级'
) b on a.departname=b.departname and a.sfcf=b.sfcf
where a.jcsj<b.jcsj
drop table #t
/*----------结果---------
id jibie jcsj departname sfcf
-------------------------
3 矿级 2010-01-02 08:10:10 排尾车间 是
------------------------*/
select *
from(
select id,jibie,jcsj,departname,sfcf,
LEAD(jibie)OVER(PARTITION BY departname ORDER BY jcsj) AS POST
from T
) as a where jibie='矿级' AND POST='采区及'