代码之家 › 专栏 › 技术社区 › Nathan W

挑战:SQL检查下一条记录与上一条记录

sql

Nathan W · 技术社区 · 14 年前

我有一些需要验证的数据,虽然我已经找到了一个在excel中工作的快速公式,但我想找到一些SQL也可以这样做,这样我就可以时不时地在数据库中使用它作为检查。

ACACIA ST (KLN) | 1073 | 1149 | FAIL! | 76
ACACIA ST (KLN) | 1073 | 1151 | FAIL! | 78
ACACIA ST (KLN) | 1150 | 1332 | FAIL! | 182
ACACIA ST (KLN) | 1151 | 1332 | FAIL! | 181
ACACIA ST (KLN) | 1606 | 2079 |  | 473
ACTIVITY ST (WRK) | 6 | 215 | WIN! | 209
ACTIVITY ST (WRK) | 215 | 227 | WIN! | 12
ACTIVITY ST (WRK) | 227 | 423 | WIN! | 196
ACTIVITY ST (WRK) | 423 | 549 | WIN! | 126
ACTIVITY ST (WRK) | 549 | 600 |  | 51
ADRIENNE CT (WMN) | 5 | 107 | WIN! | 102
ADRIENNE CT (WMN) | 107 | 122 |  | 15
AERODROME RD (LYB) | 0 | 140 | WIN! | 140
AERODROME RD (LYB) | 140 | 428 | WIN! | 288
AERODROME RD (LYB) | 428 | 716 | WIN! | 288
AERODROME RD (LYB) | 716 | 998 | WIN! | 282
AERODROME RD (LYB) | 998 | 1280 | WIN! | 282
AERODROME RD (LYB) | 1280 | 1566 | WIN! | 286
AERODROME RD (LYB) | 1566 | 1851 | WIN! | 285
AERODROME RD (LYB) | 1851 | 2136 | WIN! | 285
AERODROME RD (LYB) | 2136 | 2421 |  | 285

还有更多的数据,大约11000多行。

现在的想法是,每个路段(第一列)都有一个起点和终点链测长度(第二列和第三列)以及一个长度(最后一列),只要路段名称相同(当然按起点链测长度排序),终点链测长度就应该是下一个路段的起点链测长度。您可以看到,由于第一行的结束链测长度不等于下一行的开始链测长度,因此失败。

我对伪代码的错误尝试:

If EndChainage != NextStartChainge Where RoadSegment = NextRoadSegment Error

所以上表的结果是:

ACACIA ST (KLN) | 1073 | 1149 | FAIL! | 76
ACACIA ST (KLN) | 1073 | 1151 | FAIL! | 78
ACACIA ST (KLN) | 1150 | 1332 | FAIL! | 182
ACACIA ST (KLN) | 1151 | 1332 | FAIL! | 181

希望这有道理。我尝试过,但似乎只是让它工作,我的SQL技能有点缺乏。

2 回复 | 直到 14 年前

user359040 user359040 14 年前

假设不会有重复的行,下面应该是一个合理的DB不可知解决方案:

select street, startno, endno, 
case  (select coalesce(min(s2.startno),-1) from stackchallenge s2 
       where s1.street = s2.street and 
             s1.startno <= s2.startno and 
             s1.endno < s2.endno)
    when -1 then ' '
    when endno then 'WIN!'
    else 'FAIL!'
end as validated,
length
from stackchallenge s1 order by 1,2,3

Craig 14 年前

我的第一次尝试很简单,但如果你有相思ST的情况下,一个正确的片段不是在正确的顺序。

    select * from stackchallenge se
    where not exists (select * from stackchallenge 
where startchain = se.endchain and segment = se.segment)
    and exists (select * from stackchallenge 
where segment = se.segment and endchain > se.endchain)

     with segments (segment,startchain,endchain,lenchain,segnum)
        as (select segment,startchain,endchain,lenchain
    ,row_number() over (partition by segment order by startchain,endchain) segnum
                from stackchallenge)
        select s1.segment,s1.startchain,s1.endchain,s1.lenchain
            ,case when s1.endchain = s2.startchain then 'WIN!'
                when s1.endchain <> s2.startchain then 'FAIL!'
            else '' end result
        from segments s1
            left join segments s2 on s1.segment = s2.segment 
and s1.segnum + 1 = s2.segnum

下面是用于测试的表和数据,您需要直接使用SQLServer2008。如果您正在使用Insert-to-sql2005,请更改其语法。

create table stackchallenge (segment varchar(100),
    startchain int,
    endchain int,
    result varchar(100),
    lenchain int
)   

insert into stackchallenge (segment,startchain,endchain,result,lenchain)
values 
('ACACIA ST (KLN)' , 1073 , 1149 , 'FAIL!' , 76),
('ACACIA ST (KLN)' , 1073 , 1151 , 'FAIL!' , 78),
('ACACIA ST (KLN)' , 1150 , 1332 , 'FAIL!' , 182),
('ACACIA ST (KLN)' , 1151 , 1332 , 'FAIL!' , 181),
('ACACIA ST (KLN)' , 1606 , 2079 , '' , 473),
('ACTIVITY ST (WRK)' , 6 , 215 , 'WIN!' , 209),
('ACTIVITY ST (WRK)' , 215 , 227 , 'WIN!' , 12),
('ACTIVITY ST (WRK)' , 227 , 423 , 'WIN!' , 196),
('ACTIVITY ST (WRK)' , 423 , 549 , 'WIN!' , 126),
('ACTIVITY ST (WRK)' , 549 , 600 , '' , 51),
('ADRIENNE CT (WMN)' , 5 , 107 , 'WIN!' , 102),
('ADRIENNE CT (WMN)' , 107 , 122 , '' , 15),
('AERODROME RD (LYB)' , 0 , 140 , 'WIN!' , 140),
('AERODROME RD (LYB)' , 140 , 428 , 'WIN!' , 288),
('AERODROME RD (LYB)' , 428 , 716 , 'WIN!' , 288),
('AERODROME RD (LYB)' , 716 , 998 , 'WIN!' , 282),
('AERODROME RD (LYB)' , 998 , 1280 , 'WIN!' , 282),
('AERODROME RD (LYB)' , 1280 , 1566 , 'WIN!' , 286),
('AERODROME RD (LYB)' , 1566 , 1851 , 'WIN!' , 285),
('AERODROME RD (LYB)' , 1851 , 2136 , 'WIN!' , 285),
('AERODROME RD (LYB)' , 2136 , 2421 , '' , 285)