在我的数据库中，下表如下：
mysql> SELECT * FROM ultrasound_results;
+-----------+-----------------+--------+
| result_id | architecture_id | value |
+-----------+-----------------+--------+
| 29 | 1 | 0.00 |
| 29 | 2 | 90.00 |
| 29 | 3 | 14.14 |
| 30 | 1 | 0.00 |
| 30 | 2 | 90.00 |
| 30 | 3 | 14.14 |
| 31 | 1 | 0.00 |
| 31 | 2 | 90.00 |
| 31 | 3 | 14.14 |
| 32 | 1 | 21.49 |
| 32 | 2 | 20.72 |
| 32 | 3 | 63.69 |
| 333 | 1 | 41.34 |
| 333 | 2 | 22.71 |
| 333 | 3 | 112.13 |
+-----------+-----------------+--------+
15 rows in set (0.00 sec)
mysql> SELECT * FROM muscle_achitectures;
+----+------------------+
| id | name |
+----+------------------+
| 3 | Fascicle Length |
| 1 | Muscle Thickness |
| 2 | Pennation Angle |
+----+------------------+
3 rows in set (0.00 sec)
因此，通过使用以下查询：
SELECT ur.result_id, ma.name, ur.value
FROM ultrasound_results ur
INNER JOiN muscle_achitectures ma
ON ur.architecture_id=ma.id
ORDER BY ur.result_id
我得到以下输出：
+-----------+------------------+--------+
| result_id | name | value |
+-----------+------------------+--------+
| 29 | Fascicle Length | 14.14 |
| 29 | Muscle Thickness | 0.00 |
| 29 | Pennation Angle | 90.00 |
| 30 | Pennation Angle | 90.00 |
| 30 | Fascicle Length | 14.14 |
| 30 | Muscle Thickness | 0.00 |
| 31 | Muscle Thickness | 0.00 |
| 31 | Pennation Angle | 90.00 |
| 31 | Fascicle Length | 14.14 |
| 32 | Muscle Thickness | 21.49 |
| 32 | Pennation Angle | 20.72 |
| 32 | Fascicle Length | 63.69 |
| 333 | Pennation Angle | 22.71 |
| 333 | Fascicle Length | 112.13 |
| 333 | Muscle Thickness | 41.34 |
+-----------+------------------+--------+
15 rows in set (0.00 sec)
我真正要做的是编写一个SQL查询，它将给出如下输出：
+-----------+------------------+-------------------+-------------------+
| result_id | Fascicle Length | Muscle Thickness | Pennation Angle |
+-----------+------------------+-------------------+-------------------+
| 29 | 14.14 | 0.00 | 90.00 |
| 30 | 14.14 | 0.00 | 90.00 |
| 31 | 14.14 | 0.00 | 90.00 |
| 32 | 63.69 | 21.49 | 20.72 |
| 333 | 112.13 | 41.34 | 22.71 |
+-----------+------------------+-------------------+-------------------+
15 rows in set (0.00 sec)
我已经阅读了有关PIVOT的几篇文章，但是我不确定该怎么办以及我是否需要枢轴。有任何想法吗？
最佳答案
通过result_id和条件聚合进行分组：
SELECT
ur.result_id,
max(case ma.name when 'Fascicle Length' then ur.value end) `Fascicle Length`,
max(case ma.name when 'Muscle Thickness' then ur.value end) `Muscle Thickness`,
max(case ma.name when 'Pennation Angle' then ur.value end) `Pennation Angle`
FROM ultrasound_results ur INNER JOiN muscle_achitectures ma
ON ur.architecture_id=ma.id
GROUP BY ur.result_id
ORDER BY ur.result_id
关于mysql - 按ID对结果进行分组，并在MySql的一行中显示多个记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57151907/