34,590
社区成员
发帖
与我相关
我的任务
分享
;with t(id ,a ,b)
as
(
select 1, 1, 'a56'
union all select 2, 100, 'b68'
union all select 3, 200, 'e98'
union all select 4, 300, 'f15'
),
condition(id, 条件)
as
(
select 1, 60
union all select 2 , 75
union all select 3 , 250
union all select 4 , 310
)
select tt.id,tt.b
from
(
select t.*,
row_number() over(partition by c.id,c.条件 order by t.a desc) as rownum
from t
inner join condition c
on t.a <= c.条件
)tt
where rownum = 1
/*
id b
1 a56
1 a56
3 e98
4 f15
*/
CREATE TABLE temp (i int)
INSERT temp
SELECT 1 UNION
SELECT 100 UNION
SELECT 200 UNION
SELECT 300
declare @i int
set @i = 260
SELECT max(i) FROM temp
WHERE i<= @i
/*
200
*/
SELECT TOP(1) *
FROM dbo.tablename
WHERE ID <= @ID
ORDER BY ID DESC
CREATE TABLE #temp (i int)
INSERT #temp
SELECT 1 UNION
SELECT 100 UNION
SELECT 200 UNION
SELECT 300
SELECT TOP 1 * FROM #temp WHERE i<=10 ORDER BY i DESC
SELECT TOP 1 * FROM #temp WHERE i<=260 ORDER BY i DESC
create table 源数据
(id int,a int,b varchar(10))
insert into 源数据
select 1,1,'a56' union all
select 2,100,'b68' union all
select 3,200,'e98' union all
select 4,300,'f15'
create table 条件数据
(id int,条件 int)
insert into 条件数据
select 1,60 union all
select 2,75 union all
select 3,250 union all
select 4,310
select a.id,
(select top 1 b.[b]
from 源数据 b
where b.[a]<a.条件
order by b.id desc) 'bb'
from 条件数据 a
/*
id bb
----------- ----------
1 a56
2 a56
3 e98
4 f15
(4 row(s) affected)
*/