为什么DISTINCT必须首先在MySQL中使用? [英] Why does DISTINCT has to go first in MySQL?

问题描述

我有一个查询，该查询在工作时有效

I have a query that works when I do

SELECT DISTINCT(table.field.id), 1 FROM ... 

但在我这样做时失败

SELECT 1, DISTINCT(table.field.id) FROM ... 

这是已知的行为吗?

为什么第一个不起作用而第二个不起作用?

Why does the first one work while the second doesn't?

推荐答案

很遗憾，我还不能添加评论.

Unfortunately I'm not able to add a comment yet.

@Gordon Linoff写的是完全正确的. 由于DISTINCT通常是SELECT子句或AGGREGATE函数的一部分，因此会出现错误.它用于从结果集中返回唯一的行，并且可以用于在聚合函数中强制唯一的列值.

What @Gordon Linoff has written is exactly right. You are getting error as DISTINCT in general works as part of SELECT clause or AGGREGATE function. It is used to return unique rows from a result set and it can be used to force unique column values within an aggregate function.

示例:SELECT DISTINCT * ... COUNT(DISTINCT COLUMN)或SUM(DISTINCT COLUMN).

有关流行数据库引擎中DISTINCT的更多信息:

More information's about DISTINCT in popular DB engines:

• PostgreSQL: https://www.postgresql .org/docs/9.0/static/sql-select.html#SQL-DISTINCT
• SQL Server: https://www.techonthenet.com/sql_server/distinct.php
• Oracle: https://www.techonthenet.com/oracle/distinct.php
• MySQL: https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html https://dev.mysql.com/doc/refman/5.7/zh/select.html

• PostgreSQL:https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT
• SQL Server: https://www.techonthenet.com/sql_server/distinct.php
• Oracle: https://www.techonthenet.com/oracle/distinct.php
• MySQL: https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html https://dev.mysql.com/doc/refman/5.7/en/select.html

这篇关于为什么DISTINCT必须首先在MySQL中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！