日期:2014-05-17 浏览次数:20595 次
SELECT *
FROM (select *,ROW_NUMBER()OVER(PARTITION BY 名称 order by abs(1997-年份),年份) AS Row from tab )as a
WHERE row=1
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-20 10:45:28
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([名称] varchar(1),[年份] int)
insert [test]
select 'A',2012 union all
select 'A',2010 union all
select 'A',1995 union all
select 'B',2011 union all
select 'B',1990 union all
select 'C',1999 union all
select 'C',1997 union all
select 'C',1995 union all
select 'C',1991
go
declare @year int=2007
;with t
as(
select
*,
abs([年份]-@year) as Lens
from
test
where
[年份]<@year
)
select
[名称],
[年份]
from
t
where
Lens=(select min(Lens) from t a where a.名称=t.名称)
/*
名称 年份
---- -----------
A 1995
B 1990
C 1999
(3 行受影响)
*/
SELECT * FROM tb WHERE nianfen=(SELECT max(nianfen) FROM tb WHERE nianfen<1997)