统计sql语句

iteye_11910

于 2011-09-23 11:34:37 发布

阅读量83

点赞数

CC 4.0 BY-SA版权

文章标签：统计查询

本文链接：https://blog.youkuaiyun.com/iteye_11910/article/details/82157708

drop table if exists testGroup;

create table testGroup(id int,goods_name varchar(50),num int,bill_type int);
insert into testGroup(id,goods_name,num,bill_type) values(1,"商品1",11,2);
insert into testGroup(id,goods_name,num,bill_type) values(1,"商品1",100,1);
insert into testGroup(id,goods_name,num,bill_type) values(2,"商品2",33,1);
insert into testGroup(id,goods_name,num,bill_type) values(2,"商品2",44,1);
insert into testGroup(id,goods_name,num,bill_type) values(5,"商品5",66,1);

select * from testGroup;
select sum(t.num),t.id,t.goods_name from testGroup t group by t.id
select sum(case bill_type when 1 then t.num when 2 then -t.num end ) as totalNum,t.id,t.goods_name from testGroup t group by t.id