mysql统计期初库存_商品库存统计 - 学习 进步 - OSCHINA - 中文开源技术交流社区...

创建表

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

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