还是我那个订单问题
有产品表,产品表里有单价
下单以后在订单表里获取产品的数量然后用产品表里的单价乘以订单表里的数量,最后在发票表里得到总额实现这个之前除了主外键做好关联,还需要注意什么?我现在的代码是:CREATE TRIGGER 计算总价ON 发票FOR UPDATE, INSERTASUPDATE 发票 SET 总价 = 产品.单价 * 订单.数量请问如何补充?
有产品表,产品表里有单价
下单以后在订单表里获取产品的数量然后用产品表里的单价乘以订单表里的数量,最后在发票表里得到总额实现这个之前除了主外键做好关联,还需要注意什么?我现在的代码是:CREATE TRIGGER 计算总价ON 发票FOR UPDATE, INSERTASUPDATE 发票 SET 总价 = 产品.单价 * 订单.数量请问如何补充?
from 发票 inner join 产品 on 两表主键关联
ON 发票
FOR UPDATE, INSERT
AS
begin
declare @bill varchar(50)
select @bill=订单号 from inserted
update 发票 set 总价=(select sum(数量*单价) from 订单表 as x inner join 产品表 as y on x.产品编号=y.产品编号 where 订单号=@bill)
where 订单号=@bill
end--大概就是上面的格式了
CREATE VIEW vw_ProductInvoice
AS
SELECT
I.发票_Id,
I.订单_Id,
P.产品_单价 * O.数量 AS 总价
FROM 数据库_db.dbo.发票 AS I, 数据库.dbo.产品 AS P, 数据库_db.dbo.订单 AS O
inner join 订单 AS Orders ON O.订单_Id = O.订单_Id
inner join 产品 AS Item ON p.产品_Id = O.产品_Id得到的错误提示是:Msg 4104, Level 16, State 1, Procedure vw_ProductInvoice, Line 9
The multi-part identifier "p.产品_Id" could not be bound.我估计应该是别名的用法不对,楼上二位大侠能不能帮我检查下?我本来出现了七八个这样的错误,我自己改了下代码,还剩下这一个了,不知道怎么改了
AS
SELECT I.发票_Id ,
I.订单_Id ,
P.产品_单价 * O.数量 AS 总价
FROM 数据库_db.dbo.发票 AS I
INNER JOIN 数据库_db.dbo.订单 AS O ON I.订单_Id = O.订单_Id
INNER JOIN 数据库.dbo.产品 AS p ON p.产品_Id = O.产品_Id
AS
SELECT I.发票_Id, I.订单_Id, sum(P.产品_单价 * O.数量) AS 总价
FROM 数据库_db.dbo.发票 AS I,
inner join 数据库_db.dbo.订单 AS O AS Orders ON I.订单_Id = O.订单_Id
inner join 数据库.dbo.产品 AS P ON p.产品_Id = O.产品_Id
group by I.发票_Id, I.订单_Id
AS
SELECT I.发票_Id ,
I.订单_Id ,
SUM(P.产品_单价 * O.数量) AS 总价
FROM 数据库_db.dbo.发票 AS I
INNER JOIN 数据库_db.dbo.订单 AS O ON I.订单_Id = O.订单_Id
INNER JOIN 数据库.dbo.产品 AS p ON p.产品_Id = O.产品_Id
GROUP BY I.发票_Id ,
I.订单_Id
CREATE VIEW vw_Pice
AS
SELECT a.发票Id ,a.订单Id ,c.产品单价 * b.数量 AS 总价 FROM 发票表 AS a,订单表 AS b,产品表 as c where a.订单Id = c.订单Id and c.产品Id = b.产品Id--查看视图表
select * from vw_Pice