从查询返回多个值但不允许子查询。 [英] Return multiple values from query but subquery does not permitted.
问题描述
我找到了在查询中使用top 1的解决方案,但它只返回一个最高值。我知道变量存储只有一个值,但我找不到任何其他方法。
这是我的存储过程
请帮助。
< pre lang =SQL> 表 1:dbo.tblEmpDetail(名称 varchar ( 30 ),BasicPay int ,ScaleID int )
表 2:dbo.tblAllowance(名称 varchar ( 30 ),金额 int ,ScaleID int )
表 3:dbo。 tblDeduction(名称 varchar ( 30 ),金额 int ,ScaleID int )
<前郎=sql> alter proc 测试
as
开始
/ * 税收变量* /
/ * AllowanceDeduction cal vairable * /
声明 @ totaldeduc int ,
@ totalAllowance int ,
@ grosspay int
set @ totaldeduc =(选择 sum(金额)来自 tblAllowance A
inner join tblEmpOfficialDetail O on O.ScaleID = A.ScaleID
group by A.ScaleID,O.EmpID)
set @ totalAllowance =(选择 sum(金额)来自 tblDeduction A
inner join tblEmpOfficialDetail O on O.ScaleID = A.ScaleID
group by A.ScaleID,O.EmpID)
set @ grosspay =(< span class =code-keyword>选择 O.BasicPay 来自 tblEmpOfficialDetail O
inner join tblAllowance on A.ScaleID = O.ScaleID
inner join tblDeduction D on D.ScaleID = O.ScaleID
group by o.ScaleID,o.EmpID,O.BasicPay)+ @ totaldeduc- @ totalAllowance
选择 @ grosspay
结束
执行测试
我尝试过的事情:
我试图通过增加津贴[dbo.tblAllowances]并减去扣除额来计算所有员工的总薪酬[dbo.Deductions]针对员工的基本工资[dbo.EmpDetails]的特定职级。
使用临时表来实现你的目标其他一些方法也可用,但这很容易..
创建 表 #Temp
(
Sn INT IDENTITY ,
EmpId int ,
名称 Varchar ( 150 ),
totaldeduc decimal ( 10 , 2 ),
totalAllowance decimal ( 10 , 2 ),
basicpay decimal ( 10 , 2 ),
grosspay decimal ( 10 , 2 )
)
insert into #temp(EmpId)
选择 EmpId 来自 tblEmpDetail
更新 #temp set
totaldeduc =(选择 sum(金额)来自 tblAllowance 其中 EmpId =#temp.EmpId),
totalAllowance =( select sum(Amount) from tblDeduction 其中 EmpId =#temp.EmpId),
basicpay =(选择 BasicPay 来自 tblEmpOfficialDetail 其中 EmpId =#temp.EmpId)
更新# Temp set grosspay =(basicpay + totalAllowance-totaldeduc)
选择 * < span class =code-keyword>来自 #Temp
Drop table #Temp
我想你所有的桌子都有EmpId。
i found solution to use top 1 in the query but it only return one top value. I know variable store only one value but i cant find any other way to do.
Here is my Stored Procedure
Please Help.
Table 1: dbo.tblEmpDetail (Name varchar(30), BasicPay int, ScaleID int)
Table 2: dbo.tblAllowance (Name varchar(30), Amount int, ScaleID int)
Table 3: dbo.tblDeduction (Name varchar(30), Amount int, ScaleID int)
alter proc Test
as
begin
/*Tax cal variable*/
/*AllowanceDeduction cal vairable*/
declare @totaldeduc int,
@totalAllowance int,
@grosspay int
set @totaldeduc = ( select sum(Amount) from tblAllowance A
inner join tblEmpOfficialDetail O on O.ScaleID=A.ScaleID
group by A.ScaleID, O.EmpID)
set @totalAllowance= ( select sum(Amount) from tblDeduction A
inner join tblEmpOfficialDetail O on O.ScaleID=A.ScaleID
group by A.ScaleID, O.EmpID)
set @grosspay= (select O.BasicPay from tblEmpOfficialDetail O
inner join tblAllowance A on A.ScaleID=O.ScaleID
inner join tblDeduction D on D.ScaleID=O.ScaleID
group by o.ScaleID,o.EmpID, O.BasicPay )+@totaldeduc-@totalAllowance
select @grosspay
end
Execute Test
What I have tried:
I am trying to calculate the gross pay of all employees by adding the allowances [dbo.tblAllowances] and subtracting the deductions [dbo.Deductions] against specific rank of employee from basic pay [dbo.EmpDetails] of the employee.
Use Temporary tables to achieve your goal some other method also available but this is easy..
Create Table #Temp ( Sn INT IDENTITY, EmpId int, Name Varchar(150), totaldeduc decimal(10,2), totalAllowance decimal(10,2), basicpay decimal(10,2), grosspay decimal(10,2) ) insert into #temp(EmpId) select EmpId from tblEmpDetail Update #temp set totaldeduc=(select sum(Amount) from tblAllowance where EmpId=#temp.EmpId), totalAllowance=(select sum(Amount) from tblDeduction where EmpId=#temp.EmpId), basicpay=(select BasicPay from tblEmpOfficialDetail where EmpId=#temp.EmpId) Update #Temp set grosspay=(basicpay+totalAllowance-totaldeduc) Select * from #Temp Drop table #Temp
I suppose in all your table EmpId is present.
这篇关于从查询返回多个值但不允许子查询。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!