如何基于相同值对MySQL表中WIDTH和HEIGHT列的Quantity求和？

方法一：合并列后分组求和（推荐） #

假设你的数据表名为size_data，包含WIDTH、HEIGHT、Quantity三个字段，通过UNION ALL将WIDTH和HEIGHT的数值合并为同一列，再分组求和：

SELECT value, SUM(quantity) AS total_quantity
FROM (
    -- 提取WIDTH列的数值及对应Quantity
    SELECT WIDTH AS value, Quantity FROM size_data
    UNION ALL
    -- 提取HEIGHT列的数值及对应Quantity
    SELECT HEIGHT AS value, Quantity FROM size_data
) AS combined
GROUP BY value
ORDER BY value;

该查询会直接输出每个数值对应的总Quantity，完全匹配你给出的示例结果（如10对应26、15对应37等）。

方法二：条件聚合（保留分列统计） #

如果需要同时查看每个数值在WIDTH、HEIGHT各自的总和，再计算总计，可使用条件聚合：

SELECT
    COALESCE(w.value, h.value) AS value,
    COALESCE(w.width_total, 0) AS width_quantity,
    COALESCE(h.height_total, 0) AS height_quantity,
    COALESCE(w.width_total, 0) + COALESCE(h.height_total, 0) AS total_quantity
FROM (
    SELECT WIDTH AS value, SUM(Quantity) AS width_total
    FROM size_data
    GROUP BY WIDTH
) w
LEFT JOIN (
    SELECT HEIGHT AS value, SUM(Quantity) AS height_total
    FROM size_data
    GROUP BY HEIGHT
) h ON w.value = h.value
UNION
SELECT
    h.value AS value,
    COALESCE(w.width_total, 0) AS width_quantity,
    h.height_total AS height_quantity,
    COALESCE(w.width_total, 0) + h.height_total AS total_quantity
FROM (
    SELECT WIDTH AS value, SUM(Quantity) AS width_total
    FROM size_data
    GROUP BY WIDTH
) w
RIGHT JOIN (
    SELECT HEIGHT AS value, SUM(Quantity) AS height_total
    FROM size_data
    GROUP BY HEIGHT
) h ON w.value = h.value
WHERE w.value IS NULL
ORDER BY value;

注：此写法兼容MySQL 8.0以下不支持FULL OUTER JOIN的版本。

如果需要在代码层处理数据，可按以下步骤实现：

1. 连接数据库并获取数据（PDO示例） #

<?php
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo->query("SELECT WIDTH, HEIGHT, Quantity FROM size_data");
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}
?>

2. 遍历计算总和 #

<?php
$totalMap = [];

foreach ($data as $row) {
    $width = $row['WIDTH'];
    $height = $row['HEIGHT'];
    $quantity = $row['Quantity'];

    // 累加WIDTH对应数值的Quantity
    $totalMap[$width]['width'] = ($totalMap[$width]['width'] ?? 0) + $quantity;
    // 初始化height值（避免未定义索引）
    if (!isset($totalMap[$width]['height'])) $totalMap[$width]['height'] = 0;

    // 累加HEIGHT对应数值的Quantity
    $totalMap[$height]['height'] = ($totalMap[$height]['height'] ?? 0) + $quantity;
    // 初始化width值
    if (!isset($totalMap[$height]['width'])) $totalMap[$height]['width'] = 0;
}

// 整理结果并排序
$result = [];
foreach ($totalMap as $value => $quantities) {
    $result[] = [
        'value' => $value,
        'width_quantity' => $quantities['width'],
        'height_quantity' => $quantities['height'],
        'total_quantity' => $quantities['width'] + $quantities['height']
    ];
}
usort($result, fn($a, $b) => $a['value'] - $b['value']);

// 输出结果
print_r($result);
?>

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