MySQL LEFT JOIN能否添加多个CASE语句？语法报错求解

问题背景 #

现有可运行的SQL查询，从clients表获取用户数据，通过LEFT JOIN关联likes表生成isLiked字段，需要新增来自viewed表的hasViewed字段（viewed表结构与likes一致），但尝试添加时出现语法错误。

错误分析 #

你遇到的语法错误由两个问题导致：

• 表别名使用了数字2：SQL中标识符（包括表别名）不能以数字开头，必须以字母或下划线开头
• JOIN语法错误：多个LEFT JOIN之间不能用逗号分隔，需使用标准的连续LEFT JOIN语法

修正后的完整代码 #

$logged_user = mysqli_real_escape_string($conn, $_GET["logged_user"]);

$sql = "
SELECT 
    u.*, 
    (
        3959 * acos(
            cos(radians(52.41357)) * 
            cos(radians(u.lat)) * 
            cos(radians(u.lng) - radians(-1.51314816)) + 
            sin(radians(52.41357)) * 
            sin(radians(u.lat))
        )
    ) AS distance,
    CASE 
        WHEN l.to_user IS NOT NULL THEN 'true'
        ELSE 'false'
    END AS isLiked,
    CASE 
        WHEN v.to_user IS NOT NULL THEN 'true'
        ELSE 'false'
    END AS hasViewed
FROM clients u
LEFT JOIN likes l 
    ON u.userID = l.to_user 
    AND l.from_user = '$logged_user'
LEFT JOIN viewed v 
    ON u.userID = v.to_user 
    AND v.from_user = '$logged_user'
HAVING distance < 10
ORDER BY distance 
LIMIT 99";

$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

$userOnlineStatus = array();
while ($row = mysqli_fetch_assoc($result)) {
    $userOnlineStatus[] = $row;
}

echo json_encode($userOnlineStatus);

关键修正点 #

• 将viewed表的别名改为合法的v（替换原错误的数字别名2）
• 移除LEFT JOIN之间的逗号，使用标准的LEFT JOIN viewed v ON ...语法
• 确保hasViewed字段的CASE语句对应正确的表别名v

可选优化 #

如果想要更简洁的写法，可以用IF()函数替代CASE语句，效果完全一致：

IF(l.to_user IS NOT NULL, 'true', 'false') AS isLiked,
IF(v.to_user IS NOT NULL, 'true', 'false') AS hasViewed

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