求SQL语句的最佳方案

Question

create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'

Answer 1

要求结果：
000000005     1          2007-01-16 00:00:00.000
000000006     2          2008-01-17 00:00:00.000说明：统计日期小于当前日期的，根据DM_Code不同，取日期最近的一条！

Answer 2

select a.* from tb a,
(select DM_Code , max(DM_InureDate) from tb group by DM_Code) b
where a.DM_Code = b.DM_Code and a.DM_InureDate = b.DM_InureDate

Answer 3

select t1.*
from tb_SD_DocMainInfo t1
inner join (
select DM_Code, max(DM_InureDate) as DM_InureDate from tb_SD_DocMainInfo where convert(varchar(10), DM_InureDate, 121)<=convert(varchar(10), getdate(), 121) group by DM_Code
)t2 on t1.DM_Code=t2.DM_Code and t1.DM_InureDate=t2.DM_InureDate

Answer 4

create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'select a.* from tb_SD_DocMainInfo a,
(select DM_Code , max(DM_InureDate) DM_InureDate from tb_SD_DocMainInfo group by DM_Code) b
where a.DM_Code = b.DM_Code and a.DM_InureDate = b.DM_InureDate
order by a.DM_Code
drop table tb_SD_DocMainInfo/*
DM_DocumentID DM_Code    DM_InureDate                                           
------------- ---------- ------------------------------------------------------ 
000000001     1          2008-01-01 00:00:00.000
000000006     2          2008-01-17 00:00:00.000（所影响的行数为 2 行）*/

Answer 5

--另一种.create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'select * 
from tb_SD_DocMainInfo a 
where DM_InureDate in (select max(DM_InureDate) from tb_SD_DocMainInfo where DM_Code=a.DM_Code)
order by DM_Codedrop table tb_SD_DocMainInfo/*
DM_DocumentID DM_Code    DM_InureDate                                           
------------- ---------- ------------------------------------------------------ 
000000001     1          2008-01-01 00:00:00.000
000000006     2          2008-01-17 00:00:00.000（所影响的行数为 2 行）*/

Answer 6

取DM_Code为1、2的DM_InureDate 时间小于当前日期且为最大的记录。
当然DM_Code不确定有多少个！

Answer 7

select dm_documentid,dm_code,dm_inuredate 
from tb_sd_docmaininfo
where dm_inuredate > getdate()
group by dm_documentid,dm_code,dm_inuredate 
having max(dm_inuredate)

Answer 8

说明：统计日期小于当前日期的，根据DM_Code不同，取日期最近的一条！
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'哪条对?

Answer 9

create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'select * 
from tb_SD_DocMainInfo a 
where DM_InureDate in (select max(DM_InureDate) from tb_SD_DocMainInfo where DM_Code=a.DM_Code and DM_InureDate < getdate())
order by DM_Codedrop table tb_SD_DocMainInfo/*
DM_DocumentID DM_Code    DM_InureDate                                           
------------- ---------- ------------------------------------------------------ 
000000005     1          2007-01-16 00:00:00.000
000000003     2          2007-01-01 00:00:00.000（所影响的行数为 2 行）*/

Answer 10

create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'select a.* from tb_SD_DocMainInfo a,
(select DM_Code , max(DM_InureDate) DM_InureDate from tb_SD_DocMainInfo where DM_InureDate < getdate() group by DM_Code) b
where a.DM_Code = b.DM_Code and a.DM_InureDate = b.DM_InureDate 
order by a.DM_Code
drop table tb_SD_DocMainInfo
/*
DM_DocumentID DM_Code    DM_InureDate                                           
------------- ---------- ------------------------------------------------------ 
000000005     1          2007-01-16 00:00:00.000
000000003     2          2007-01-01 00:00:00.000（所影响的行数为 2 行）*/

Answer 11

create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2007-01-17'select * 
from tb_SD_DocMainInfo a 
where DM_InureDate in (select max(DM_InureDate) from tb_SD_DocMainInfo where DM_Code=a.DM_Code and DM_InureDate < getdate())
order by DM_Codedrop table tb_SD_DocMainInfo
(6 行受影响)
DM_DocumentID DM_Code    DM_InureDate
------------- ---------- -----------------------
000000005     1          2007-01-16 00:00:00.000
000000006     2          2007-01-17 00:00:00.000(2 行受影响)

Answer 12

declare @Test table(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert @Test
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'--求最大的日期：
select * from @Test a where DM_DocumentID = (select top 1 DM_DocumentID from @Test where DM_Code = a.DM_Code order by DM_InureDate desc)/*
DM_DocumentID DM_Code DM_InureDate
000000001 1 2008-01-01 00:00:00.000
000000006 2 2008-01-17 00:00:00.000
*/--求小于当前日期最大的日期：
select * from @Test a where DM_DocumentID = (select top 1 DM_DocumentID from @Test where DM_InureDate < getdate() and DM_Code = a.DM_Code order by DM_InureDate desc)/*
DM_DocumentID DM_Code DM_InureDate
000000005 1 2007-01-16 00:00:00.000
000000003 2 2007-01-01 00:00:00.000
*/
/*
都不是你要求的结果，你的结果是不是写错了：要求结果：
000000005 1 2007-01-16 00:00:00.000
000000006 2 2008-01-17 00:00:00.000
说明：统计日期小于当前日期的，根据DM_Code不同，取日期最近的一条！
*/

Answer 13

declare @t datetime
set @t='2008-02-07'
select a.* from tb_SD_DocMainInfo a inner join
(select dm_code,max(DM_InureDate) DM_InureDate from tb_SD_DocMainInfo where DM_InureDate<@t group by dm_code
) b on a.DM_Code=b.DM_Code and a.DM_InureDate=b.DM_InureDate

Answer 14

create table tb_SD_DocMainInfo(DM_DocumentID varchar(9),DM_Code varchar(10),DM_InureDate datetime)
insert tb_SD_DocMainInfo
select '000000001','1','2008-01-01'
union all
select '000000002','1','2007-01-01'
union all
select '000000005','1','2007-01-16'
union all
select '000000003','2','2007-01-01'
union all
select '000000004','2','2008-01-01'
union all
select '000000006','2','2008-01-17'
declare @t datetime
set @t='2008-02-07'
select a.* from tb_SD_DocMainInfo a inner join
(select dm_code,max(DM_InureDate) DM_InureDate from tb_SD_DocMainInfo where DM_InureDate<@t group by dm_code
) b on a.DM_Code=b.DM_Code and a.DM_InureDate=b.DM_InureDate