如何在MySQL和Rails上使用SELECT DISTINCT ON [英] How to use SELECT DISTINCT ON with MySQL and Rails

问题描述

我有一个非常复杂的查询，实质上是选择每个交付服务的最便宜的交付服务价格。

I have quite a complex query to essentially select the cheapest delivery service price per delivery service.

为了获取每个交付服务，我利用SQL中的 DISTINCT 函数。该查询提供正确的结果：

In order to get unique records per delivery service, I utilise the DISTINCT function in SQL. This query provides correct results:

DeliveryServicePrice.active.select('DISTINCT ON (delivery_service_id) *').order('delivery_service_id, price ASC')

（仅查询的一部分）

但是，此查询似乎仅适用于PostgreSQL（考虑到PostgreSQL对SQL标准的严格要求，我认为这很奇怪）；它不适用于MySQL和SQLite。我收到以下错误：

However, this query only seems to work with PostgreSQL (which I think is strange considering PostgreSQL is a lot more strict with SQL standards); it does not work with MySQL and SQLite. I receive the following error:

Mysql2 :: Error：您的SQL语法有错误。检查与您的MySQL服务器版本相对应的手册
，以在'ON（delivery_service_id）附近使用
的正确语法* FROM`delivery_service_prices`
WHERE`delivery_servi'在第1行：SELECT DISTINCT ON
（delivery_service_id）*从`delivery_service_prices`中
`delivery_service_prices..active` = 1 AND（2808.0> = min_weight AND
2808.0< = max_weight AND 104.0> = min_length AND 104.0< ; = max_length AND 104.0> = min_thickness AND 104.0< = max_thickness）ORDER BY delivery_service_id，价格ASC

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON (delivery_service_id) * FROM `delivery_service_prices` WHERE `delivery_servi' at line 1: SELECT DISTINCT ON (delivery_service_id) * FROM `delivery_service_prices` WHERE `delivery_service_prices`.`active` = 1 AND (2808.0 >= min_weight AND 2808.0 <= max_weight AND 104.0 >= min_length AND 104.0 <= max_length AND 104.0 >= min_thickness AND 104.0 <= max_thickness) ORDER BY delivery_service_id, price ASC

我正在构建的应用程序是开放源代码，因此需要支持所有3种数据库类型。
如何在Rails框架语法中为MySQL和SQLite创建 DISTINCT ON 查询？

The application I'm building is open source, so it's required to support all 3 database types. How do I create DISTINCT ON queries for MySQL and SQLite in the Rails framework syntax?

I我正在使用Rails 4.1。

I'm using Rails 4.1.

我以前的参考问题：

如何根据外来选择唯一记录

使用查询的文件和行号。

DeliveryServicePrice.select('delivery_service_prices.id').active.joins('LEFT OUTER JOIN delivery_service_prices t2 ON (delivery_service_prices.delivery_service_id = t2.delivery_service_id AND delivery_service_prices.price > t2.price)').where('t2.delivery_service_id IS NULL')

解决方案

DISTINCT ON 是Postgres特定于标准SQL DISTINCT 。它们都不是函数，它们都是SQL关键字-即使 DISTINCT ON 之后所需的括号使它看起来像函数一样。

DISTINCT ON is a Postgres specific extension to the standard SQL DISTINCT. Neither of them is a "function", both are SQL key words - even though the parentheses required after DISTINCT ON make it look like a function.

有几种技术可以用标准SQL重写，但是所有这些技术都比较冗长。由于MySQL不支持窗口函数，因此 row_number（）已退出。

There are a couple of techniques to rewrite this with standard-SQL, all of them more verbose, though. Since MySQL does not support window-functions row_number() is out.

详细信息和更多可能的查询技术：

Details and more possible query techniques:

• 在每个GROUP BY组中选择第一行？

获取具有列最大值的行

用不存在重写：

SELECT *
FROM   delivery_service_prices d1
WHERE  active = 1
AND    2808.0 BETWEEN min_weight AND max_weight
AND    104.0  BETWEEN min_length AND max_length
AND    104.0  BETWEEN min_thickness AND max_thickness
AND NOT EXISTS (
   SELECT 1
   FROM   delivery_service_prices d2
   WHERE  active = 1
   AND    2808.0 BETWEEN min_weight AND max_weight
   AND    104.0  BETWEEN min_length AND max_length
   AND    104.0  BETWEEN min_thickness AND max_thickness
   AND    d2.delivery_service_id = d1.delivery_service_id
   AND    d2.price < d1.price 
   AND    d2.<some_unique_id> < d1.<some_unique_id>  -- tiebreaker!
   )
ORDER  BY delivery_service_id

• 如果相同的 delivery_service_id 可以有多个行具有相同的价格，则您需要添加一些唯一的平局，以避免每个 delivery_service_id出现多个结果。至少如果您想要一个完全等效的查询。我的示例将从每组重复中选择具有最小< some_unique_id> 的行。

  • If there can be multiple rows with the same price for the same delivery_service_id, you need to add some unique tie-breaker to avoid multiple results per delivery_service_id. At least if you want a perfectly equivalent query. My example would select the row with the smallest <some_unique_id> from each set of dupes.

    与 DISTINCT ON 不同，在这里 ORDER BY 是可选的。

    Unlike with DISTINCT ON, ORDER BY is optional here.

    这篇关于如何在MySQL和Rails上使用SELECT DISTINCT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！