时间戳

首页 > 解决方案 > 在具有多列的访问数据库中排序

ms-access - 在具有多列的访问数据库中排序

问题描述

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

这是示例数据库：

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

第二部分：

我需要它只使用最新日期的行。

这是示例数据库：

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

标签： ms-access

解决方案

尝试这个：

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 子句： 

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;

推荐阅读