selecting from eav返回两次行

7kjnsjlb  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(315)

我有下列表格

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      ║
╚═════════════╩════════════════╩══════════╝
7cjasjjr

7cjasjjr1#

使用 group_concat() ```
SELECT det.projectName, group_concat(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"
group by det.projectName,architect.lastName

fbcarpbf

fbcarpbf2#

使用 group_concat() ```
SELECT det.projectName, group_concat( 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"
group by det.projectName,archLast

相关问题