CREATE TABLE [dbo].[shaixuan](
[A] [nvarchar](50) NULL,
[B] [nvarchar](50) NULL
)insert into shaixuan
select 1,12 union
select 5,17 union
select 2,10 union
select 4,11 union
select 3,15 union
select 8,13 union
select 6,19 union筛选条件:A列值增大时,B列值必须大于前边小的A列值对应的B列值想得到结果:
1 12
3 15
5 17
6 19
[A] [nvarchar](50) NULL,
[B] [nvarchar](50) NULL
)insert into shaixuan
select 1,12 union
select 5,17 union
select 2,10 union
select 4,11 union
select 3,15 union
select 8,13 union
select 6,19 union筛选条件:A列值增大时,B列值必须大于前边小的A列值对应的B列值想得到结果:
1 12
3 15
5 17
6 19
;WITH tempa AS (
SELECT * ,
ROW_NUMBER() OVER ( ORDER BY A ) AS num
FROM dbo.shaixuan
)
SELECT b.A ,
b.B
FROM tempa b
JOIN tempa a ON ( b.num - 1 = a.num
AND CONVERT(INT, b.B) > CONVERT(INT, a.B)
)
OR ( b.num = 1
AND a.num = 1
)
结果;
where a.B>b.B or a.A=1
with cte
as
(select *,ROW_NUMBER() over (order by A) as rn from shaixuan)
select A.A,A.B from cte A
where exists (select 1 from cte where rn=A.rn-1 and A.B>B)
or A.rn=1