求mysql语句:
menu表
menuid wpname
1 水煮鱼
2 清蒸鱼材料表:
menuid wpneedname neednum
1 鱼 1
1 油 5
1 盐 4背包表:
bagid wpname wpnum
1 鱼 1
2 鱼 4
3 油 1
4 油 5
5 盐 10要求:背包中的材料消耗顺序按照bagid增长的顺序消耗。
如:烹饪水煮鱼后,背包中的数据应该变为:
bagid wname wpnum
2 鱼 4
4 油 1
5 盐 6
menu表
menuid wpname
1 水煮鱼
2 清蒸鱼材料表:
menuid wpneedname neednum
1 鱼 1
1 油 5
1 盐 4背包表:
bagid wpname wpnum
1 鱼 1
2 鱼 4
3 油 1
4 油 5
5 盐 10要求:背包中的材料消耗顺序按照bagid增长的顺序消耗。
如:烹饪水煮鱼后,背包中的数据应该变为:
bagid wname wpnum
2 鱼 4
4 油 1
5 盐 6
select bagid,wname,(sum(wpnum)-(select wpnum from tableneed as b where b.wpneedname=a.wname)) from tablebag as a group by a.wname order by a.bagid asc;
select a.bagid,a.wname,sum(a.wpnum)-b.neednum from tablebag as a left join tableneed as b on(a.wname=b.wpneedname) group by a.wname order by a.bagid asc;
第一个 括号里面的 select 字段写错了二个都没有测试 LZ 自己测试下看看 行不行
from bag b,stuff s,
(select b2.bagid,sum(b1.wpnum) ws
from bag b1,bag b2
where b1.wpname=b2.wpname and b1.bagid<=b2.bagid
group by b2.bagid) t
where b.bagid=t.bagid and b.wpname=s.wpneedname
and t.ws-s.neednum>0;
from bag b,stuff s,menu m,
(select b2.bagid,sum(b1.wpnum) ws
from bag b1,bag b2
where b1.wpname=b2.wpname and b1.bagid<=b2.bagid
group by b2.bagid) t
where m.menuid=s.menuid and
b.bagid=t.bagid and b.wpname=s.wpneedname
and t.ws-s.neednum>0 and m.wpname='水煮鱼';