SQLITE：如果行共享一列，则将它们合并为单行

Question

在上一篇文章中，我在sqlite3中具有以下视图：

CREATE View AttendeeTableView AS

SELECT  (LastName || " " || FirstName) as AttendeeName,  
        CompanyName, 
        PhotoURI,
        CompanyAttendeeRelation.CompanyId,
        CompanyAttendeeRelation.AttendeeId

FROM    Attendee 
JOIN    CompanyAttendeeRelation on CompanyAttendeeRelation.AttendeeId = Attendee.AttendeeId

ORDER BY LastName;

现在，由于数据是根据Attendee和之间的多对多关系生成的Company，因此我可以得到如下结果：

Doe John | company A | johnPic.png | 1 | 15
Doe John | company B | johnPic.png | 2 | 15

我想做的是，如果有一家以上的公司（如上），则创建一个查询，输出：

Doe John | company A company B | johnPic.png | 1 2 | 15

另一个输出：

Doe John | company A | company B | johnPic.png | 1 | 2 | 15

因此，我本质上需要知道如何合并该表中具有不同值的行的特定列。

有任何想法吗？

以防万一，company A company B在第一个查询中显然是文本串联，也就是说，(row1.CompanyName || " " ||row2.CompanyName)

Answer 1

group_concat(X) 为此使用聚合函数：

SELECT (a.LastName || " " || a.FirstName) AS AttendeeName
     , a.PhotoURI
     , group_concat(c.CompanyName) AS Companies
     , group_concat(c.CompanyId)   AS CompanyIds
FROM   Attendee AS a
JOIN   CompanyAttendeeRelation AS ca ON ca.AttendeeId = a.AttendeeId
JOIN   Company                 AS c  ON c.CompanyId = ca.CompanyId
GROUP  BY a.LastName, a.Firstname, a.PhotoURI;

（使用表别名使其更短且更易于阅读。）

NULL值从结果中排除。手册：

所有非NULL值的串联

根据手册，和 中元素 的 顺序
是任意的：CompanyIds``Companies

串联元素的顺序是任意的。

另请注意，“任意”与“随机”不同。group_concat像其他聚合函数一样，按收到的顺序处理行集。没有any ORDER BY，该顺序由执行的查询计划决定。有一个在关系数据库的表中没有的自然顺序（你可以不依赖于插入顺序 在所有
）。但是group_concat()，同一SELECT列表中的的两个实例均以 相同的
顺序处理行，因此in中的第一个IDCompanyIds对应于中的第一个名称Companies。

您可以ORDER BY在子查询中强加您的订单。这是实现细节，但极不可能更改。喜欢：

SELECT (LastName || " " || FirstName) AS AttendeeName
     , PhotoURI
     , group_concat(CompanyName) AS Companies
     , group_concat(CompanyId)   AS CompanyIds
FROM  (
   SELECT a.LastName, a.FirstName, a.PhotoURI, c.CompanyName, c.CompanyId
   FROM   Attendee AS a
   JOIN   CompanyAttendeeRelation AS ca ON ca.AttendeeId = a.AttendeeId
   JOIN   Company                 AS c  ON c.CompanyId = ca.CompanyId
   ORDER  BY 1,2,3,4,5  -- or whatever you need
   ) AS sub
GROUP  BY LastName, Firstname, PhotoURI;

关于（可选）序号的手册ORDER BY：

如果ORDER BY表达式是常数整数K，则该表达式被视为结果集的第K列的别名（列从1到从左到右编号）。

使用GROUP BY列表作为ORDER BY最佳结果的前导表达式。

在对派生表 进行 排序 后 ，不要对其进行任何可能重新排列的操作（例如将子查询联接到另一个表等）。