我有下列表格
contacts
╔═══════════╦═══════════╦══════════╦═══════════╗
║ contactId ║ projectId ║ lastName ║ type ║
╠═══════════╬═══════════╬══════════╬═══════════╣
║ 1 ║ 1 ║ Foo ║ architect ║
║ 2 ║ 1 ║ Owner 1 ║ owner ║
║ 3 ║ 1 ║ Owner 2 ║ owner ║
╚═══════════╩═══════════╩══════════╩═══════════╝
projectDetails
╔═══════════╦═════════════╗
║ projectId ║ projectName ║
╠═══════════╬═════════════╣
║ 1 ║ Bar ║
║ 2 ║ Fizz ║
╚═══════════╩═════════════╝
我试图通过搜索联系人的姓氏来选择projectname以及所有相关联系人。我用它来做这件事的代码是
SELECT `det`.`projectName`, `owner`.`lastName` as `ownerLast`, `architect`.`lastName` as `archLast`,
FROM `projectDetails` as `det`
LEFT JOIN `contacts` as `owner`
ON `owner`.`projectId` = `det`.`projectId` AND `owner`.`type`="owner"
LEFT JOIN `contacts` as `architect`
ON `architect`.`projectId` = `det`.`projectId` AND `architect`.`type`="architect"
WHERE `architect`.`lastName` = "Foo"
这将生成两行
╔═════════════╦═══════════╦══════════╗
║ projectName ║ ownerLast ║ archLast ║
╠═════════════╬═══════════╬══════════╣
║ Bar ║ Owner1 ║ Foo ║
║ Bar ║ Owner2 ║ Foo ║
╚═════════════╩═══════════╩══════════╝
我想这是有道理的,但不是我要找的。有没有一种方法可以将同一类型的结果聚合到
╔═════════════╦════════════════╦══════════╗
║ projectName ║ ownerLast ║ archLast ║
╠═════════════╬════════════════╬══════════╣
║ Bar ║ Owner1, Owner2 ║ Foo ║
╚═════════════╩════════════════╩══════════╝
2条答案
按热度按时间7cjasjjr1#
使用
group_concat()
```SELECT
det
.projectName
, group_concat(owner
.lastName
) asownerLast
,architect
.lastName
asarchLast
,FROM
projectDetails
asdet
LEFT JOIN
contacts
asowner
ON
owner
.projectId
=det
.projectId
ANDowner
.type
="owner"LEFT JOIN
contacts
asarchitect
ON
architect
.projectId
=det
.projectId
ANDarchitect
.type
="architect"WHERE
architect
.lastName
= "Foo"group by
det
.projectName
,architect
.lastName
fbcarpbf2#
使用
group_concat()
```SELECT
det
.projectName
, group_concat(owner
.lastName
) asownerLast
,architect
.lastName
asarchLast
,FROM
projectDetails
asdet
LEFT JOIN
contacts
asowner
ON
owner
.projectId
=det
.projectId
ANDowner
.type
="owner"LEFT JOIN
contacts
asarchitect
ON
architect
.projectId
=det
.projectId
ANDarchitect
.type
="architect"WHERE
architect
.lastName
= "Foo"group by
det
.projectName
,archLast