MySQL：使用单行查询多列的性能

Question

我的表格简化了这样的事情......

Table Categories
cat_id   | cat_name
-------------------
1        | Baseball
2        | Hockey
3        | Football

Table Day
day_id   | day_name
--------------------
1        | Friday
2        | Saturday
3        | Sunday

Table Day_Categories
day_id    | cat_id
-------------------
1         | 1
1         | 2
2         | 2
2         | 3
3         | 1
3         | 2
3         | 3

我希望我的结果数据看起来像这样......

"Friday","Baseball, Hockey"
"Saturday","Hockey, Football"
"Sunday", "Baseball, Hockey, Football"

它不一定需要采用三行格式。我只需要相同的结果数据。

从大量数据的性能角度来看，实现这一结果的最佳方法是什么？

Answer 1

请试试这个

SELECT day_name, GROUP_CONCAT ( cat_name ) as categories 
FROM Days as d
LEFT JOIN Days_Categories as dnc ON d.day_id = dnc.day_id 
LEFT JOIN Categories as c ON c.cat_id = dnc.cat_id 
GROUP BY ( dnc.day_id)