select max(testdate) testdate from tb where testdate < '2007-11-01'
create table tb(id int, TestDate datetime) insert into tb values(1, '2007-09-17') insert into tb values(2, '2007-10-18') insert into tb values(3, '2007-11-01') insert into tb values(4, '2008-01-10') go --小于2007-11-01的最大日期 select max(testdate) testdate from tb where testdate < '2007-11-01' /* testdate ----------------------- 2007-10-18 00:00:00.000 (1 行受影响) */--小于2007-11-01所有日期 select * from tb where testdate < '2007-11-01' /* id TestDate ----------- ----------------------- 1 2007-09-17 00:00:00.000 2 2007-10-18 00:00:00.000(2 行受影响) */drop table tb
create table tb(id int, TestDate datetime) insert into tb values(1, '2007-09-17') insert into tb values(2, '2007-10-18') insert into tb values(3, '2007-11-01') insert into tb values(4, '2008-01-10') go --小于2007-11-01的最大日期 select max(testdate) testdate from tb where testdate < '2007-11-01' /* testdate ----------------------- 2007-10-18 00:00:00.000 (1 行受影响) */--小于2007-11-01的最大日期所有的数据 select * from tb where testdate in (select max(testdate) testdate from tb where testdate < '2007-11-01') /* id TestDate ----------- ----------------------- 2 2007-10-18 00:00:00.000 */--小于2007-11-01所有日期 select * from tb where testdate < '2007-11-01' /* id TestDate ----------- ----------------------- 1 2007-09-17 00:00:00.000 2 2007-10-18 00:00:00.000(2 行受影响) */drop table tb
declare @tb table (id int,date datetime) insert into @tb select 1,'2007-09-17' insert into @tb select 2,'2007-10-18' insert into @tb select 3,'2007-11-01' insert into @tb select 4,'2008-01-10' declare @dt datetime set @dt='2007-10-18' select * from ( select *,datediff(dd,date,@dt)as dt from @tb)b where abs(dt)=( select min(abs(dt)) from (select *,datediff(dd,date,@dt)as dt from @tb )a where dt<>0)3 2007-11-01 00:00:00.000 -14
如何取得最小于2007-11-01的日期(2007-10-18)? ------------------------ 貌似和题目的需求不同?如果是小于'2007-11-01'的日期里面的最大日期,乌龟兄的正解 select max(testdate) testdate from tb where testdate < '2007-11-01' 如果是所有日期,去掉MAX就可以了 select testdate testdate from tb where testdate < '2007-11-01'
create table tb(id int,TestDate datetime) insert into tb values(1,'2007-09-17') insert into tb values(2,'2007-10-18') insert into tb values(3,'2007-11-01') insert into tb values(4,'2008-01-10') go --1,小于2007-11-01的最大日期 select max(testdate) testdate from tb where testdate < '2007-11-01' --2, select top 1 * from tb where TestDate < '2007-11-01' order by TestDate desc --3 select * from tb a where not exists(select 1 from tb where testdate > a.testdate and testdate < '2007-11-01') and testdate < '2007-11-01'--4 select max(testdate) testdate from tb where testdate < '2007-11-01'drop table tb /* testdate ------------------------------------------------------ 2007-10-18 00:00:00.000(所影响的行数为 1 行)id TestDate ----------- ------------------------------------------------------ 2 2007-10-18 00:00:00.000(所影响的行数为 1 行)id TestDate ----------- ------------------------------------------------------ 2 2007-10-18 00:00:00.000(所影响的行数为 1 行)testdate ------------------------------------------------------ 2007-10-18 00:00:00.000(所影响的行数为 1 行)*/
insert into tb values(1, '2007-09-17')
insert into tb values(2, '2007-10-18')
insert into tb values(3, '2007-11-01')
insert into tb values(4, '2008-01-10')
go
--小于2007-11-01的最大日期
select max(testdate) testdate from tb where testdate < '2007-11-01'
/*
testdate
-----------------------
2007-10-18 00:00:00.000
(1 行受影响)
*/--小于2007-11-01所有日期
select * from tb where testdate < '2007-11-01'
/*
id TestDate
----------- -----------------------
1 2007-09-17 00:00:00.000
2 2007-10-18 00:00:00.000(2 行受影响)
*/drop table tb
insert into tb values(1, '2007-09-17')
insert into tb values(2, '2007-10-18')
insert into tb values(3, '2007-11-01')
insert into tb values(4, '2008-01-10')
go
--小于2007-11-01的最大日期
select max(testdate) testdate from tb where testdate < '2007-11-01'
/*
testdate
-----------------------
2007-10-18 00:00:00.000
(1 行受影响)
*/--小于2007-11-01的最大日期所有的数据
select * from tb where testdate in (select max(testdate) testdate from tb where testdate < '2007-11-01')
/*
id TestDate
----------- -----------------------
2 2007-10-18 00:00:00.000
*/--小于2007-11-01所有日期
select * from tb where testdate < '2007-11-01'
/*
id TestDate
----------- -----------------------
1 2007-09-17 00:00:00.000
2 2007-10-18 00:00:00.000(2 行受影响)
*/drop table tb
insert into @tb select 1,'2007-09-17'
insert into @tb select 2,'2007-10-18'
insert into @tb select 3,'2007-11-01'
insert into @tb select 4,'2008-01-10'
declare @dt datetime
set @dt='2007-10-18'
select * from (
select *,datediff(dd,date,@dt)as dt from @tb)b
where abs(dt)=(
select min(abs(dt))
from (select *,datediff(dd,date,@dt)as dt from @tb )a
where dt<>0)3 2007-11-01 00:00:00.000 -14
------------------------
貌似和题目的需求不同?如果是小于'2007-11-01'的日期里面的最大日期,乌龟兄的正解
select max(testdate) testdate from tb where testdate < '2007-11-01'
如果是所有日期,去掉MAX就可以了
select testdate testdate from tb where testdate < '2007-11-01'
insert into tb values(1,'2007-09-17')
insert into tb values(2,'2007-10-18')
insert into tb values(3,'2007-11-01')
insert into tb values(4,'2008-01-10')
go
--1,小于2007-11-01的最大日期
select max(testdate) testdate from tb where testdate < '2007-11-01'
--2,
select top 1 * from tb where TestDate < '2007-11-01' order by TestDate desc
--3
select *
from tb a
where not exists(select 1 from tb where testdate > a.testdate and testdate < '2007-11-01')
and testdate < '2007-11-01'--4
select max(testdate) testdate from tb where testdate < '2007-11-01'drop table tb
/*
testdate
------------------------------------------------------
2007-10-18 00:00:00.000(所影响的行数为 1 行)id TestDate
----------- ------------------------------------------------------
2 2007-10-18 00:00:00.000(所影响的行数为 1 行)id TestDate
----------- ------------------------------------------------------
2 2007-10-18 00:00:00.000(所影响的行数为 1 行)testdate
------------------------------------------------------
2007-10-18 00:00:00.000(所影响的行数为 1 行)*/