Mysql，如何合并连接查询的结果？

Question

我有这些表:

Objects
ID | Name
----------
1    name1

Tags
ID | ObjectId | Tag
--------------------
1    1          tag1
2    1          tag2

Attributes
ID | ObjectId | Key | Value
--------------------
1    1          key1  value1  
2    1          key2  value2

我想编写一个查询，它会给出一行，例如:

id = 1
name = name1
tags = tag1,tag2
attributes = key1=value1,key2=value2

但是，这个查询:

SELECT      o.name, 
            GROUP_CONCAT(t.tag) as tags,
            GROUP_CONCAT(CONCAT(a,'=',a.value)) as attributes
FROM        objects o 
LEFT JOIN   attributes a on a.ObjectId = o.id
LEFT JOIN   tags t on t.ObjectId = o.id
WHERE       p.id = 1

给我这个单行结果，带有重复的标签/属性值

id = 1
name = name1
tags = tag1,tag1,tag2,tag2
attributes = key1=value1,key2=value2,key1=value1,key2=value2

Answer 1

您的解决方案的问题在于，您正在根据所有相关属性连接所有相关标签，从而有效地产生交叉连接。

相反，您可以使用标量子查询。例如，您可以将其写为:

select
  id,
  name,
  (SELECT GROUP_CONCAT(t.tag) FROM tags t on t.ObjectId = o.id) as tags,
  (SELECT GROUP_CONCAT(CONCAT(a.key,'=',a.value)) 
     FROM attributes a on a.ObjectId = o.id) as attributes
FROM objects o
WHERE id = 1