SQL获取小于或等于日期时间参数的最大记录

Question

例如，在 SQL 中，我试图获取最近的记录(小于或等于传递的 datetime 参数的最大记录)

例如，在我名为 serviceentry 的表中，有一列名为 ServiceDateTime

如何在我的 select 子句中应用 Max。所以在下面如果一个部分有一个服务条目，我想根据我的日期时间参数获取最大记录

select  
    Part.System as Subsystem, Part.ID as PartNumber, 
    Part.PartDescription, System.SystemFullName,
    COALESCE(ServiceEntryPart.PartRevisionNumber, Part.RevisionNumber) AS Revision
from 
    Part
left outer join 
    ServiceEntryPart on Part.ID = ServiceEntryPart.PartID
left outer join 
    ServiceEntry on ServiceEntryPart.ServiceEntryID = ServiceEntry.ID
left outer join 
    System on System.PlatformID = Part.platformid
where
    part.id = 670220
    and ServiceEntry.ServiceDateTime <= '1/1/2014'

Answer 1

简单的解决方案 - 只需要 1 条记录:

这就是诀窍:

select TOP 1 * from myTable where myDate <= @SomeDate order by se.ServiceDateTime desc

完整的解决方案 - 超过 1 个合适的记录:

以防万一您需要所有具有小于或等于给定参数的最大值的记录，请考虑以下内容，很糟糕，但还是明白了，解决方案示例:

示例:

CREATE TABLE Parts 
(
 id int auto_increment primary key,
 part_name varchar(10),
 buyer varchar(10)
);

CREATE TABLE Purchase 
    (
     id int auto_increment primary key,
     buyer varchar(10),
     purchase_date datetime
    );

INSERT INTO Parts (part_name, buyer)
VALUES ('hammer','mc'), ('wrench','larry'), ('screw','bill');

INSERT INTO Purchase (buyer, purchase_date)
VALUES ('mc','2012-01-01 22:00'), ('larry', '2014-01-01 22:00'), ('bill', '2012-01-01 22:00');


SET @my_date ='2013-01-01 22:00';
select pa.buyer as buyer , purchase_date, pa.part_name as part_name
      from Parts pa 
      left outer join Purchase pu on (pa.buyer = pu.buyer)
      inner join (
        select max(purchase_date) max_date from Purchase where purchase_date <= @my_date) md 
        on md.max_date = pu.purchase_date

Sql Fiddle Link