商品库存统计

为什么80%的码农都做不了架构师？>>>   

创建表

CREATE TABLE "public"."test" (
	"product" varchar COLLATE "default",
	"flag" int2,
	"qtn" int2
)
WITH (OIDS=FALSE);

插入数据

insert into "public"."test" ( "product", "flag", "qtn") values ( 'one', '0', '100');
insert into "public"."test" ( "product", "flag", "qtn") values ( 'one', '1', '30');
insert into "public"."test" ( "product", "flag", "qtn") values ( 'one', '0', '30');
insert into "public"."test" ( "product", "flag", "qtn") values ( 'one', '1', '50');
insert into "public"."test" ( "product", "flag", "qtn") values ( 'two', '0', '30');
insert into "public"."test" ( "product", "flag", "qtn") values ( 'two', '1', '10');

第一反应时，分别把进库的和出库的统计出来，然后做减法，于是有了下面的sql

select aaa.product,(aaa.aa-bbb.bb) from 
(select product,sum(qtn) aa from test where flag = 0 group by product ) aaa
join
(
select product,sum(qtn) bb from test where flag = 1 group by product 
) bbb on aaa.product = bbb.product

可是，仔细想想，这样的sql看起来，好难看，效率也不高，于是有了下面的sql

select product,
sum(case when flag =1 then -qtn
else qtn end)
 aa from test group by product

结果是一样的，这才是我想要的，看来写东西的时候不能想当然。

转载于:https://my.oschina.net/sucre/blog/736391