求MYSQL统计的语句

[liushengmz]

大家好，有一些MYSQL的数据统计麻烦一下大家。有如下表格
id gtype mtype money
1 1 1 20
2 1 1 30
3 1 2 20
4 2 0 43
5 2 0 45
6 1 0 35
7 2 4 40
8 1 5 70
9 1 2 30
10 1 0 43
11 2 1 37
12 2 2 34

MYSQL 创建 表 的语句如下：

create table `testsum` (
`id` double ,
`gtype` double ,
`mtype` double ,
`money` float
);
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('1','1','1','20');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('2','1','1','30');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('3','1','2','20');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('4','2','0','43');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('5','2','0','45');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('6','1','0','35');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('7','2','4','40');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('8','1','5','70');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('9','1','2','30');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('10','1','0','43');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('11','2','1','37');
insert into `testsum` (`id`, `gtype`, `mtype`, `money`) values('12','2','2','34');

需求：
按gtype进行分组，把mtype=0和mtype<>0的money分别求各统计出来，
分成两列,也就是结果如下
gtype mtype0money mtype2money
1 78 170
2 88 110

请大家帮帮忙。如果能用一句SQL语句就可以搞定那就最好，
如果不成，用存储过程也是可以的。

或者给我思路，让我自己去实现也行。麻烦大家了。

[百年树人]

try

select 
  gtype,
  sum(case when mtype=0 then `money` else 0 end) as `mtype0money`,
  sum(case when mtype<>0 then `money` else 0 end) as `mtype2money`
from
  `testsum`
group by
  gtype

[华夏小卒]

[Quote=引用 1 楼 josy 的回复:]
try

SQL code
select
gtype,
sum(case when mtype=0 then `money` else 0 end) as `mtype0money`,
sum(case when mtype<>0 then `money` else 0 end) as `mtype2money`
from
`testsum`
group by
……
[/Quote]up

[王向飞]

localhost@root>select gtype, SUM(case when mtype=0 then money else 0 end ) as m1
 , SUM(case when mtype<>0 then money else 0 end ) as m1
    ->  from testsum group by gtype;
+-------+------+------+
| gtype | m1   | m1   |
+-------+------+------+
|     1 |   78 |  170 |
|     2 |   88 |  111 |
+-------+------+------+
2 rows in set (0.00 sec)

[liushengmz]

对。。。。CASE WHEN。。。。。忘得这么快啊。。。感谢楼上的两位！

[飘零一叶]

蹭分 ，不懂MYSQL。语法和msSQL差不多