现在有两个表,一个表如下:
SalesOrderID UnitPrice OrderQty
1001 1 2
1001 3 1
1002 5 1分别是ID,单价,数量
我要求订单总额Total = Sum (UnitPrice*OrderQty)...也就是每个ID的UnitPrice*OrderQty之和,存到另一个表Result里面
结果希望是
表Result
SalesOrderID Total
1001 5
1002 5
请问该怎么写呢?
我只写到Update Result就不会了
SalesOrderID UnitPrice OrderQty
1001 1 2
1001 3 1
1002 5 1分别是ID,单价,数量
我要求订单总额Total = Sum (UnitPrice*OrderQty)...也就是每个ID的UnitPrice*OrderQty之和,存到另一个表Result里面
结果希望是
表Result
SalesOrderID Total
1001 5
1002 5
请问该怎么写呢?
我只写到Update Result就不会了
select SalesOrderID, sum(UnitPrice*OrderQty) Total
from tb
group by SalesOrderID
select saleseorderid,sum(UnitPrice*OrderQty) from OrderQty
group by saleseorderid
CREATE TABLE tb(SalesOrderID INT,UnitPrice INT ,OrderQty INT)INSERT INTO tb
SELECT 1001, 1 ,2
UNION ALL
SELECT 1001, 3, 1
UNION ALL
SELECT 1002, 5 ,1select SalesOrderID, sum(UnitPrice*OrderQty) Total from tb group by SalesOrderIDSalesOrderID Total
------------ -----------
1001 5
1002 5(2 row(s) affected)
SELECT * INTO result FROM(
select SalesOrderID,
sum(UnitPrice*OrderQty) Total from tb group by SalesOrderID)p
我的Result表主键是SalesOrderID,但是还有好多别的键
insert into result
SELECT * FROM(
select SalesOrderID,
sum(UnitPrice*OrderQty) Total from tb group by SalesOrderID)p
SELECT * FROM(
select SalesOrderID,
sum(UnitPrice*OrderQty) Total from tb group by SalesOrderID)p
WHERE p.SalesOrderID=SalesOrderID
--先插入表result,只有一个SalesOrderID
insert into Result(salesOrderID) select distinct SalesOrderID from tb
--再update
update result set totalSales=total from result inner join ( select SalesOrderID,
sum(UnitPrice*OrderQty) Total from tb group by SalesOrderID)p
WHERE p.SalesOrderID=SalesOrderID where totalSales is null or totalsales=0
--这里假设totalsales就是汇总值
搞定了,不过用了一种很笨的方法SELECT * INTO Tem FROM(
select SalesOrderID,
sum(UnitPrice*OrderQty) Total from SalesOrderDetail group by SalesOrderID)pupdate result
Set Total = Tem.Total From Tem
WHERE Tem.SalesOrderID = Result.SalesOrderIDDrop Table Tem谢谢大家