假设有这样两个表~~
Item表:
ItemNo VARCHAR2(10)
ItemName VARCHAR2(30)
UnitPrice NUMBER(6, 2)
Description VARCHAR2(50)Order表:
OrderNo VARCHAR2(10)
ItemNo VARCHAR2(10)
Amount NUMBER(5)在Order表中有这样几条记录,OrderNo“XX1234”中包含7件编号为ZN414的物品,5件编号为MC6809的物品和16件编号为HM20C的物品,在表中表达为:(“XX1234”, ZN414”, 7), (“XX1234”, “MC6809”, 5) 和 (“XX1234”, “HM20C”, 16).如何写出查询Order总价(即Amount和UnitPrice的乘积)比OrderNo为“XX1234”要大的所有记录
Item表:
ItemNo VARCHAR2(10)
ItemName VARCHAR2(30)
UnitPrice NUMBER(6, 2)
Description VARCHAR2(50)Order表:
OrderNo VARCHAR2(10)
ItemNo VARCHAR2(10)
Amount NUMBER(5)在Order表中有这样几条记录,OrderNo“XX1234”中包含7件编号为ZN414的物品,5件编号为MC6809的物品和16件编号为HM20C的物品,在表中表达为:(“XX1234”, ZN414”, 7), (“XX1234”, “MC6809”, 5) 和 (“XX1234”, “HM20C”, 16).如何写出查询Order总价(即Amount和UnitPrice的乘积)比OrderNo为“XX1234”要大的所有记录
(
select a.OrderNo from [Order] a join Item b on a.ItemNo=b.ItemNo group by a.OrderNo
having sum(a.Amount*b.UnitPrice)>
(select sum(a.Amount*b.UnitPrice) from [Order] a join Item b on a.ItemNo=b.ItemNo where a.OrderNo='XX1234')
) b
on a.OrderNo=b.OrderNo
O.*,I.UnitPrice*O.Amount as Total
into #temp
from [Order] O
join Item I on O.ItemNO=I.ItemNOselect * from #temp where Total>(select Total from #temp where OrderNo='XX1234')
(
select m.orderno , sum(m.amount*n.UnitPrice) totalprice from [order] m , item n where m.itemno = n.itemno group by m.orderno
) t
where totalprice > (select sum(m.amount*n.UnitPrice) totalprice from [order] m , item n where m.itemno = n.itemno and m.orderno = 'XX1234')