MariaDB动态透视表SQL转PHP(PDO)实现遇语法错误求助

错误原因分析 #

你写的SQL语句存在两个核心问题：

• 单引号嵌套错误：CONCAT('SUM(CASE WHEN test_id = 'test_id'...')里的内层单引号没有转义，导致SQL语法被截断，触发1064错误。
• GROUP BY逻辑错误：原需求是按url_id分组，你写的GROUP BY id不符合业务逻辑。

正确的PDO实现步骤 #

我们可以分两步实现原SQL的逻辑，避免直接执行多语句SQL的限制：

1. 获取动态生成的透视列SQL片段
2. 拼接完整查询语句并执行

以下是完整代码：

// 假设你已经初始化了PDO连接$pdo
try {
    // 第一步：获取动态列的SQL片段
    $stmt = $pdo->query("SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'SUM(CASE WHEN `test_id`=''', test_id, ''' THEN `points` ELSE 0 END) AS `', test_id, '`'
    )) AS pivot_columns FROM test_answered WHERE checked = '1'");
    
    $pivotColumns = $stmt->fetch(PDO::FETCH_ASSOC)['pivot_columns'];
    
    // 处理没有符合条件的test_id的情况
    if (empty($pivotColumns)) {
        echo "没有可用的透视列数据";
        exit;
    }
    
    // 第二步：拼接完整的查询语句并执行
    $finalSql = "SELECT url_id, $pivotColumns FROM test_answered WHERE checked = '1' GROUP BY url_id";
    $resultStmt = $pdo->query($finalSql);
    
    // 获取结果
    $pivotData = $resultStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // 输出或处理结果
    print_r($pivotData);
} catch (PDOException $e) {
    die("执行错误: " . $e->getMessage());
}

关键细节说明 #

• 单引号转义：在PDO的字符串中，用两个单引号''表示SQL里的一个单引号，避免嵌套冲突。
• 服务器端预处理：如果需要用预处理方式执行动态SQL，需确保PDO关闭了模拟预处理（默认可能开启），可以添加：

  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  

• 空值处理：当没有checked='1'的记录时，pivot_columns会为空，需要提前判断避免生成无效SQL。

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