将SQL Server查询更改为纯ANSI SQL查询 [英] Changing SQL Server query to pure ANSI SQL query

问题描述

我正在使用SQL语法的数据库系统上工作.但是，我无法在下面的代码中使用交叉应用.有没有办法重写它而不适用?

I am working on a database system that uses SQL syntax. However I am unable to use the cross apply in the code below. Is there a way to rewrite this without applies?

 declare @rsBuildDetails table(dt datetime, build varchar(255), val varchar(255));

insert into @rsBuildDetails (dt, build, val) 
values ('20100101', '1', 'pass')
      ,('20100102', '2', 'fail')
      ,('20100103', '3', 'pass')
      ,('20100104', '4', 'fail')
      ,('20100105', '5', 'fail')
      ,('20100106', '6', 'fail')
      ,('20100107', '7', 'pass')
      ,('20100108', '8', 'pass')
      ,('20100109', '9', 'pass')
      ,('20100110', '10', 'fail');

with passed as
(
    select * 
    from @rsBuildDetails
    where val='pass'
)
select distinct 
    preFail.dt AS FailedDt,
    postFail.dt AS SecondFailedDt
from 
    passed
cross apply
    (select top 1 
         pre.*
     from 
         @rsBuildDetails as pre
     where 
         pre.dt < passed.dt 
         and pre.val = 'fail'
     order by 
         pre.dt desc) as preFail
cross apply
    (select top 1 
         post.*
     from 
         @rsBuildDetails as post
     where 
         post.dt > passed.dt 
         and post.val = 'fail'
     order by 
         post.dt asc) as postFail

推荐答案

您可能会尝试转移CTE，所有这些都适用于内联子选择:

You might try to transfer the CTE and all applies to inlined sub-selects:

declare @rsBuildDetails table(dt datetime, build varchar(255), val varchar(255));

insert into @rsBuildDetails (dt, build, val) values 
 ('20100101', '1', 'pass')
,('20100102', '2', 'fail')
,('20100103', '3', 'pass')
,('20100104', '4', 'fail')
,('20100105', '5', 'fail')
,('20100106', '6', 'fail')
,('20100107', '7', 'pass')
,('20100108', '8', 'pass')
,('20100109', '9', 'pass')
,('20100110', '10', 'fail');

select *
from
(
    select distinct
           (
            select top 1 pre.Dt
            from @rsBuildDetails as pre
            where pre.dt<passed.dt 
              and pre.val='fail'
            order by pre.dt desc
           ) as FailedDt
         ,(
            select top 1 post.Dt
            from @rsBuildDetails as post
            where post.dt>passed.dt 
              and post.val='fail'
            order by post.dt asc
          ) AS SecondFailedDt
    from 
    (
        select * 
        from @rsBuildDetails
        where val='pass'
    ) AS passed
) AS tbl
where tbl.FailedDt IS NOT NULL 
  AND tbl.SecondFailedDt IS NOT NULL

这篇关于将SQL Server查询更改为纯ANSI SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！