查询，以获得最新的日期时间为2列单独

问题描述：

create table Orders 
(
city varchar(5) 
,orderid int 
,OrderedDateTime datetime 
,ThresholdDatetime datetime 
) 

insert into Orders values ('Texas',23,'2017-08-24 12:20:56.560','2017-08-24 20:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-24 13:20:45.560','2017-08-24 22:20:23.000') 
insert into Orders values ('Texas',23,'2017-08-24 16:20:45.560','2017-08-24 20:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-24 17:20:23.560','2017-08-24 22:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-23 12:20:23.560','2017-08-23 14:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-23 13:20:23.560','2017-08-23 21:20:23.000') 
insert into Orders values ('Texas',23,'2017-08-23 16:20:23.560','2017-08-23 20:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-23 18:20:23.560','2017-08-23 20:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-22 12:20:23.560','2017-08-22 14:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-22 13:20:23.560','2017-08-22 21:20:23.000') 
insert into Orders values ('Texas',23,'2017-08-22 16:20:23.560','2017-08-22 20:00:23.000') 
insert into Orders values ('Texas',23,'2017-08-22 19:20:23.560','2017-08-22 20:00:23.000')

] 2 查询，以获得最新的日期时间为2列单独

要求是让从“orderedDateTime”列最新datetime和最新的日期时间从“ThresholdDateTime”一栏，应返回一个记录每个如输出图像中所述。我已经有一个解决方案，但求比我更好的答案，因为我觉得我的查询是有性能问题

select city,orderid, max(OrderedDateTime) as MaxOrderedDateTime ,max(ThresholdDatetime) as MaxThresholdTime 
,day(ThresholdDatetime) as [UniqueDay] 
from dbo.Orders 
where OrderedDateTime<=ThresholdDatetime 
and convert(date,OrderedDateTime)=convert(date,ThresholdDatetime) 
group by city,orderid,day(ThresholdDatetime)

为什么你认为你查询有性能问题？您的查询似乎正确地返回您正在查找的数据。 –

您是否收到任何类型的错误？ – kazzi

如果您的数据超过1个月，则该查询将返回错误的输出，因为日期的输出（ThresholdDatetime）将在30天后重复。因此，您需要根据您拥有的数据量，在分组依据子句中选择Month（ThresholdDatetime）和Year（ThresholdDatetime）。 –

答

尝试编码这样的...

SELECT 
    o.city, 
    o.orderid, 
    MaxOrderedDateTime = MAX(o.OrderedDateTime), 
    MaxThresholdTime = MAX(o.ThresholdDatetime) 
FROM 
    #Orders o 
GROUP BY 
    o.city, 
    o.orderid, 
    CAST(o.OrderedDateTime AS DATE);

查询，以获得最新的日期时间为2列单独

相关推荐