我正在使用SQL 2008，只是无法使该查询正常工作。我有一张桌子，上面有航班价格的详细信息。每条路线每天都有很多行，我想返回该路线每天的最低价格以及DateAdded（添加行的日期时间）。我快要到达了，因为我似乎已经设法获得了每天最低的价格，但是我似乎无法获得正确的日期补交。下面的代码似乎可以返回每天的最低价格，但是我想我需要某种联接才能返回DateAdded？
ALTER PROCEDURE [dbo].[FlightInfoLowestPricePerDay]
(
@AirportFrom varchar(5),
@AirportTo varchar(5)
)
AS
select DATEADD(dd, 0, DATEDIFF(dd, 0, TimeDeparture)) as FlightDate, MIN(Price) as MinPrice
from FlightInfo
where AirportFrom = @AirportFrom and AirportTo = @AirportTo
AND TimeDeparture > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
Group By DATEADD(dd, 0, DATEDIFF(dd, 0, TimeDeparture))
Order by FlightDate ASC
我已经尝试了下面的代码之类的东西，但是我还不完全了解联接，因此尽管已经确定我很想念一些简单的东西，但已经挣扎了一段时间。
ALTER PROCEDURE [dbo].[FlightInfoLowestPricePerDay]
(
@AirportFrom varchar(5),
@AirportTo varchar(5)
)
AS
select DATEADD(dd, 0, DATEDIFF(dd, 0, fi1.TimeDeparture)) as FlightDate, MIN(fi1.Price) as MinPrice, fi2.DateAdded
from FlightInfo
fi1 join FlightInfo fi2 on fi1.Price = fi2.Price
where fi1.AirportFrom = @AirportFrom and fi1.AirportTo = @AirportTo
AND fi1.TimeDeparture > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
Group By DATEADD(dd, 0, DATEDIFF(dd, 0, fi1.TimeDeparture))
Order by FlightDate ASC
最佳答案
添加fi2.Date已添加到分组依据
ALTER PROCEDURE [dbo].[FlightInfoLowestPricePerDay]
(
@AirportFrom varchar(5),
@AirportTo varchar(5)
)
AS
select DATEADD(dd, 0, DATEDIFF(dd, 0, fi1.TimeDeparture)) as FlightDate, MIN(fi1.Price) as MinPrice, Cast(Convert(varchar(20),fi2.DateAdded,101) as datetime) DateAdded
from FlightInfo
fi1 join FlightInfo fi2 on fi1.Price = fi2.Price
where fi1.AirportFrom = @AirportFrom and fi1.AirportTo = @AirportTo
AND fi1.TimeDeparture > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
Group By DATEADD(dd, 0, DATEDIFF(dd, 0, fi1.TimeDeparture)),Cast(Convert(varchar(20),fi2.DateAdded,101) as datetime)
Order by FlightDate ASC