我的SQL查询从联接 [英] my sql query from join

问题描述

select it.stock,(it.stock-i.quantity) as remainingstock from items it
left outer join invoiceitems i on i.ItemID=it.ID

我需要这样的查询:从发票项目中为每种产品选择数量，然后从项目表中的库存中减去它，并在结果中显示为库存和剩余库存

I need such query that select quantity for each product from invoiceitem and subtract it from stock which is in item table and display in result as stock and remaining stock

推荐答案

select sum (it.Stock)，总和(it.stock-i.quantity)为"t库存"，来自i.ItemID = it.ID
上的项目t左外联接发票i 按它分组.股票

希望这会有所帮助.

select sum(it.Stock),sum(it.stock-i.quantity) as ''Stock in Hand'' from items t left outer join invoiceitems i on i.ItemID=it.ID
group by it.Stock

Hope this helps.

对RoliKings解决方案进行一些修改:

从它在外部加入发票i的项目中选择sum(it.stock)，isnull(sum(it.stock-i.quantity)，sum(it.stock))作为``现有库存''.itemId = i .ItemId分组.stock

Little bit modification to RoliKings solution:

select sum(it.stock),isnull(sum(it.stock-i.quantity),sum(it.stock)) as ''Stock in Hand'' from items it left outer join invoiceitems i on it.itemId=i.ItemId group by it.stock

这篇关于我的SQL查询从联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！