MySQL+PHP实现按NAME和TYPE分组的SUM行转列统计（最大TYPE为9）

没问题，咱们直接上解决方案，分MySQL查询和PHP展示两部分来实现你要的效果：

1. MySQL查询语句 #

首先需要写出能直接得到行列转置结果的SQL，这里用SUM(IF())来实现列转行，同时通过UNION ALL拼接总计行：

SELECT 
    name,
    SUM(IF(type = '1', total, 0)) AS TYPE1,
    SUM(IF(type = '2', total, 0)) AS TYPE2,
    SUM(IF(type = '3', total, 0)) AS TYPE3,
    SUM(IF(type = '4', total, 0)) AS TYPE4,
    SUM(IF(type = '5', total, 0)) AS TYPE5,
    SUM(IF(type = '6', total, 0)) AS TYPE6,
    SUM(IF(type = '7', total, 0)) AS TYPE7,
    SUM(IF(type = '8', total, 0)) AS TYPE8,
    SUM(IF(type = '9', total, 0)) AS TYPE9
FROM test_table
GROUP BY name
UNION ALL
SELECT 
    'Total' AS name,
    SUM(IF(type = '1', total, 0)) AS TYPE1,
    SUM(IF(type = '2', total, 0)) AS TYPE2,
    SUM(IF(type = '3', total, 0)) AS TYPE3,
    SUM(IF(type = '4', total, 0)) AS TYPE4,
    SUM(IF(type = '5', total, 0)) AS TYPE5,
    SUM(IF(type = '6', total, 0)) AS TYPE6,
    SUM(IF(type = '7', total, 0)) AS TYPE7,
    SUM(IF(type = '8', total, 0)) AS TYPE8,
    SUM(IF(type = '9', total, 0)) AS TYPE9
FROM test_table;

语句说明： #

• 第一部分按name分组，对每个type对应的total求和，没有数据的补0；
• 第二部分单独统计所有数据的各type总和，name字段设为Total，用UNION ALL合并到结果集里。

2. PHP代码实现展示 #

这里用mysqli扩展来连接数据库并输出表格，你可以根据自己的环境调整连接参数：

<?php
// 数据库连接参数
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$dbname = 'your_database';

// 创建连接
$conn = new mysqli($host, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 执行查询
$sql = "SELECT 
            name,
            SUM(IF(type = '1', total, 0)) AS TYPE1,
            SUM(IF(type = '2', total, 0)) AS TYPE2,
            SUM(IF(type = '3', total, 0)) AS TYPE3,
            SUM(IF(type = '4', total, 0)) AS TYPE4,
            SUM(IF(type = '5', total, 0)) AS TYPE5,
            SUM(IF(type = '6', total, 0)) AS TYPE6,
            SUM(IF(type = '7', total, 0)) AS TYPE7,
            SUM(IF(type = '8', total, 0)) AS TYPE8,
            SUM(IF(type = '9', total, 0)) AS TYPE9
        FROM test_table
        GROUP BY name
        UNION ALL
        SELECT 
            'Total' AS name,
            SUM(IF(type = '1', total, 0)) AS TYPE1,
            SUM(IF(type = '2', total, 0)) AS TYPE2,
            SUM(IF(type = '3', total, 0)) AS TYPE3,
            SUM(IF(type = '4', total, 0)) AS TYPE4,
            SUM(IF(type = '5', total, 0)) AS TYPE5,
            SUM(IF(type = '6', total, 0)) AS TYPE6,
            SUM(IF(type = '7', total, 0)) AS TYPE7,
            SUM(IF(type = '8', total, 0)) AS TYPE8,
            SUM(IF(type = '9', total, 0)) AS TYPE9
        FROM test_table;";

$result = $conn->query($sql);

// 输出HTML表格
if ($result->num_rows > 0) {
    echo "<table border='1'>";
    // 输出表头
    echo "<tr><th>NAME</th><th>TYPE1</th><th>TYPE2</th><th>TYPE3</th><th>TYPE4</th><th>TYPE5</th><th>TYPE6</th><th>TYPE7</th><th>TYPE8</th><th>TYPE9</th></tr>";
    // 输出数据行
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row["name"] . "</td>";
        echo "<td>" . $row["TYPE1"] . "</td>";
        echo "<td>" . $row["TYPE2"] . "</td>";
        echo "<td>" . $row["TYPE3"] . "</td>";
        echo "<td>" . $row["TYPE4"] . "</td>";
        echo "<td>" . $row["TYPE5"] . "</td>";
        echo "<td>" . $row["TYPE6"] . "</td>";
        echo "<td>" . $row["TYPE7"] . "</td>";
        echo "<td>" . $row["TYPE8"] . "</td>";
        echo "<td>" . $row["TYPE9"] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "0 结果";
}

// 关闭连接
$conn->close();
?>

测试用表结构与数据 #

你提供的测试表可以直接用下面的SQL创建并插入数据：

CREATE TABLE `test_table` (
 `id` int(10) NOT NULL,
 `name` varchar(10) NOT NULL,
 `type` varchar(1) NOT NULL,
 `total` int(10) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

INSERT INTO `test_table` (`id`, `name`, `type`, `total`) VALUES
(1, 'user1', '1', 5),(2, 'user1', '5', 5),(3, 'user1', '2', 5),(4, 'user1', '3', 5),(5, 'user1', '4', 5),
(6, 'user1', '1', 10),(7, 'user1', '2', 15),(8, 'user1', '3', 15),(9, 'user1', '4', 15),(10, 'user1', '5', 15),
(11, 'user2', '1', 5),(12, 'user2', '5', 5),(13, 'user2', '2', 5),(14, 'user2', '3', 5),(15, 'user2', '4', 5),
(16, 'user2', '1', 10),(17, 'user2', '2', 15),(18, 'user2', '3', 15),(19, 'user2', '4', 15),(20, 'user2', '5', 15),
(21, 'user3', '1', 5),(22, 'user3', '5', 5),(23, 'user3', '2', 5),(24, 'user3', '3', 5),(25, 'user3', '4', 5),
(26, 'user3', '1', 10),(27, 'user3', '2', 15),(28, 'user3', '3', 15),(29, 'user3', '4', 15),(30, 'user3', '5', 15),
(31, 'user4', '1', 5),(32, 'user4', '5', 5),(33, 'user4', '2', 5),(34, 'user4', '3', 5),(35, 'user4', '4', 5),
(36, 'user4', '1', 10),(37, 'user4', '2', 15),(38, 'user4', '3', 15),(39, 'user4', '4', 15),(40, 'user4', '5', 15),
(41, 'user5', '1', 5),(42, 'user5', '5', 5),(43, 'user5', '2', 5),(44, 'user5', '3', 5),(45, 'user5', '4', 5),
(46, 'user5', '1', 10),(47, 'user5', '2', 15),(48, 'user5', '3', 15),(49, 'user5', '4', 15),(50, 'user5', '5', 15);

把这些代码放到你的环境里运行，就能得到你想要的分组求和+行列转置+总计行的效果了。

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