SQL多查询需求：将物料交易表转换为指定透视表格式

1. 修正后的SQL查询 #

核心是使用条件聚合函数，按Item Name分组后分别统计两种交易类型的总重量：

通用SQL写法（支持多数数据库） #

SELECT
    `Item Name`,
    SUM(CASE WHEN `Transaction Type` = 'Issue Material' THEN Weight ELSE 0 END) AS `Total Issue Weight`,
    SUM(CASE WHEN `Transaction Type` = 'Return Material' THEN Weight ELSE 0 END) AS `Total Return Weight`
FROM material_transaction
GROUP BY `Item Name`
ORDER BY `Item Name`;

MySQL简洁写法 #

SELECT
    `Item Name`,
    SUM(IF(`Transaction Type` = 'Issue Material', Weight, 0)) AS `Total Issue Weight`,
    SUM(IF(`Transaction Type` = 'Return Material', Weight, 0)) AS `Total Return Weight`
FROM material_transaction
GROUP BY `Item Name`
ORDER BY `Item Name`;

2. 修正后的PHP代码示例（PDO版） #

确保SQL查询正确，同时规范结果输出逻辑：

<?php
// 数据库连接配置
$dsn = 'mysql:host=localhost;dbname=你的数据库名;charset=utf8mb4';
$username = '你的用户名';
$password = '你的密码';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 执行修正后的统计SQL
    $sql = "
        SELECT
            `Item Name`,
            SUM(CASE WHEN `Transaction Type` = 'Issue Material' THEN Weight ELSE 0 END) AS `Total Issue Weight`,
            SUM(CASE WHEN `Transaction Type` = 'Return Material' THEN Weight ELSE 0 END) AS `Total Return Weight`
        FROM material_transaction
        GROUP BY `Item Name`
        ORDER BY `Item Name`
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    // 输出Table B格式的统计结果
    echo "<table border='1' cellpadding='8'>";
    echo "<tr><th>Item Name</th><th>Total Issue Weight</th><th>Total Return Weight</th></tr>";
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['Item Name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Total Issue Weight']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Total Return Weight']) . "</td>";
        echo "</tr>";
    }
    echo "</table>";

} catch(PDOException $e) {
    die("数据库错误: " . $e->getMessage());
}
?>

3. 关键修正说明 #

• SQL层面：用条件聚合替代普通分组，确保同一物料下的Issue和Return重量被独立统计，避免数据混淆。
• PHP层面：确保执行正确的SQL语句，同时通过htmlspecialchars()防止XSS风险，规范字段映射逻辑。

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