如何在同一个 SQL 语句中使用 SELECT DISTINCT 和 CONCAT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9027937/
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 use SELECT DISTINCT and CONCAT in the same SQL statement
提问by radleybobins
So I am feeding the results of this SQL into an array. The array later becomes the suggestions for a textbox that operates while typing. I want it to only return each name 1 time, even if the person has multiple appointments. Currently, this returns all appointments for the person with that name, so if "Brad Robins" has 5 appointments, and I start to type "Brad", it displays "Brad Robins" 5 times in the suggestions instead of only once.
所以我将这个 SQL 的结果输入到一个数组中。该数组稍后成为在键入时操作的文本框的建议。我希望它只返回每个名字 1 次，即使该人有多个约会。目前，这将返回具有该名称的人的所有约会，因此如果“Brad Robins”有 5 个约会，并且我开始键入“Brad”，它会在建议中显示“Brad Robins”5 次而不是仅一次。
$sql = "SELECT DISTINCT CONCAT(clients.studentFirstName, ' ', clients.studentLastName) AS name, appointments.location, appointments.subLocation, appointments.appointmentAddress1, appointments.appointmentAddress2, appointments.appointmentCity, appointments.appointmentState, appointments.appointmentZip, appointments.startTime, appointments.endTime, appointments.date, clients.school
FROM appointments JOIN clients
ON appointments.clientID = clients.clientID
WHERE CONCAT(clients.studentFirstName, ' ', clients.studentLastName) = '".$roommate."' AND clients.school = '".$school."';";
To me, it just seems like DISTINCT and CONCAT aren't playing nicely together.
对我来说，似乎 DISTINCT 和 CONCAT 不能很好地一起玩。
采纳答案by DRapp
Distinct goes against the entire row of ALL columns, not just the name portion... So if the appointments are on different date/times, locations, etc, they will all come out. If all you want to show is the NAME portion, strip the rest of the other content. Query the available appointments AFTER a person has been chosen.
Distinct 与 ALL 列的整行相悖，而不仅仅是姓名部分......所以如果约会在不同的日期/时间、地点等，它们都会出现。如果您只想显示 NAME 部分，请去掉其他内容的其余部分。选择人员后查询可用约会。
回答by Luis
The problem are the other fields; DISTINCT applies to the whole result. Probably the best thing is to do to separate queries or populate 2 different arrays; if you ORDER BY name, you can remove duplicates by copying into the dest array only when the name changes.
问题是其他领域；DISTINCT 适用于整个结果。最好的办法可能是分离查询或填充 2 个不同的数组；如果按名称排序，则只有在名称更改时才可以通过复制到 dest 数组来删除重复项。
回答by vimdude
Don't use DISTINCT, use group by:
不要使用 DISTINCT，使用 group by：
$sql = "SELECT CONCAT(clients.studentFirstName, ' ', clients.studentLastName) AS name, appointments.location, appointments.subLocation, appointments.appointmentAddress1, appointments.appointmentAddress2, appointments.appointmentCity, appointments.appointmentState, appointments.appointmentZip, appointments.startTime, appointments.endTime, appointments.date, clients.school
FROM appointments JOIN clients
ON appointments.clientID = clients.clientID
WHERE CONCAT(clients.studentFirstName, ' ', clients.studentLastName) = '".$roommate."' AND clients.school = '".$school."' group by CONCAT(clients.studentFirstName, ' ', clients.studentLastName);";
Also be careful about XSS in $school and $roomate if this accessible outside.
如果可以在外面访问，还要小心 $school 和 $roomate 中的 XSS。
回答by bricriu
You could use
你可以用
group by name
at the end, which would cause the query to return only one of each name, but then you can't predict what appointment results will be returned in cases where a client has multiple appointments, and the query stops being very useful.
最后，这将导致查询仅返回每个名称中的一个，但是您无法预测在客户有多个约会的情况下将返回什么约会结果，并且该查询不再非常有用。
Like others have pointed out, you should probably just get the list of appointments after the client has been chosen.
就像其他人指出的那样，您可能应该在选择客户后获取约会列表。
回答by Yes
Select distinct concat(....) From ....
选择不同的 concat(....) From ....
回答by s6a6n6d6m6a6n
select colA||' - '||colB
from table1
where colA like 'beer%'
group by colA||' - '||colB
order by colA||' - '||colB
;
回答by Ahmad Ashik
I know its late response, someone may find this use full
我知道它的反应迟了，有人可能会发现这个用途已满
Try using DISTINCTafter CONCAT
尝试使用DISTINCT后CONCAT
example :
例子 ：
$sql = SELECT CONCAT(DISTINCT clients.studentFirstName ...
