我有一个像这样的表格:
i1 i2
----------
1 a
1 b
1 c
1 d
2 x
3 y
4 a
4 b
4 c
我希望选择介于 1 c 和 4 a 之间的行。
结果应该如下所示:
1 c
1 d
2 x
3 y
4 a
我该怎么做?
我有一个像这样的表格:
i1 i2
----------
1 a
1 b
1 c
1 d
2 x
3 y
4 a
4 b
4 c
1 c
1 d
2 x
3 y
4 a
我该怎么做?
1c
和4a
的行号。 这些行号构成您要选择的范围。;WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS RowNumber, i1, i2
FROM yourTable
)
SELECT *
FROM cte t
WHERE t.RowNumber >= (SELECT RowNumber FROM cte WHERE i1=1 AND i2='c') AND
t.RowNumber <= (SELECT RowNumber FROM cte WHERE i1=4 AND i2='a')
where
子句就足够了,所以这个查询的做法过于冗长。 - Gordon Linoff我会这么做:
select t.*
from t
where (i1 > 1 or (i1 = 1 and i2 >= 'c')) and
(i1 < 4 or (i1 = 4 and i2 <= 'a'));
select i1, i2
from tbl
where (i1 > 1 and i1 < 4)
or (i1 = 1 and i2 >='c')
or (i1 = 4 and i2 <='a')
不是很美观的方法...但是
create procedure GetRangeBetween (@i11 int, @i12 char, @i21 int, @i22 char)
AS
BEGIN
if object_id ('tempdb..#Test') is not null drop table #Test
create table #Test (i1 int, i2 nvarchar(10), [Rank] int)
insert into #Test(i1, i2)
values
(1, 'a'), (1, 'b'), (1, 'c'),
(1, 'd'), (2, 'x'), (3, 'y'),
(4, 'a'), (4, 'b'), (4, 'c')
update #Test
set Rank = src.[srcRank]
from #Test t
join (select *, row_number() over (order by i1) [srcRank] from #Test) src
on t.i1 = src.i1 and t.i2 = src.i2
declare @Rank1 int = (select [Rank] from #Test where i1 = @i11 and i2 = @i12)
declare @Rank2 int = (select [Rank] from #Test where i1 = @i21 and i2 = @i22)
select i1, i2 from #Test
where (i1 between @i11 and @i21) and ([Rank] between @Rank1 and @Rank2)
END
接下来,您只需使用...来执行它。
execute GetRangeBetween 1, 'c', 4, 'a'
where (i1, i2) >= (1, 'c') and (i1, i2) <= (4, 'a')
,但我不知道SQL Server是否支持。 - user330315