我有两个表,一是采购订单表(order),另一个是采购订单明细表(orderdetails)
order表中的字段有 orderID,providerID,requireddate,summoney,分别表示采购订单ID,供应商,预交货日期,订单总额
orderdetails表的字段有orderID,productID,quantity,unitprice,rev_quantity,分别表示采购订单ID,产品ID,采购数量,产品单价,产品已收数量例如:order表 orderID providerID requireddate summoney
10001 1 2006-6-1 100
10002 2 2006-6-12 200orderdetails表 orderID productID quantity unitprice rev_quantity
10001 101 2 25 0
10001 102 1 50 1
10002 103 8 25 0现在我想根据上面的数据查询得到如下数据,如按照供应商查询,例如对供应商1,得到
采购单号 到货日期 采购总额 未到货金额
10001 2006-6-1 100 50请教要得到上面表中的数据sql语句该怎么写呢?谢谢!
order表中的字段有 orderID,providerID,requireddate,summoney,分别表示采购订单ID,供应商,预交货日期,订单总额
orderdetails表的字段有orderID,productID,quantity,unitprice,rev_quantity,分别表示采购订单ID,产品ID,采购数量,产品单价,产品已收数量例如:order表 orderID providerID requireddate summoney
10001 1 2006-6-1 100
10002 2 2006-6-12 200orderdetails表 orderID productID quantity unitprice rev_quantity
10001 101 2 25 0
10001 102 1 50 1
10002 103 8 25 0现在我想根据上面的数据查询得到如下数据,如按照供应商查询,例如对供应商1,得到
采购单号 到货日期 采购总额 未到货金额
10001 2006-6-1 100 50请教要得到上面表中的数据sql语句该怎么写呢?谢谢!
未到货金额=summoney-(select sum(quantity) from orderdetails where orderID=order.orderID and providerID=order.providerID)
from order
A.orderID As 采购单号,
A.requireddate As 到货日期,
A.summoney As 采购总额,
A.summoney-IsNull(B.summoney,0) As 未到货金额
From [order] A
Left Join (Select orderID,SUM(unitprice*rev_quantity) As summoney From orderdetails Group By orderID) B
On A.orderID=B.orderID
Where providerID=1
上例中的sum(quantity) ,需用sum(quantity*unitprice) 替换
请问未到货金额怎么算出来?
----------------------------------------------
订单总额減去已收產品總金額不就OK.
如:未到货总金额=
summoney-(select sum(quantity*unitprice) from orderdetails where orderID=order.orderID and providerID=order.providerID)
select orderID 采购单号,requiredDate 到货日期,summoney 采购总额,
未到货金额=summoney-(select sum(rev_quantity*unitprice) from orderdetails where orderID=[order].orderID and [order].providerID=1)
from [order]测试数据的结果如下:
10001 2006-06-01 00:00:00.000 100.0000 50.0000
10002 2006-06-12 00:00:00.000 200.0000 NULL
但是结果明显多出了一项,所以我改成如下:select orderID 采购单号,requiredDate 到货日期,summoney 采购总额,
未到货金额=summoney-(select sum(rev_quantity*unitprice) from orderdetails where orderID=[order].orderID)
from [order] where providerID=1结果正确
drop table #order
CREATE TABLE #order (orderid int, proveiderID int, requiredate datetime,summoney int)
测试数据
INSERT INTO #order select 10001,1,'2006-06-01',100
union all select 10002,2,'2006-06-12',200
drop table #orderdetailsCREATE TABLE #orderdetails (orderid int,productID int, quantity int,unitprice int, rev_quantity int)
insert into #orderdetails select 10001,101,2,25,0
union all select 10001,102,1,50,1
union all select 10002,103,8,25,0select orderid as 采购单号 ,convert(nvarchar(10),requiredate,120) 到货日期,summoney as 采购总额,
未到货金额=(select sum((quantity-rev_quantity)*unitprice) from #orderdetails where orderid=#order.orderid and #order.proveiderID=1 group by orderid)
from #order
where proveiderID=1查询结果:采购单号 到货日期 采购总额 未到货金额
10001 2006-06-01 100 50
总算做出来了,花了我不少时间哦
问题解决,收工结贴!