例如：某型号库存 = 上期结余 + 本期入库 - 本期出库
这样就会发现 用这俩个表是实现不了你的需求的
如果上期并不存在结余则 库存 = 本期入库 - 本期出库
那么依照这个方法可以得到SQL为：
select sum(b.数量) - sum(a.数量 ),a.物料号,a.名称,a..型号
from a,b
where a.型号 = b.型号 and a.物料号 = b.物料号
group by a.物料号,a.名称,a..型号
建表和视图语句：
DROP TABLE IF EXISTS `tab`
CREATE TABLE `tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NULL DEFAULT NULL,
`date` datetime NULL DEFAULT NULL,
`instructions` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`amount` decimal(18, 2) NULL DEFAULT NULL,
`type` tinyint(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic
create view tab_v as
select
`tab`.`userid` AS `userid`,
date_format( `tab`.`date`, '%Y-%m' ) AS `ym`,
`tab`.`instructions` AS `instructions`,
`tab`.`amount` AS `amount`,
`tab`.`type` AS `type`
from
`tab`
查询语句：
select t0.userid 用户ID,
t0.ym 年月,
t1.cnt 本月收入笔数,
t2.instructions 本月最大收入项目,
t2.amount 本月最大收入金额,
t3.instructions 本月最小收入项目,
t3.amount 本月最小收入金额,
t4.cnt 本月支出笔数,
t5.instructions 本月最大支出项目,
t5.amount 本月最大支出金额,
t6.instructions 本月最小支出项目,
t6.amount 本月最小支出金额,
t7.cnt 本月结余
from (select distinct a.userid,
a.ym
from tab_v a) t0
left join (select a.userid,
a.ym,
count(1) cnt
from tab_v a
where a.type = 2
group by a.userid,
a.ym) t1
on t0.userid = t1.userid
and t0.ym = t1.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
max(a.amount) amount
from tab_v a
where a.type = 2
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t2
on t0.userid = t2.userid
and t0.ym = t2.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
min(a.amount) amount
from tab_v a
where a.type = 2
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t3
on t0.userid = t3.userid
and t0.ym = t3.ym
left join (select a.userid,
a.ym,
count(1) cnt
from tab_v a
where a.type = 1
group by a.userid,
a.ym) t4
on t0.userid = t4.userid
and t0.ym = t4.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
max(a.amount) amount
from tab_v a
where a.type = 1
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t5
on t0.userid = t5.userid
and t0.ym = t5.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
min(a.amount) amount
from tab_v a
where a.type = 1
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t6
on t0.userid = t6.userid
and t0.ym = t6.ym
left join (select a.userid,
a.ym,
sum(case
when type = 1 then
-1 * a.amount
when 2 then
a.amount
end) cnt
from tab_v a
group by a.userid,
a.ym) t7
on t0.userid = t7.userid
and t0.ym = t7.ym
只能做到这个效果了
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）