代码之家 › 专栏 › 技术社区 › gen_Eric

搜索组\u CONCAT使用LIKE

group-concat group-by mysql

6

gen_Eric · 技术社区 · 16 年前

SELECT orders.orderID, 
GROUP_CONCAT(contacts.firstName, " ", contacts.lastName) AS attachedContacts
FROM (orders)
JOIN contacts ON orders.contactID=contacts.contactID
GROUP BY orders.orderID
ORDER BY orders.orderID DESC

WHERE attachedContacts LIKE '%Eric%'

查询返回如下数据:

orderID atachedContacts
01      Eric Siegel, John Smith
02      Jason Jackson, Bill O'Neil
03      Eric Siegel, Jason Jackson, Neil O'Ryan

我希望查询返回第01行和第03行,因为“Eric”在联系人列表中。

我该怎么做?

1 回复 | 直到 13 年前

1

23

gen_Eric 15 年前

试试这个:

SELECT orders.orderID, 
GROUP_CONCAT(contacts.firstName, " ", contacts.lastName) AS attachedContacts
FROM orders
JOIN contacts ON orders.contactID=contacts.contactID
GROUP BY orders.orderID DESC
HAVING attachedContacts LIKE '%Eric%'