MySQL 选择没有重复条目的一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7040631/
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
Select one row without duplicate entries
提问by Darius
In mysql table infoi have :
在 mysql 表中，info我有：
Id , Name , City , date , status
Id ， Name ， City ， date ， status
I want to select all names from "info" Making the query
我想从“信息”中选择所有名称进行查询
$query = mysql_query("SELECT name FROM info WHERE status = 1 ORDER BY id")
or die(mysql_error());
while ($raw = mysql_fetch_array($query))
{
$name = $raw["name"];
echo ''.$name.'<br>';
}
Well, the result is that it returns all the entries. I want to echo all the entries without duplicates.
好吧，结果是它返回了所有条目。我想回显所有没有重复的条目。
Saying: under raw "name" we have inserted the name "John" 10 times.
I want to echo only one time.
Is this possible?
说：在原始的“name”下，我们已经插入了 10 次“John”这个名字。
我只想回声一次。这可能吗？
回答by Bohemian
It's pretty simple:
这很简单：
SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id
The SQL keyword DISTINCTdoes the trick.
SQL 关键字DISTINCT可以解决问题。
回答by Dalen
try using this as your query:
尝试使用它作为您的查询：
SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id
to get distinct names
获得不同的名字
or as other suggested use GROUP BY
或作为其他建议的用途 GROUP BY
SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id
I think the first one is more intuitive and there are no big performance difference between the two
我认为第一个更直观，两者之间没有太大的性能差异
EDIT
编辑
as the OP wants also the number of names here we go:
因为 OP 还想要我们这里的名字数量：
SELECT name,COUNT(id) AS n_names
FROM info WHERE status = 1
GROUP BY name
ORDER BY name
you can ORDER BYnameor n_namesdepending on what you need
你可以ORDER BYname或n_names取决于你需要什么
回答by cristian
Change
改变
SELECT name FROM info WHERE status = 1 ORDER BY id
to
到
SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id
Observe that GROUP BYwas added. More about group by http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html
观察GROUP BY添加。更多关于 group by http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html
Edit:
for name with number of apparences try
编辑：
对于具有出现次数的名称，请尝试
SELECT name, count(name) FROM info WHERE status = 1 GROUP BY name ORDER BY id
回答by Manse
add GROUP BY nameto your SQL Statment - this will only bring back one of each entry from the name column
添加GROUP BY name到您的 SQL 语句 - 这只会从名称列中带回每个条目之一
回答by SergeS
use GROUP BY name statement
使用 GROUP BY 名称语句
$query = mysql_query("SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id") or die(mysql_error());
while ($raw = mysql_fetch_array($query)) {
$name = $raw["name"];
echo ''.$name.'<br>';
}
回答by Darkpore
This works for me, returns the table names for a given database.
这对我有用，返回给定数据库的表名。
my $sql="select distinct table_name from COLUMNS where table_schema='$database'"
my $sth = $dbht->prepare( $sql )
or die "Cannot prepare SQL statement: $DBI::errstr\n";
$sth->execute
or die "Cannot execute SQL statement: $DBI::errstr\n";
if ($DBI::err){
$msg= "Data fetching terminated early by error: $DBI::errstr";
}
while (@col=$sth->fetchrow_array()){
$table[$i]=$col[0];
$i++;
}
回答by Humphrey
Let us say that you are sending bulk sms and you don't want to send the same message two times to the same guy John . What i discovered , is that using the trick of GROUP BY and ORDER BY at the same time works perfectly . But I don't say that its the best way . Here is how u can use it
假设您正在发送大量短信，并且您不想向同一个人 John 发送两次相同的消息。我发现，同时使用 GROUP BY 和 ORDER BY 的技巧非常有效。但我并不是说这是最好的方法。这是你如何使用它
SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY name
EDIT:This is important to note, when you need more then single column and there values are unique per row then the DISTINCT was not helping.
编辑：请务必注意，当您需要多于单列并且每行的值都是唯一的时， DISTINCT 没有帮助。
回答by Laud Randy Amofah
$sql="SELECT DISTINCT name FROM status =1 GROUP BY name ORDER BY name";
$sql="SELECT DISTINCT name FROM status =1 GROUP BY name ORDER BY name";
$query = mysqli_query($conn,$sql);
<?php while ( $fire=mysqli_fetch_array($query)) { ?>
<h4><?php echo $query['name']; ?><br></h4>
<?php } ?>
