mysql - 一行显示mysql结果

Question

mysql> select * from facts;
+----+---------+------------+---------+
| id | fact_id | fact_value | host_id |
+----+---------+------------+---------+
|  1 |       1 | rh5        |       1 |
|  2 |       1 | rh4        |       2 |
|  3 |       2 | virtual    |       1 |
|  4 |       2 | virtual    |       2 |
|  5 |       3 | rack 2     |       1 |
+----+---------+------------+---------+

mysql> select * from hosts;
+---------+-----------+
| host_id | host_name |
+---------+-----------+
|       1 | bellagio  |
|       2 | mirage    |
+---------+-----------+

我使用的查询执行以下操作:

mysql> select host_name,fact_value from hosts as a left join facts as b on 
     >   b.host_id=a.host_id and b.fact_id in (1,2,3);

+-----------+------------+
| host_name | fact_value |
+-----------+------------+
| bellagio  | rh5        |
| bellagio  | virtual    |
| bellagio  | rack 2     |
| mirage    | rh4        |
| mirage    | virtual    |
+-----------+------------+

我希望结果为每个主机打印一行，请注意它如何在单独的行上打印每个 fact_value。我有 IN 子句的原因是这个表有超过 40 个可能的列用于每个主机。我只想要一把(在这个例子中我选择了 3 个)。

这就是我要找的东西。

+-----------+--------+-----------+-----------+
| host_name | value1 |  value 2  |  value 3  |
+-----------+--------+-----------+-----------+
| bellagio  | rh5    |  virtual  |  rack 2   |
| mirage    | rh4    |  virtual  |  NULL     |
+-----------+--------+-----------+-----------+

Answer 1

SELECT GROUP_CONCAT(fact_value)...
...
GROUP BY host_name