php 从列中选择唯一值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8571902/
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
selecting unique values from a column
提问by meandme
I have a MySQL table which contains the following type of information:
我有一个 MySQL 表，其中包含以下类型的信息：
Date product
2011-12-12 azd
2011-12-12 yxm
2011-12-10 sdx
2011-12-10 ssdd
Here is an example of a script I use to get data from this table:
这是我用来从该表中获取数据的脚本示例：
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("db", $con);
$sql=mysql_query("SELECT * FROM buy ORDER BY Date");
while($row = mysql_fetch_array($sql))
{
echo "<li><a href='http://www.website/". $row['Date'].".html'>buy ". date("j, M Y", strtotime($row["Date"]))."</a></li>";
}
mysql_close($con);
?>
This script displays every date from the table, e.g.
此脚本显示表中的每个日期，例如
12.dec 2011
12.dec.2011
10.dec.2011
10.dec.2011
I would like to only display unique dates, e.g.
我只想显示唯一的日期，例如
12.dec.2011
10.dec.2011
回答by Léon Rodenburg
回答by rabudde
use
用
SELECT DISTINCT Date FROM buy ORDER BY Date
so MySQL removes duplicates
所以 MySQL 删除重复项
BTW: using explicit column names in SELECTuses less resources in PHP when you're getting a large result from MySQL
顺便说一句：SELECT当您从 MySQL 获得大量结果时，使用显式列名在PHP中使用的资源较少
回答by John
Use this query to get values
使用此查询获取值
SELECT * FROM `buy` group by date order by date DESC
回答by ajreal
The rest are almost correct, except they should order by Date DESC
其余的几乎是正确的，除了它们应该按日期 DESC 排序
SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;
回答by Kvvaradha
DISTINCTis always a right choice to get unique values. Also you can do it alternatively without using it. That's GROUP BY. Which has simply add at the end of the query and followed by the column name.
DISTINCT始终是获得独特价值的正确选择。您也可以在不使用它的情况下进行替代。那是GROUP BY。它只是在查询的末尾添加，然后是列名。
SELECT * FROM buy GROUP BY date,description
回答by CONvid19
Another DISTINCTanswer, but with multiple values:
另一个DISTINCT答案，但有多个值：
SELECT DISTINCT `field1`, `field2`, `field3` FROM `some_table` WHERE `some_field` > 5000 ORDER BY `some_field`
回答by Peter Darmis
Use something like this in case you also want to output products details per date as JSON.
如果您还想将每个日期的产品详细信息输出为 JSON，请使用类似的方法。
SELECT `date`,
CONCAT('{',GROUP_CONCAT('{\"id\": \"',`product_id`,'\",\"name\": \"',`product_name`,'\"}'),'}') as `productsJSON`
FROM `buy` group by `date`
order by `date` DESC
product_id product_name date
| 1 | azd | 2011-12-12 |
| 2 | xyz | 2011-12-12 |
| 3 | ase | 2011-12-11 |
| 4 | azwed | 2011-12-11 |
| 5 | wed | 2011-12-10 |
| 6 | cvg | 2011-12-10 |
| 7 | cvig | 2011-12-09 |
RESULT
date productsJSON
2011-12-12T00:00:00Z {{"id": "1","name": "azd"},{"id": "2","name": "xyz"}}
2011-12-11T00:00:00Z {{"id": "3","name": "ase"},{"id": "4","name": "azwed"}}
2011-12-10T00:00:00Z {{"id": "5","name": "wed"},{"id": "6","name": "cvg"}}
2011-12-09T00:00:00Z {{"id": "7","name": "cvig"}}
Try it out in SQL Fiddle
在SQL Fiddle 中尝试一下
回答by Marco Desposito
Depends on what you need.
取决于你需要什么。
In this case I suggest:
在这种情况下，我建议：
SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;
because there are few fields and the execution time of DISTINCTis lower than the execution of GROUP BY.
因为字段少， 的执行时间DISTINCT比 的执行时间短GROUP BY。
In other cases, for example where there are many fields, I prefer:
在其他情况下，例如在有很多字段的情况下，我更喜欢：
SELECT * FROM buy GROUP BY date ORDER BY date DESC;
回答by Ashutosh dwivedi
There is a specific keyword for the achieving the same.
有一个特定的关键字可以实现相同的目标。
SELECT DISTINCT( Date ) AS Date
FROM buy
ORDER BY Date DESC;
