USE test;
CREATE TEMPORARY TABLE `ta`(
`id` int,
`Value` int,
`Com` varchar(10),
`Comm` varchar(10)
);
INSERT INTO `ta` VALUES
(1,200,'工商银行','未处理'),
(2,300,'建行','已经支付未收到'),
(3,500,'商业银行','未处理'),
(4,200,'工商银行','未处理');SELECT
`Com`,
SUM(CASE WHEN `Comm`='未处理' THEN `Value` ELSE 0 END) '未处理',
SUM(CASE WHEN `Comm`='已经支付未收到' THEN `Value` ELSE 0 END) '已经支付未收到',
SUM(CASE WHEN `Comm`='已经支付且收到' THEN `Value` ELSE 0 END) '已经支付且收到'
FROM `ta`
GROUP BY `Com`;
+----------+--------+----------------+----------------+
| Com | 未处理 | 已经支付未收到 | 已经支付且收到 |
+----------+--------+----------------+----------------+
| 商业银行 | 1000 | 0 | 0 |
| 工商银行 | 800 | 0 | 0 |
| 建行 | 0 | 0 | 0 |
+----------+--------+----------------+----------------+
3 rows in set (0.00 sec)
CREATE TEMPORARY TABLE `ta`(
`id` int,
`Value` int,
`Com` varchar(10),
`Comm` varchar(10)
);
INSERT INTO `ta` VALUES
(1,200,'工商银行','未处理'),
(2,300,'建行','已经支付未收到'),
(3,500,'商业银行','未处理'),
(4,200,'工商银行','未处理');SELECT
`Com`,
SUM(CASE WHEN `Comm`='未处理' THEN `Value` ELSE 0 END) '未处理',
SUM(CASE WHEN `Comm`='已经支付未收到' THEN `Value` ELSE 0 END) '已经支付未收到',
SUM(CASE WHEN `Comm`='已经支付且收到' THEN `Value` ELSE 0 END) '已经支付且收到'
FROM `ta`
GROUP BY `Com`;
+----------+--------+----------------+----------------+
| Com | 未处理 | 已经支付未收到 | 已经支付且收到 |
+----------+--------+----------------+----------------+
| 商业银行 | 1000 | 0 | 0 |
| 工商银行 | 800 | 0 | 0 |
| 建行 | 0 | 0 | 0 |
+----------+--------+----------------+----------------+
3 rows in set (0.00 sec)
CREATE TEMPORARY TABLE `ta`(
`id` int,
`Value` int,
`Com` varchar(20),
`Comm` varchar(40)
);
INSERT INTO `ta` VALUES
(1,200,'工商银行','未处理'),
(2,300,'建行','已经支付未收到'),
(3,500,'商业银行','未处理'),
(4,200,'工商银行','未处理');SELECT
`Com`,
SUM(CASE WHEN `Comm`='未处理' THEN `Value` ELSE 0 END) '未处理',
SUM(CASE WHEN `Comm`='已经支付未收到' THEN `Value` ELSE 0 END) '已经支付未收到',
SUM(CASE WHEN `Comm`='已经支付且收到' THEN `Value` ELSE 0 END) '已经支付且收到'
FROM `ta`
GROUP BY `Com`;
+----------+--------+----------------+----------------+
| Com | 未处理 | 已经支付未收到 | 已经支付且收到 |
+----------+--------+----------------+----------------+
| 商业银行 | 500 | 0 | 0 |
| 工商银行 | 400 | 0 | 0 |
| 建行 | 0 | 300 | 0 |
+----------+--------+----------------+----------------+
3 rows in set (0.00 sec)
哈.前一个字段设得小了点....