如何从表中获取所有值，其中包括自身作为同一表中的另一个值邻居字段 - How to get all values from a table which includes itself as another value neighbour field at the same table?

How to get all values from a table which includes itself as another value neighbour field at the same table?

我有3个表。第一个表保存带有id号的"group_names"。第二个表在element_name旁边保存带有id号和group_id号的"groups_elements"。第三个表保存group_elements之间的关系，其中包括element_id, sub_element_id。我希望获得concat group_name, element_name和element_id数字sub_elements数字。这里是sqlfiddler链接http://sqlfiddle.com/#!2/44f63

我希望得到这样的结果:

Solid Soil  5,6,7
Liquid Oil  8,9,10

你可以这么做

SELECT  CONCAT(g.group_name,' , ',e.element_name)
, GROUP_CONCAT(DISTINCT  er.sub_element_id)
FROM groups g
JOIN elements e ON(g.id = e.group_id)
JOIN element_subelement_relation  er ON(er.element_id= e.id)
GROUP BY g.group_name, e.element_name

演示

编辑注释

SELECT  CONCAT(g.group_name,' , ',e.element_name) `group_elements`
, GROUP_CONCAT(DISTINCT  er.`sub_element_id`) `ids`
FROM groups g
LEFT JOIN elements e ON(g.id = e.group_id)
LEFT JOIN element_subelement_relation  er ON(er.element_id= e.id)
GROUP BY g.group_name, e.element_name
HAVING group_elements IS NOT NULL
ORDER BY g.group_name

演示

using group_concat()

SELECT g.group_name, group_concat(sub_element_id) as items
FROM elements e INNER JOIN element_subelement_relation er
ON e.id = er.element_id INNER JOIN groups g
ON g.id = e.group_id
GROUP BY g.group_name

演示:http://sqlfiddle.com/# !2/44f63/21

直接在子id上连接并使用group_concat:

select
  concat(g.group_name, ' ', e.element_name) as name,
  group_concat(sub_element_id order by sub_element_id) as sub_elements
from elements e
inner join groups g on g.id = e.group_id
inner join element_subelement_relation r on r.element_id = e.id
group by name
order by sub_elements, name;

SQL小提琴:http://sqlfiddle.com/! 2/44f63/31。

您可能还想尝试这些:

SELECT g.group_name, e.element_name, concat(g.id,",", e.id,",",esr.id) as ID
FROM element_subelement_relation esr
LEFT JOIN elements e ON(esr.element_id = e.id)
LEFT JOIN groups g ON(e.group_id = g.id)