我有一条SQL语句来计算我在不同项目上拥有的库存量。因此,我总结了所有已交付的物品并减去了已售出的所有物品。给我正确结果的SQL语句如下:
SELECT Item.id,
Item.description,
deliver.del_amount,
sell.sell_amount ,
deliver.del_amount - sell.sell_amount as stock
from item
join (
select Deliveryitem.Item_id as del_id,
sum(DeliveryItem.amount) as del_amount
from Deliveryitem group by DeliveryItem.item_id
) deliver on deliver.del_id = item.id
join (
SELECT LineItem.Item_id as sell_id,
sum(LineItem.amount) as sell_amount
from LineItem group by LineItem.item_id
) sell on sell.sell_id=item.id
现在,我需要将其转换为等效的SQLAlchemy语句。我不知道如何结合交付和销售标签正确地表达两个联接。有人可以帮我吗?
SQLAlchemy的优点在于,当您开始使用SQLAlchemy表达式语法难以表达的复杂查询时,可以使用直接SQL并将结果填充到ORM类中。
您可以按照以下方式做一些事情
Session.query(Item, deliver, sell, stock).from_statement("""
SELECT Item.id,
Item.description,
deliver.del_amount,
sell.sell_amount ,
deliver.del_amount - sell.sell_amount as stock
from item
join (
select Deliveryitem.Item_id as del_id,
sum(DeliveryItem.amount) as del_amount
from Deliveryitem group by DeliveryItem.item_id
) deliver on deliver.del_id = item.id
join (
SELECT LineItem.Item_id as sell_id,
sum(LineItem.amount) as sell_amount
from LineItem group by LineItem.item_id
) sell on sell.sell_id=item.id
""")
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句