在具有多列的访问数据库中排序 [英] Sorting in access database with multiple columns

问题描述

我正在尝试对访问中的数据库进行排序，但无法完成.首先是价格最低的城市和该城市的所有价格，然后是价格第二低的城市，以此类推.

Im trying to sort a database in access and I can't get it done. First the city with the lowest price and all the prices of that city, then the city with the second lowest price, etc.

这是示例数据库:

Country    City     Price    Departure_date     Return_date
Peru       Lima     360$     xxxx               xxxxx
Peru       Lima     420$     xxxx               xxxxx
Mexico     CMX      300$     xxxx               xxxxx
Mexico     CMX      400$     xxxx               xxxxx
Mexico     Cancun   350$     xxxx               xxxxx
Mexico     Cancun   500$     xxxx               xxxxx
Peru       Cusco    50$      xxxx               xxxxx
Peru       Cusco    60$      xxxx               xxxxx

必须这样排序:

Country    City     Price    Departure_date     Return_date
Peru       Cusco    50$      xxxx               xxxxx
Peru       Cusco    60$      xxxx               xxxxx
Mexico     CMX      300$     xxxx               xxxxx
Mexico     CMX      400$     xxxx               xxxxx
Mexico     Cancun   350$     xxxx               xxxxx
Mexico     Cancun   500$     xxxx               xxxxx
Peru       Lima     360$     xxxx               xxxxx
Peru       Lima     420$     xxxx               xxxxx

第二部分:

我需要它只使用具有最新日期的行.

I need it to only use the rows with latest date.

这是示例数据库:

Date           Country    City     Price    Departure_date     Return_date
05-06-2019     Peru       Lima     360$     xxxx               xxxxx
05-06-2019     Peru       Lima     420$     xxxx               xxxxx
05-06-2019     Mexico     CMX      300$     xxxx               xxxxx
05-06-2019     Mexico     CMX      400$     xxxx               xxxxx
05-06-2019     Mexico     Cancun   350$     xxxx               xxxxx
05-06-2019     Mexico     Cancun   500$     xxxx               xxxxx
05-06-2019     Peru       Cusco    50$      xxxx               xxxxx
05-06-2019     Peru       Cusco    60$      xxxx               xxxxx
04-06-2017     Mexico     Cancun   300$     xxxx               xxxxx
04-06-2017     Peru       Cusco    70$      xxxx               xxxxx
04-06-2017     Peru       Cusco    30$      xxxx               xxxxx

必须这样排序:

Date         Country    City     Price    Departure_date     Return_date
05-06-2019   Peru       Cusco    50$      xxxx               xxxxx
05-06-2019   Peru       Cusco    60$      xxxx               xxxxx
05-06-2019   Mexico     CMX      300$     xxxx               xxxxx
05-06-2019   Mexico     CMX      400$     xxxx               xxxxx
05-06-2019   Mexico     Cancun   350$     xxxx               xxxxx
05-06-2019   Mexico     Cancun   500$     xxxx               xxxxx
05-06-2019   Peru       Lima     360$     xxxx               xxxxx
05-06-2019   Peru       Lima     420$     xxxx               xxxxx

推荐答案

试试这个:

SELECT t1.*
FROM Table1 AS t1 INNER JOIN
     (SELECT Table1.City, Min(Table1.Price) AS min_price
        FROM Table1
        GROUP BY Table1.City) AS t2 
     ON t1.City = t2.City
ORDER BY t2.min_price, t1.City, t1.Price

为了容纳第二部分，包括 WHERE 子句:

To accommodate second part, include WHERE clause:

SELECT t1.*
FROM Table1 AS t1 INNER JOIN
     (SELECT Table1.City, Min(Table1.Price) AS min_price
        FROM Table1
        WHERE DateEnter=(SELECT Max(DateEnter) AS MaxDate FROM Table1)
        GROUP BY Table1.City) AS t2 
     ON t1.City = t2.City
WHERE DateEnter=(SELECT Max(DateEnter) AS MaxDate FROM Table1)
ORDER BY t2.min_price, t1.City, t1.Price;

这篇关于在具有多列的访问数据库中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！