MySQL 如何在不使用 group by 语句的情况下选择不同的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10922464/
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
How to select distinct rows without using group by statement
提问by cool_cs
A B C
1 1 1
1 1 1
2 2 2
2 2 2
3 3 3
3 3 3
4 4 4
4 4 4
5 5 5
5 5 5
5 5 5
6 6 6
6 6 6
I am to output only the distinct rows without using the group by statement. I cannot use group by because it makes mysql hang. So it should return
我将只输出不同的行而不使用 group by 语句。我不能使用 group by，因为它使 mysql 挂起。所以它应该返回
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
I am using DISTINCT in for an inner join.This does not work either:
我正在使用 DISTINCT 进行内部连接。这也不起作用：
SELECT DISTINCT * FROM TABLEA inner join TABLEB on TABLEA.A = TABLEB.A
回答by Jerome WAGNER
SELECT DISTINCT A,B,C FROM TABLE;
According to mysql documentation, DISTINCT specifies removal of duplicate rows from the result set (http://dev.mysql.com/doc/refman/5.0/en/select.html)
根据 mysql 文档， DISTINCT 指定从结果集中删除重复行（http://dev.mysql.com/doc/refman/5.0/en/select.html）
I created a sample on jsfiddle and it works IMHO
我在 jsfiddle 上创建了一个示例，恕我直言
create table tablea (A int,B int,C int);
create table tableb (A int);
INSERT INTO tablea VALUES (1,1,1),(1,1,1),(2,2,2),(2,2,2),(3,3,3),(3,3,3),(4,4,4),(4,4,4),(5,5,5),(5,5,5),(5,5,5),(6,6,6),(6,6,6);
INSERT INTO tableb VALUES (1),(1),(1),(1),(1);
SELECT DISTINCT tablea.A,tablea.B,tablea.C FROM tablea INNER JOIN tableb ON tablea.A=tableb.A;
feel free to experiment on this SQLFiddle.
随意尝试这个SQLFiddle。
回答by RolandoMySQLDBA
This may totally shock you but MySQL uses GROUP BY under the hood to execute DISTINCT !!!
这可能会让你大吃一惊，但 MySQL 在幕后使用 GROUP BY 来执行 DISTINCT ！！！
Here is something you may want to try
这是您可能想尝试的东西
If the table is called mytable, do these two things:
如果该表被称为mytable，请执行以下两件事：
First run this
首先运行这个
ALTER TABLE mytable ADD INDEX ABC (A,B,C);`
Second, run this query
其次，运行此查询
SELECT A,B,C FROM mytable GROUP BY A,B,C;
GROUP BY actually works better with an index present !!!
GROUP BY 实际上在索引存在的情况下效果更好！！！
Here is sample code to prove it works
这是证明它有效的示例代码
mysql> drop database if exists cool_cs;
Query OK, 1 row affected (0.04 sec)
mysql> create database cool_cs;
Query OK, 1 row affected (0.00 sec)
mysql> use cool_cs
Database changed
mysql> create table mytable
-> (A int,B int,C int, key ABC (A,B,C));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERt INTO mytable VALUES
-> (1,1,1),(1,1,1),(2,2,2),(2,2,2),(3,3,3),
-> (3,3,3),(4,4,4),(4,4,4),(5,5,5),(5,5,5),
-> (5,5,5),(6,6,6),(6,6,6);
Query OK, 13 rows affected (0.06 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> select a,b,c FROM mytable group by a,b,c;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 6 |
+------+------+------+
6 rows in set (0.02 sec)
mysql>
Give it a Try !!!
试一试 ！！！
回答by dpelisek
IF you (or anyone else) really wants to get distinct resutl without using DISTINCT or GROUP BY statement, this may be the way:
如果您（或其他任何人）真的想在不使用 DISTINCT 或 GROUP BY 语句的情况下获得不同的结果，则可能是这样：
SELECT
a.name
FROM person a
LEFT JOIN person b
ON (a.name = b.name OR (a.name IS NULL AND b.name IS NULL))
AND a.id < b.id
WHERE b.id IS NULL
It's rather a quaint thing, though.
不过，这是一件很古怪的事情。
回答by gexicide
the DISTINCT keyword does what you want. I.e., if the name of your table is A, select distinct * from Awill do the trick.
DISTINCT 关键字可以满足您的需求。即，如果您的表的名称是 A，select distinct * from A就可以解决问题。
Cf. mysql manual: http://dev.mysql.com/doc/refman/5.0/en/select.html
参见 mysql 手册：http: //dev.mysql.com/doc/refman/5.0/en/select.html
