将SELECT DISTINCT ON查询从Postgresql转换为MySQL [英] Converting SELECT DISTINCT ON queries from Postgresql to MySQL

问题描述

我一直在使用 PostgreSQL ，现在正在迁移到 MySQL 。

在我的查询中，我使用 PostgreSQL 的 SELECT DISTINCT ON（col1，col2，col3），我想知道是否在 MySQL 中有这个语句的任何对应项。

解决方案

并没有将使用SELECT DISTINCT ON的Postgresql查询转换为MySQL的确切等价物。

Postgresql SELECT DISTINCT ON

在Postgresql中，以下查询将消除表达式（col1，col2，col3）匹配的所有行，并且它只会为每一组匹配行保留第一个col4，col5行：

  SELECT DISTINCT ON（col1，col2 ，col3）col4，col5 
 FROM tablename 
  

所以如果你的表是这样的：

  col1 | col2 | col3 | col4 | col5 
 -------------------------------- 
 1 | 2 | 3 | 777 | 888 
 1 | 2 | 3 | 888 | 999 
 3 | 3 | 3 | 555 | 555 
  

我们的查询将只保留一行（1,2,3）和一行（ 3,3,3）。结果行将会是：

  col4 | col5 
 ----------- 
 777 | 888 
 555 | 555 
  

请注意，每套的第一排是不可预知的，我们的第一排可能是888，999），除非我们指定一个ORDER BY：

  SELECT DISTINCT ON（col1，col2，col3）col4，col5 
 FROM tablename 
 ORDER BY col1，col2，col3，col4 
  

对表达式的DISTINCT必须与最左边的ORDER BY表达式匹配，但ORDER BY可以包含其他表达式）。

$ b

GROUP BY的扩展

MySQL扩展了 GROUP BY ，以便我们可以选择未在GROUP BY子句中命名的非聚合列。每当我们选择nonaggregated列时，服务器可以自由地从该列中的每个组中选择任何值，因此结果值将是不确定的。

所以这个Postgresql查询： p>

  SELECT DISTINCT ON（col1，col2，col3）col4，col5 
 FROM表名
  

可以被认为等价于这个MySQL查询：

  SELECT col4，col5 
 FROM tablename 
 GROUP BY col1，col2，col3 
  

Postgresql和MySQL都会为每个（col1，col2，col3）返回第一行，并且在这两种情况下返回的行都是不可预知的，因为我们没有指定和按子句排序。
$ b

很多人都很想将这个Postgresql查询转换为ORDER BY：

  SELECT DISTINCT ON（col1，col2，col3）col4，col5 
 FROM tablename 
 ORDER BY col1，col2，col3，col4 
  

与这一个：

  SELECT col4，col5 
 FROM（
 SELECT col1，col2，col3，col4，col5 
 FROM tablename 
 ORDER BY col1 ，col2，col3，col4 
）s 
 GROUP BY col1，col2，col3 
  

<这里的想法是将一个ORDER BY应用到子查询中，这样当MySQL按col1，col2，col3分组时，它将保留第一个遇到的col4和col5的值。 这个想法很好，但它是错误的！ MySQL可以自由地为col4和col5选择任何值，并且我们不知道哪些是遇到的第一个值，它取决于优化器。所以我会更正它：

  SELECT t1.col4，t1.col5 
 FROM tablename t1 INNER JOIN（ SELECT col1，col2，col3，MIN（col4）as m_col4 
 FROM tablename 
 GROUP BY col1，col2，col3）s 
 ON t1.col1 = s.col1 
 AND t1 .col2 = s.col2 
 AND t1.col3 = s.col3 
 AND t1.col4 = s.m_col4 
 GROUP BY 
 t1.col1，t1.col2，t1。 col3，t1.col4 
  

但是这开始变得更加复杂。

结论

作为一般规则，没有将Postgresql查询转换为MySQL查询的确切方法，但是有很多解决方法，结果查询可能与原始查询一样简单，或者它可能变得非常复杂，但它取决于查询本身。

I've been using PostgreSQL and now migrating to MySQL.

In my queries, I'm using PostgreSQL's SELECT DISTINCT ON (col1, col2, col3), I was wondering if there is any counterpart of this statement in MySQL.

解决方案

There's not an exact equivalent to convert a Postgresql query that makes use of SELECT DISTINCT ON to MySQL.

Postgresql SELECT DISTINCT ON

In Postgresql, the following query will eliminate all rows where the expressions (col1, col2, col3) match, and it will only keep the "first col4, col5 row" for each set of matched rows:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename

So if your table is like this:

col1 | col2 | col3 | col4 | col5
--------------------------------
1    | 2    | 3    | 777  | 888
1    | 2    | 3    | 888  | 999
3    | 3    | 3    | 555  | 555

our query will keep just one row for (1,2,3) and one row for (3,3,3). The resulting rows will then be:

col4 | col5
-----------
777  | 888
555  | 555

please notice that the "first row" of each set is unpredictable, our fist row might be (888, 999) as well unless we specify an ORDER BY:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4

(the DISTINCT on expressions must match the leftmost ORDER BY expressions, but the ORDER BY can contain additional expressions).

MySQL extension to GROUP BY

MySQL extends the use of GROUP BY so that we can select nonaggregated columns not named in the GROUP BY clause. Whenever we select nonaggregated columns the server is free to choose any value from each group from that column, so the resulting values will be indetermined.

So this Postgresql query:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename

can be considered equivalent to this MySQL query:

SELECT col4, col5
FROM tablename
GROUP BY col1, col2, col3

both Postgresql and MySQL will return the "First row" for each (col1, col2, col3), and in both cases the row returned is unpredictable because we didn't specify and order by clause.

A lot of people would be very tempted to convert this Postgresql query with an ORDER BY:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4

with this one:

SELECT col4, col5
FROM (
  SELECT col1, col2, col3, col4, col5
  FROM tablename
  ORDER BY col1, col2, col3, col4
) s
GROUP BY col1, col2, col3

the idea here is to apply an ORDER BY to a subquery so that when MySQL groups by col1, col2, col3 it will keep the first encountered value for col4 and col5. The idea is good, but it's wrong! MySQL is free to choose any value for col4 and col5, and we don't know which are the first values encountered, it depends on the optimizer. So I would correct it to this:

SELECT t1.col4, t1.col5
FROM tablename t1 INNER JOIN (SELECT col1, col2, col3, MIN(col4) as m_col4
                              FROM tablename
                              GROUP BY col1, col2, col3) s
     ON t1.col1=s.col1
        AND t1.col2=s.col2
        AND t1.col3=s.col3
        AND t1.col4=s.m_col4
GROUP BY
  t1.col1, t1.col2, t1.col3, t1.col4

but this is starting to get more complicated.

Conclusion

As a general rule, there's not an exact way to convert a Postgresql query to a MySQL query, but there are a lot of workarounds, the resulting query might be as simple as the original one or it might become very complicated, but it depends on the query itself.

这篇关于将SELECT DISTINCT ON查询从Postgresql转换为MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！