存储过程中的别名出现问题 [英] Problem with an alias in a stored procedure

问题描述

大家好！
我有一个存储过程，该过程使用两个别名检索两次总和，并创建一个变量来携带两者的差值.

有一个错误，请问有人可以告诉我该怎么做吗?

Hi all!
I have a stored procedure that retrieves the sum twice with two aliases and I create a variable to carry the value of the difference of both of them.

There is an error, can any one tell me how can I make it,please???

<br />
<br />
<pre lang="sql"><br />
ALTER PROC select_test<br />
(<br />
    @Id bigint<br />
)<br />
AS<br />
DECLARE @x AS bigint<br />
SELECT test6.Id ,test6.summation as  TotalLoan   ,(select test6.summation from test6 where Name=''b'' and Id =@Id ) as TotalBorrower, @x as ''Total''<br />
FROM test6  WHERE Name=''a'' and Id =@Id<br />
GROUP BY test6.AccId,test6.summation<br />
SET @x= TotalLoan - TotalBorrower<br />
PRINT @x</pre><br />

Thanks in advance!

推荐答案

首先，参数列表中没有括号().第二个问题是，在SELECT语句之后计算差值不会将总计"列设置为该值.像这样尝试:

First of all there are no parenthesis () around the parameter list. Second problem is that calculating the difference after the SELECT statement will not set column "Total" to that value. Try it in the likes of this:

ALTER PROC select_test
    @Id bigint
AS
DECLARE @difference AS bigint, @totalLoan AS bigint, @totalBorrower AS bigint;
SET @totalLoan = (SELECT test6.summation FROM test6 WHERE Id = @Id AND Name='a');
SET @totalBorrower = (SELECT test6.summation FROM test6 WHERE Id = @Id AND Name='b');
SET @difference = @totalLoan - @totalBorrower;

SELECT @Id AS Id, @totalLoan AS TotalLoan, @totalBorrower AS TotalBorrower, @difference AS Total
--PRINT @x

请告诉我您是否想要的.

干杯！

Please tell me if that is what you wanted.

Cheers!

这篇关于存储过程中的别名出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！