mysql按拆分的和值组

pgky5nke 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(288)

这个问题在这里已经有答案了：

如何在mysql中返回pivot表输出(9个答案）
两年前关门了。
mysql总和分组依据

ID  NAME    TYPE    TOTAL
1   user1   1       5
2   user1   2       10
3   user2   1       5
4   user2   1       10
5   user3   3       20
6   user2   3       10
.........

按名称和类型列出的组总数
我想要这个结果

NAME    TYPE1   TYPE2   TYPE3   TYPE4   TYPE5
user1   5       10      0       0       0
user2   15      0       10      0       0
user3   0       0       20      0       0
---------------------------------------------
Total   20      10      30      0       0

php和mysql（（max type 9））谢谢！！！！
演示表

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);

mysql php

来源：https://stackoverflow.com/questions/48131044/mysql-sum-value-group-by-split

1条答案

按热度按时间

w9apscun1#

这应该可以做到：

SELECT
    tt.`name` AS NAME,
    SUM(CASE WHEN tt.type = 1 THEN tt.total ELSE 0 END) AS TYPE1,
    SUM(CASE WHEN tt.type = 2 THEN tt.total ELSE 0 END) AS TYPE2,
    SUM(CASE WHEN tt.type = 3 THEN tt.total ELSE 0 END) AS TYPE3,
    SUM(CASE WHEN tt.type = 4 THEN tt.total ELSE 0 END) AS TYPE4,
    SUM(CASE WHEN tt.type = 5 THEN tt.total ELSE 0 END) AS TYPE5,
    SUM(CASE WHEN tt.type = 6 THEN tt.total ELSE 0 END) AS TYPE6,
    SUM(CASE WHEN tt.type = 7 THEN tt.total ELSE 0 END) AS TYPE7,
    SUM(CASE WHEN tt.type = 8 THEN tt.total ELSE 0 END) AS TYPE8,
    SUM(CASE WHEN tt.type = 9 THEN tt.total ELSE 0 END) AS TYPE9
FROM
    test_table AS tt
GROUP BY
    tt.`name`

赞(0）回复(0）举报 2021-06-21

我来回答

mysql按拆分的和值组

1条答案

相关问题

热门标签

最新问答