无法在 SQL Server 中执行分组依据

桑托什

试图过滤细节

需要唯一具有最大BookingVersion分组依据的行shipmentNumber, shipmentDate,

select max(BookingVersion) BookingVersion, flightId, shipmentNumber, shipmentDate, FlightOffPoint, IIF(BookingStatusCode = 'XX', 'XX','SS') BookingStatusCode
from Exp_BookingDelta where flightid='625436' 
group by flightId, shipmentNumber, shipmentDate, FlightOffPoint, BookingStatusCode  
order by max(BookingVersion) desc

谁能解释一下，我错过了什么？

当前结果：

BookingVersion  flightId    shipmentNumber  shipmentDate    FlightOffPoint  BookingStatusCode
4               625436      61823647238     2019-12-04      LHR             XX
2               625436      61823647238     2019-12-04      LHR             SS
1               625436      61826374895     2019-12-06      LHR             XX
0               625436      61825364780     2019-11-26      LHR             SS
0               625436      61825364791     2019-11-26      LHR             SS
0               625436      61825364802     2019-11-26      LHR             SS
0               625436      61826374895     2019-12-06      LHR             SS

期待结果：

BookingVersion  flightId    shipmentNumber  shipmentDate    FlightOffPoint  BookingStatusCode
4               625436      61823647238     2019-12-04      LHR             XX
1               625436      61826374895     2019-12-06      LHR             XX
0               625436      61825364780     2019-11-26      LHR             SS
0               625436      61825364791     2019-11-26      LHR             SS
0               625436      61825364802     2019-11-26      LHR             SS

科技馆

请试试：

select 
   max(BookingVersion) BookingVersion, flightId, shipmentNumber, shipmentDate, FlightOffPoint, MAX(BookingStatusCode) BookingStatusCode
from
   Exp_BookingDelta where flightid='625436' 
group by flightId, shipmentNumber, shipmentDate, FlightOffPoint
order by max(BookingVersion) desc

要么

select 
    max(BookingVersion) BookingVersion, flightId, shipmentNumber, shipmentDate,
    IIF((select count(*) from Exp_BookingDelta b where b.FlightId=a.FlightId and b.ShimpmentNumber=a.ShimpmentNumber)>0, 'XX', 'SS') BookingStatusCode
from 
    Exp_BookingDelta where flightid='625436' 
group by flightId, shipmentNumber, shipmentDate, FlightOffPoint  
order by max(BookingVersion) desc

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。