MySQL 查询以获取每个分类的最新和最便宜的商品

Question

我有一个数据集，其中包含各种租车的价格。数据包括：

• 汽车类别（紧凑型、小型货车、SUV 等）、
• 租赁价格以及
• 收集数据的日期。

我需要查询这些数据以获得每类最便宜的价格，但我还需要从数据库中的多个结果中选择最新的价格，即使最新的价格比旧的价格更贵。

例如，我可能有这样的数据：

• 类别，价格，日期
• suv，100，今天
• suv，121，今天
• suv，49，3 天前
• 小型货车，89，昨天
• 小型货车，120，昨天
• 小型货车，100，昨天
• 小型货车，91， 3 天前
• 小型货车、77、3 天前
• 等

我想运行一个查询来获取以下数据：

• suv、100、今天
• 小型货车、89、昨天
• 等

如果我执行如下查询：

SELECT * FROM data
ORDER BY class, date DESC, price;

则每个类的最上面行包含正确的数据。我只需要知道如何让数据库仅返回这些行并丢弃其余行。

我正在使用一个相当严格锁定的 MySQL 数据库，并且创建临时表不是一个选项。

更新添加：

上面是正在运行的 reql 查询的高度简化版本。真正的查询确实跨多个表进行连接，看起来更像是这样：

SELECT hc.companyname AS supplier, 
sr.pricedetails AS price, 
sr.matchedsipp AS class,
sr.cardetails AS cardetails,
CAST(sj.jobdate AS DATE) AS jobdate 
FROM searchdetails sd 
STRAIGHT_JOIN searchresults sr ON (
    sd.taskid = sr.taskid 
    AND sd.locationid = sr.locationid 
    AND sd.jobid = sr.jobid 
    AND sd.companyid = sr.companyid
) 
JOIN hirecompanies hc ON sr.companyid = hc.companyid 
JOIN locations lc ON lc.locationid = sd.locationid 
JOIN searchjobs sj ON sr.jobid = sj.jobid 
WHERE DATE(sd.pudate) = '2010-12-28' 
AND DATE(sd.dodate) = '2011-01-04' 
AND hc.countrycode = 'GB' 
AND lc.iata = 'MAN';

pudate 是接送日期，dodate 是还车日期。所有时态数据都使用 DATETIME 字段存储。

Answer 1

这应该会产生每种车辆类型的最近搜索日期的最低价格：

 SELECT * FROM data D1
   WHERE NOT EXISTS
      (SELECT * FROM data D2 WHERE D2.class = D1.class
                             AND (D2.date > D1.date 
                             OR (D2.date = D1.date AND D2.price < D1.price)))

您有多少数据（这对于大量数据来说不会特别有效）？

Answer 2

使用带有 LIMIT 1 的多个 SELECT，然后使用 UNION 怎么样？这样，您可以组合不同的 ORDER BY，但每个特定查询仍然只能获取 1 行。

Answer 3

像这样的东西吗？

SELECT class, date, MIN(price) 
FROM data 
WHERE (class, date) IN 
    (SELECT class, MAX(date) FROM data GROUP BY class)

Answer 4

SELECT  t.*
FROM    (
        SELECT  class, MAX(date) AS maxdate
        FROM    mytable
        GROUP BY
                class
        ) td
JOIN    mytable t
ON      t.id = 
        (
        SELECT  id
        FROM    mytable ti
        WHERE   ti.class = td.class
                AND ti.date = td.maxdate
        ORDER BY
                ti.class, ti.date, ti.price, ti.id
        LIMIT 1
        )

在 mytable（class、date、price、id） 上创建索引，以便快速运行。

Answer 5

要获得每类的最新价格

SELECT
*,
FROM data
GROUP BY class
HAVING date=max(date)

最低价格将是相同的，除非

HAVING price=min(price)

然后，如果您想要最低价格，当超过一个有最新日期

HAVING date=max(date) AND price=min(price)