SELECT DISTINCT 
    at.AccountId AS AccountId, 
    a.FirstName + ' ' + a.LastName AS [FullName],
    DATEDIFF(day, T.ArrivalDate, T.ReturnDate) AS LongestTrip,
    DATEDIFF(day, T.ArrivalDate, T.ReturnDate) AS ShortestTrip  
FROM 
    Accounts a
JOIN 
    AccountsTrips at ON a.Id = AT.AccountId
JOIN 
    Trips t ON T.Id = AT.TripId
WHERE 
    a.MiddleName IS NULL AND t.CancelDate IS NULL
ORDER BY 
    DATEDIFF(day, T.ArrivalDate, T.ReturnDate) DESC, ShortestTrip ASC

代码只按降序排列表 LongestTrip 而且在 ShortestTrip 样本数据！
找出每个帐户的最长和最短行程，以天为单位。将结果筛选到没有中间名和行程的帐户，这些帐户不会被取消（canceldate为空）。
将结果按最长旅行天数（降序）排序，然后按最短旅行天数（升序）排序。
示例

AccountId   FullName             LongestTrip    ShortestTrip
------------------------------------------------------------
    40      Winna Maisey             7              1
    56      Tillie Windress          7              1
    57      Eadith Gull              7              1
    66      Sargent Rockhall         7              1
    69      Jerome Flory             7              2
     …  …   …   …

table是空的--

CREATE TABLE Cities
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(20) NOT NULL,
    CountryCode CHAR(2) NOT NULL
)

CREATE TABLE Hotels
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(30) NOT NULL,
    CityId INT FOREIGN KEY REFERENCES Cities(Id) NOT NULL,
    EmployeeCount INT NOT NULL,
    BaseRate DECIMAL(10,2)
)

CREATE TABLE Rooms
(
    Id INT PRIMARY KEY IDENTITY,
    Price DECIMAL(10,2) NOT NULL,
    Type NVARCHAR(20) NOT NULL,
    Beds INT NOT NULL,
    HotelId INT FOREIGN KEY REFERENCES Hotels(Id) NOT NULL
)

CREATE TABLE Trips
(
    Id INT PRIMARY KEY IDENTITY,
    RoomId INT FOREIGN KEY REFERENCES Rooms(Id) NOT NULL,
    BookDate DATE NOT NULL, CHECK(BookDate<ArrivalDate),
    ArrivalDate DATE NOT NULL, CHECK(ArrivalDate<ReturnDate),
    ReturnDate DATE NOT NULL,
    CancelDate DATE
)

CREATE TABLE Accounts
(
    Id INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(20),
    LastName NVARCHAR(50) NOT NULL,
    CityId INT  NOT NULL,
    BirthDate DATE NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL

    CONSTRAINT FK_CityId FOREIGN KEY (CityId)
        REFERENCES Cities(Id)
)

CREATE TABLE AccountsTrips
(
    AccountId INT FOREIGN KEY REFERENCES Accounts(Id) NOT NULL,
    TripId INT FOREIGN KEY REFERENCES Trips(Id) NOT NULL,
    Luggage INT NOT NULL, CHECK(Luggage >= 0)
)