php – SELECT DISTINCT name但返回所有数据

发布时间：2020-12-13 17:44:20 所属栏目：PHP教程来源：网络整理

所以我有一个装满汽车的桌子：

假设有4个闪避毒蛇,数据库中会有4个条目,除了carID之外都是相同的.

carID | carname | carmodel | colour   |  reg
--------------------------------------------------
    1    |  viper  |  dodge  |   red     |  123
    2    |  viper  |  dodge  |   red     |  124
    3    |  viper  |  dodge  |   red     |  125
    4    |  viper  |  dodge  |   red     |  126
    5    |   R8    |  audi   |   blue    |  127
    6    |   R8    |  audi   |   blue    |  128

当用户搜索汽车时,我想只显示一个闪避毒蛇.但是,我想从该行和所有其他不同的汽车中提取所有信息.

所以我想要的输出是：

carID | carname | carmodel | colour  |  reg
--------------------------------------------------
     1   |  viper  |   dodge  |  red    |  123
     5   |   R8    |   audi   |  blue   |  127

如果我做：

SELECT DISTINCT * FROM cars

它会撤回所有条目.

SELECT DISTINCT carname FROM cars

只拉回其中一个,但我只有车名.

有没有这样的：

SELECT * FROM cars ORDER BY DISTINCT carname

或类似的东西？

解决方法

如果你想要返回他的carId,那么你可以使用以下将返回每个相同车牌等的min(carid)：

select min(carId) as carId,carname,carmodel,colour
from cars
group by carname,colour

见SQL Fiddle with Demo

您将看到我将carId置于聚合函数中,这是为了确保MySQL始终返回carId列的预期值.如果您没有GROUP BY或聚合SELECT列表中的项目,您可能会返回意外的结果. (见MySQL Extensions to GROUP BY)

来自MySQL Docs：

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. … You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However,this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group,so unless they are the same,the values chosen are indeterminate. Furthermore,the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen,and ORDER BY does not affect which values the server chooses.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!