SQL合并2行响应

Question

你好，我是SQL语言的新手，我想获得我所有的抗体，但当我使用以下查询时：

SELECT id, a.name antibodyName, c.name colorName FROM Antibodies a JOIN AssignedColors c
              ON id = antiId WHERE colorId IS NOT NULL

我得到的回应是：

[
  { id: 1, antibodyName: 'antibody 1', colorName: 'red' },
  { id: 2, antibodyName: 'antibody 2', colorName: 'red' },
  { id: 2, antibodyName: 'antibody 2', colorName: 'yellow' }
]

我怎么才能把它改成这样呢？

[
  { id: 1, antibodyName: 'antibody 1', colorName: 'red' },
  { id: 2, antibodyName: 'antibody 2', colorName: 'red, yellow' },
]

Answer 1

您需要聚合：

SELECT a.id, a.name as antibodyName, GROUP_CONCAT(c.name) as colorNames
FROM Antibodies a JOIN
     AssignedColors c
     ON a.id = c.antiId
WHERE colorId IS NOT NULL
GROUP BY a.id, a.name;