将 SELECT DISTINCT ON 查询从 Postgresql 转换为 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17673457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting SELECT DISTINCT ON queries from Postgresql to MySQL
提问by Michael 'Maik' Ardan
I've been using PostgreSQLand now migrating to MySQL.
我一直在使用PostgreSQL，现在迁移到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.
在我的疑问，我使用的PostgreSQL的SELECT DISTINCT ON (col1, col2, col3)，我想知道是否有这说法的任何对应的MySQL。
回答by fthiella
There's not an exact equivalent to convert a Postgresql query that makes use of SELECT DISTINCT ON to MySQL.
没有完全等效的将使用 SELECT DISTINCT ON 的 Postgresql 查询转换为 MySQL。
Postgresql SELECT DISTINCT ON
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:
在 Postgresql 中，以下查询将消除表达式(col1, col2, col3)匹配的所有行，并且只保留每组匹配行的“第一个 col4, col5 行”：
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:
我们的查询将只保留一行（1,2,3）和一行（3,3,3）。结果行将是：
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:
请注意，每个集合的“第一行”是不可预测的，除非我们指定 ORDER BY，否则我们的第一行也可能是 (888, 999)：
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).
（表达式上的 DISTINCT 必须匹配最左边的 ORDER BY 表达式，但 ORDER BY 可以包含其他表达式）。
MySQL extension to GROUP BY
MySQL 对 GROUP BY 的扩展
MySQL extends the use of GROUP BYso 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.
MySQL 扩展了GROUP BY的使用，以便我们可以选择未在 GROUP BY 子句中命名的非聚合列。每当我们选择非聚合列时，服务器都可以从该列的每个组中自由选择任何值，因此结果值将是不确定的。
So this Postgresql query:
所以这个 Postgresql 查询：
SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
can be considered equivalent to this MySQL query:
可以认为相当于这个 MySQL 查询：
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.
Postgresql 和 MySQL 都会为每个 (col1, col2, col3) 返回“第一行”，并且在这两种情况下，返回的行都是不可预测的，因为我们没有指定和 order by 子句。
A lot of people would be very tempted to convert this Postgresql query with an ORDER BY:
很多人会很想用 ORDER BY 转换这个 Postgresql 查询：
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:
这里的想法是将 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
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.
作为一般规则，没有一种将 Postgresql 查询转换为 MySQL 查询的确切方法，但是有很多变通方法，生成的查询可能和原始查询一样简单，也可能变得非常复杂，但这取决于查询本身。
回答by Alex Wittig
Use a subquery to determine the order, and an outer query to group them.
使用子查询来确定顺序，并使用外部查询对它们进行分组。
Like @a_horse_with_no_name points out, this works because MySQL allows partial group by, unlike other DBMSs.
就像@a_horse_with_no_name 指出的那样，这是因为 MySQL 允许 partial group by，这与其他 DBMS 不同。
For example:
例如：
CREATE TABLE customer_order
(`customer` varchar(5), `item` varchar(6), `date` datetime)
;
INSERT INTO customer_order
(`customer`, `item`, `date`)
VALUES
('alice', 'widget', '2000-01-05 00:00:00'),
('bob', 'widget', '2000-01-02 00:00:00'),
('alice', 'widget', '2000-01-01 00:00:00'),
('alice', 'wodget', '2000-01-06 00:00:00')
;
Query for each customer's first order:
查询每个客户的第一个订单：
select *
from
(select customer, item, date
from customer_order
order by date) c
group by customer
Result:
结果：
| CUSTOMER | ITEM | DATE |
|----------|--------|--------------------------------|
| alice | widget | January, 01 2000 00:00:00+0000 |
| bob | widget | January, 02 2000 00:00:00+0000 |
回答by php
You can't select distinct values from multiple columns. while selecting use query like this
您不能从多列中选择不同的值。在选择使用这样的查询时
select distinct col1, col2 from table
回答by echo_Me
you should migrate to PDOor MSYQLIinstead of MYSQLas its already deprecated.
您应该迁移到PDO或MSYQLI而不是MYSQL，因为它已经过时了。
about your question you can do
关于你可以做的问题
SELECT DISTINCT col1, col2, col3
or
或者
SELECT col1, col2, col3
........
GROUP BY col1 --//--- or whatever column you want to be distinct
