时间戳

首页 > 解决方案 > 使用 GROUP BY 时选择与有序条件匹配的行

mysql - 使用 GROUP BY 时选择与有序条件匹配的行

问题描述

我有一个房地产属性的 MySQL 数据库。一些属性被列出了不止一次，并带有不同的信息。以下是一些简化的数据：

+--------------+---------+-----------------+------------------------+
| category     | price   | address         | remarks                |
+--------------+---------+-----------------+------------------------+
| Commercial   |  435000 | 1224 81ST AVE   | Great retail space!    |
| Multi Family |  435000 | 1224 81ST AVE   | Motivated seller!      |
| Residential  |  435000 | 1224 81ST AVE   | 0% down financing      |
| Residential  | 1095000 | 3290 NW BLUE LN | An exciting new…       |
| Land         |  150000 | 3878 COHO CIR   | Build your dream home… |
| Residential  |  540000 | 3918 COHO CIR   | Ultra-efficient home…  |
| Land         |  300000 | 3890 COHO CIR   | Light-filled condo…    |
| Multi Family |  300000 | 3890 COHO CIR   | Light-filled condo…    |
+--------------+---------+-----------------+------------------------+

数据库中重复的属性可以通过price和address列来标识；在上面的示例数据中，前三行和最后两行是重复的属性。

GROUP BY price, address为每个属性只返回一行很容易使用。但我需要为每个组获取行category，Residential然后Commercial，然后Multi Family，最后Land。换句话说，我想要 where category=Residentialif that exists 的行，否则 wherecategory=Commercial等。

结果应该是：

+--------------+---------+-----------------+------------------------+
| category     | price   | address         | remarks                |
+--------------+---------+-----------------+------------------------+
| Residential  |  435000 | 1224 81ST AVE   | 0% down financing      |
| Residential  | 1095000 | 3290 NW BLUE LN | An exciting new…       |
| Land         |  150000 | 3878 COHO CIR   | Build your dream home… |
| Residential  |  540000 | 3918 COHO CIR   | Ultra-efficient home…  |
| Multi Family |  300000 | 3890 COHO CIR   | Light-filled condo…    |
+--------------+---------+-----------------+------------------------+

在 mysql < 5.7 的旧时代，我可以通过扩展使用他们的非标准组，在 GROUP BY 选择行之前应用排序，如下所示：

SELECT * FROM properties
GROUP BY price, address
ORDER BY FIELD(category, 'Residential', 'Commercial', 'Multi Family', 'Land')

这不再起作用，因为排序是在应用 GROUP BY 之后应用的，所以选择的行是任意的。

有许多问题和答案通过使用聚合函数来解决排序的 GROUP BY 子句的替代方案，但我找不到任何关于我的用例的讨论，其中基于自定义排序函数选择行。

有没有办法在纯 MySQL 中做到这一点？注意：我使用的是 MariaDB 10.1，它没有row_number().

标签： mysqlsql

解决方案

您可以使用row_number()：

select p.*
from (select p.*,
             row_number() over (partition by price, address order by field(category, 'Residential', 'Commercial', 'Multi Family', 'Land')) as seqnum
      from properties p
     ) p
where seqnum = 1;

你也可以这样做：

select p.*
from products p
where field(category, 'Residential', 'Commercial', 'Multi Family', 'Land') =
          select field(p2.category, 'Residential', 'Commercial', 'Multi Family', 'Land')
          from products p2
          where p2.price = p.price and p2.address = p.address
          order by 1
          limit 1
         );

但 。. . 你真的应该升级到更新版本的 MariaDB。

推荐阅读