表结构:表history三个字段part,date,qty。表part_mst 也是三个字段:part,name,on_hand
表part_mst的on_hand的字段是指现在(也就是今天的在库数量),表history的qty字段是指:进货多少或者出货多少,如果是进货就用正数的表示,如果是出货就用负数表示。表history的date字段是指是:进货或出货的日期。
画面是一个可以输入开始时间和结束时间的edit。比如我现在输入2008-02-11到2008-03-15;
现在我向得到的是:2008-02-11以前的在库数量(on_hand 减去2008-02-11到今天的变化量),和2008-03-15那个时候的在库数量(on_hand 减去2008-02-15到今天的变化量)。还有在2008-02-11到2008-03-15时间段的增加量和减少量。
请用sql文帮我表达出来,只能用一个表达式。谢谢!!!!!这样对吗?
select history.part as part1,
history.on_hand,
sum(iff(history.qty >= 0,history.qty,0)) as qty1,
sum(iff(history.qty < 0,-history.qty,0)) as qty2,
(select sum(qty)
from history
where history.part = part_mstpart
and date between(2008/03/08,2008/03/25)) as total,
history.on_hand - total as on_hand1,
on_hand1 - 1 - 1 as last_on_hand
from history left join part_mst on history.part = part_mst.part
where historyb.date between(2008/01/03,2008/03/08)
group by a.part
history.name
history.on_hand
order by a.part
;
表part_mst的on_hand的字段是指现在(也就是今天的在库数量),表history的qty字段是指:进货多少或者出货多少,如果是进货就用正数的表示,如果是出货就用负数表示。表history的date字段是指是:进货或出货的日期。
画面是一个可以输入开始时间和结束时间的edit。比如我现在输入2008-02-11到2008-03-15;
现在我向得到的是:2008-02-11以前的在库数量(on_hand 减去2008-02-11到今天的变化量),和2008-03-15那个时候的在库数量(on_hand 减去2008-02-15到今天的变化量)。还有在2008-02-11到2008-03-15时间段的增加量和减少量。
请用sql文帮我表达出来,只能用一个表达式。谢谢!!!!!这样对吗?
select history.part as part1,
history.on_hand,
sum(iff(history.qty >= 0,history.qty,0)) as qty1,
sum(iff(history.qty < 0,-history.qty,0)) as qty2,
(select sum(qty)
from history
where history.part = part_mstpart
and date between(2008/03/08,2008/03/25)) as total,
history.on_hand - total as on_hand1,
on_hand1 - 1 - 1 as last_on_hand
from history left join part_mst on history.part = part_mst.part
where historyb.date between(2008/01/03,2008/03/08)
group by a.part
history.name
history.on_hand
order by a.part
;
from (
select part,sum(qty) as sqty1
from (
select part,-qty
from history
where date>'2008-02-11'
union all
select part,on_hand
from part_mst)
group by part
) a,
(
select part,sum(qty) as sqty2
from (
select part,-qty
from history
where date>'2008-03-15'
union all
select part,on_hand
from part_mst)
group by part
) b,
(
select part,sum(if(qty>0,qty,0)) as GR ,sum(if(qty<0,qty,0)) as GI
from history
where date between '2008-02-11' and '2008-03-15'
group by part
) c
where a.part = b.part and a.part=c.part
或参考:
http://www.csdn.net/help/over.asp
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
=============================================================================
问题解决,请及时结贴。
正确结贴方法:
管理帖子-->给分-->输入密码-->结贴
还有一事请求,能发给我一下关于sql的材料吗。你太厉害了。
数据库系统概论PPT
MySQL 5.1 Reference Manual refman-5.1-en.chm可以到www.mysql.com下载
========================================
select part,sum(qty) as sqty1
from (
select part,-qty
from history
where date>'2008-02-11'
union all
select part,on_hand
from part_mst)
group by part
==========================================
select part,sum(qty) as sqty2
from (
select part,-qty
from history
where date>'2008-03-15'
union all
select part,on_hand
from part_mst)
group by part
=======================================
select part,sum(if(qty>0,qty,0)) as GR ,sum(if(qty<0,qty,0)) as GI
from history
where date between '2008-02-11' and '2008-03-15'
group by part
========================================
select a.part,sum(a.valu) as sqty1
from (
select part,-qty as valu
from history
where date>'2008-02-11'
union all
select part,on_hand as valu
from part_mst) as a
group by a.part
==========================================
select b.part,sum(b.valu) as sqty2
from (
select part,-qty as valu
from history
where date>'2008-03-15'
union all
select part,on_hand valu
from part_mst) as b
group by b.part
=======================================
select part,sum(if(qty>0,qty,0)) as GR ,sum(if(qty <0,qty,0)) as GI
from history
where date between '2008-02-11' and '2008-03-15'
group by part
19055578 - CSDN-VBA&Access
=========================================================
可加入QQ群 19055578 晚19:00 - 24:00在线
=========================================================