如何调整MySQL查询实现同一用户多条评论的合并展示？

现有以下MySQL表结构及数据：

CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reference` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `person` (`id`, `reference`, `email`) VALUES
(1, 'PK001',    'paulk@gmail.com');

CREATE TABLE `review` (
  `id` int NOT NULL AUTO_INCREMENT,
  `review_type` varchar(255) NOT NULL,
  `review_body` varchar(255) NOT NULL,
  `person_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `review` (`id`, `review_type`, `review_body`, `person_id`) VALUES
(1, 'Personality',  'He has a great personality!',  1),
(2, 'Skills',   'He has multiple skills!',  1);

运行如下PHP代码：

$sql = "SELECT * FROM person, review WHERE person.id = review.person_id;";
$result = $con->query($sql);

while($row = $result->fetch_assoc())  {
    echo $row['review_body'];
    echo ' | ';
    echo $row['review_body'];
    echo '<br>';
}

当前输出：

He has multiple skills! | He has multiple skills!
He has a great personality! | He has a great personality!

期望输出：

He has multiple skills! | He has a great personality!

需要调整MySQL查询语句实现上述期望效果。

方法1：条件聚合（推荐） #

利用CASE语句结合聚合函数，将同一用户的不同类型评论合并到同一行：

SELECT 
    MAX(CASE WHEN review_type = 'Skills' THEN review_body END) AS skills_review,
    MAX(CASE WHEN review_type = 'Personality' THEN review_body END) AS personality_review
FROM person
INNER JOIN review ON person.id = review.person_id
WHERE person.id = 1; -- 如需查询所有用户，可删除此WHERE条件

对应的PHP代码调整为：

$sql = "SELECT 
            MAX(CASE WHEN review_type = 'Skills' THEN review_body END) AS skills_review,
            MAX(CASE WHEN review_type = 'Personality' THEN review_body END) AS personality_review
        FROM person
        INNER JOIN review ON person.id = review.person_id
        WHERE person.id = 1;";
$result = $con->query($sql);

if($row = $result->fetch_assoc())  {
    echo $row['skills_review'];
    echo ' | ';
    echo $row['personality_review'];
    echo '<br>';
}

方法2：自连接 #

若评论类型固定，可通过自连接将不同类型的评论关联到同一行：

SELECT 
    r_skills.review_body AS skills_review,
    r_personality.review_body AS personality_review
FROM person p
INNER JOIN review r_skills ON p.id = r_skills.person_id AND r_skills.review_type = 'Skills'
INNER JOIN review r_personality ON p.id = r_personality.person_id AND r_personality.review_type = 'Personality'
WHERE p.id = 1;

PHP代码逻辑与方法1一致，直接输出两个字段即可。

内容的提问来源于stack exchange，提问作者michaelmcgurk