在插入查询中使用选择查询,求和查询等的最佳做法是什么? [英] what is the best practice for having a select query, sum query, etc in an insert query?
本文介绍了在插入查询中使用选择查询,求和查询等的最佳做法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
插入 进入收入(
年,
Newsales,
Beginninginventory,
购买,
Goodforsales,
Endinventory,
Costofsales,
GrossIncome,
Salesandwages,
sss,
租金,
邮资,
Lightandwater,
运费,
燃料,
办公室供应,
税收和许可,
代表,
折旧,
运输,
维修,
其他,
Totaloperatingexpense,
规定,
Netincomefinal
)
VALUES (
@ one ,
(选择 SUM(TotalPrice) FROM inventoryout 其中 revenue.Newsales=@two) ,
@ three ,
@ four ,
@ five ,
@ six ,
@ seven ,
(选择 NewSales - Costofsales 来自收入其中 GrossIncome = @ 8),
@ nine ,
@ ten ,
@ eleven ,
@ 12 ,
@ thirteen ,
@fourteen ,
@ fifteen ,
@ sixteen ,
@ seventeen ,
@十八,
@ nineteen ,
@twenty ,
@ twentyone ,
@ twentytwo ,
@ twentythree ,
(选择 GrossIncome - Totaloperatingexpense 来自收入其中 Netincomebeforetaxes = @ twentyfour ),
< span class =code-sdkkeyword> @ twentyfive ,
(选择 Netincomebeforetaxes - 条款来自收入其中 Netincomefinal = @ twentysix )
)
解决方案
也许您可以先尝试计算子查询,然后将它们存储在其他变量中,然后将其包含在主查询中。
类似于:
DECLARE @ computedTwo money = SELECT SUM(TotalPrice) FROM inventoryout WHERE revenue.Newsales=@two
- 然后
INSERT INTO 收入
- ...
VALUES (
- ...
@ computedTwo
- ...
)
你是否抓住了想法?
我使用商店程序一次处理它们
创建程序testtable
(
@ colum1 int,
@ colum2 nvarchar(50),
@ colum3 nvarchar(50),
@ colum4 nvarchar(50),
@flag int,
)
as
BEGIN
if(@ flag = 0)
BEGIN
- select query
END
if(@ flag = 1)
BEGIN
- 插入查询
END
if(@ flag = 2)
BEGIN
- 更新查询
END
if(@ flag = 3)
BEGIN
- 删除查询
END
END
insert into revenue (
Year,
Newsales,
Beginninginventory,
Purchases,
Goodforsales,
Endinventory,
Costofsales,
GrossIncome,
Salesandwages,
sss,
Rental,
Postage,
Lightandwater,
Freight,
Fuel,
Officesupplies,
Taxesandlicenses,
Representation,
Depreciation,
Transportation,
Repairs,
Miscellaneous,
Totaloperatingexpense,
Provisions,
Netincomefinal
)
VALUES (
@one,
(Select SUM(TotalPrice) FROM inventoryout where revenue.Newsales=@two),
@three,
@four,
@five,
@six,
@seven,
(select NewSales - Costofsales from revenue where GrossIncome=@eight),
@nine,
@ten,
@eleven,
@twelve,
@thirteen,
@fourteen,
@fifteen,
@sixteen,
@seventeen,
@eighteen,
@nineteen,
@twenty,
@twentyone,
@twentytwo,
@twentythree,
(select GrossIncome - Totaloperatingexpense from revenue where Netincomebeforetaxes = @twentyfour),
@twentyfive,
(Select Netincomebeforetaxes - Provisions from revenue where Netincomefinal = @twentysix)
)
解决方案
Maybe you can try to compute the subqueries first, and store them in other variables, that you can then include in your main query.
Something like:
DECLARE @computedTwo money = SELECT SUM(TotalPrice) FROM inventoryout WHERE revenue.Newsales=@two -- Then INSERT INTO revenues -- ... VALUES ( -- ... @computedTwo -- ... )
Do you catch the idea?
i use store procedure to handle them at once
create procedure testtable ( @colum1 int, @colum2 nvarchar(50), @colum3 nvarchar(50), @colum4 nvarchar(50), @flag int, ) as BEGIN if (@flag=0) BEGIN -- select query END if (@flag=1) BEGIN -- insert query END if (@flag=2) BEGIN -- update query END if (@flag=3) BEGIN -- delete query END END
这篇关于在插入查询中使用选择查询,求和查询等的最佳做法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文