Обсуждение ранее: i">Как указать вычисляемые столбцы в таблице, основанные на другом столбце в SQL Server?
create table orderitems
(
orderId int,
itemid int,
productId int,
itemprice decimal(18,2),
quantity int
)
GO
create table orders
(
orderId int,
shipAmount decimal(18,2),
TaxAmount decimal(18,2)
)
GO
insert into orderitems (orderId,itemid,productId,itemprice,quantity) values
(1,1,2,1199,1),
(2,2,8,489.99,1),
(3,3,1,2517,1),
(3,4,9,415,1),
(4,5,2,1199,1),
(5,6,10,299,1),
(6,7,10,299,1)
GO
insert into orders (orderId,shipAmount,TaxAmount) values
(1,5,58.71),
(2,5,58.72),
(3,10,58.73),
(4,10,58.74),
(5,5,58.75),
(6,5,58.76);
GO
select * from orders
GO
orderId | shipAmount | TaxAmount
------: | :--------- | :--------
1 | 5.00 | 58.71
2 | 5.00 | 58.72
3 | 10.00 | 58.73
4 | 10.00 | 58.74
5 | 5.00 | 58.75
6 | 5.00 | 58.76
create function udfMyTable (@orderID as int)
returns decimal(18, 2)
as
begin
declare @res as decimal(19, 2);
Select
@res = SUM(itemprice*Quantity)
from orderitems
where
orderID = @orderID
return @res;
end
GO
alter table orderitems add SubTotal as dbo.udfMyTable(orderID)
GO
alter table orders add SubTotal as dbo.udfMyTable(orderID)
GO
Select * from orderitems
GO
orderId | itemid | productId | itemprice | quantity | SubTotal
------: | -----: | --------: | :-------- | -------: | :-------
1 | 1 | 2 | 1199.00 | 1 | 1199.00
2 | 2 | 8 | 489.99 | 1 | 489.99
3 | 3 | 1 | 2517.00 | 1 | 2932.00
3 | 4 | 9 | 415.00 | 1 | 2932.00
4 | 5 | 2 | 1199.00 | 1 | 1199.00
5 | 6 | 10 | 299.00 | 1 | 299.00
6 | 7 | 10 | 299.00 | 1 | 299.00
select * from orders
GO
orderId | shipAmount | TaxAmount | SubTotal
------: | :--------- | :-------- | :-------
1 | 5.00 | 58.71 | 1199.00
2 | 5.00 | 58.72 | 489.99
3 | 10.00 | 58.73 | 2932.00
4 | 10.00 | 58.74 | 1199.00
5 | 5.00 | 58.75 | 299.00
6 | 5.00 | 58.76 | 299.00
db‹>скрипка здесь
person
2SRTVF
schedule
26.07.2018