Question

Suppose I have a table: ACCOUNT
It has columns: ID, NAME, BALANCE

我想确定,在2 000多美元以上的人的资产负债表中,是否超过5万美元。这意味着我要选择一个合乎逻辑的价值。由于我不想获得具体数额,我只需要比较结果。我想写的是:

SELECT SUM(BALANCE) > 50000 
  FROM ACCOUNT
 WHERE BALANCE > 2000

Sql服务器说,这是 s子错误。因此,我必须:

SELECT SUM(BALANCE)  
  FROM ACCOUNT
 WHERE BALANCE > 2000

然后,结果为500。 www.un.org/Depts/DGACM/index_spanish.htm 但是,在达到5 500万美元之后,所有项目的合计数是多余的,因为我仅将其与5 000万美元相比较。

因此,我如何能够在达到比较价值之后以某种方式来写这一问话,并给我带来合乎逻辑的结果?

Answer 1

This question was asked in 2011. In versions prior to 2012 there isn t really a clean way of doing this and getting SQL Server to short circuit the scan as soon as the total is reached.

你们可以采用一种补偿办法,来补充结余。一旦余额总额超过50 000美元,就会出现这一状况。视表率和指数而定,这种指数或多或少。

You could also have a custom CLR aggregate that terminated processing by throwing an error if 50,000 was reached but quite a horrible solution!

自2012年以来的版本见my。页: 1

Answer 2

www.un.org/Depts/DGACM/index_spanish.htm 页: 1

How does SQL Server know you have only positive values? If you have 10 values, and the 8th one gets you over 50,000, but 9 and 10 are both -10,000, wouldn t that be an issue?

Answer 3

如果你与一位治疗者制定程序,在达到5 00000年之后,你就可以停止盘问(登机知道它是否值得,但却是唯一的方法,也许有数百万人:

DECLARE @bal int
DECLARE @bal_tot int
DECLARE cur CURSOR FOR 
SELECT sum(balance)
FROM account
WHERE balance > 2000
ORDER BY balance DESC;

OPEN cur;

FETCH NEXT FROM cur
INTO @bal
WHILE @@FETCH_STATUS = 0 AND @bal_tot < 50000
BEGIN
@bal_tot = @bal_tot + @bal
END
CLOSE cur;

if @bal_tot > 50000 etc...

Answer 4

参看<代码>HAVING。

SELECT sum(balance)
FROM account
WHERE balance > 2000
HAVING sum(balance) > 50000

Edit: http://strong>,如,与所询问的原始问题一样。如果库克服务器盘点发动机在什么地方/运行条款是捷径(如马丁·史密斯在评论中所说的那样),则。我认为,撰写你自己的短 short将过于复杂,如果你重击业绩问题,可能采取更好的办法。

如果你需要通过询问作出真实/真实的答复,那么Jim Dagg的回答对我来说是好的。

Answer 5

I wouldn t go the route of trying to write a complicated procedure to short-circuit the query. -- that s overengineering the problem. Honestly, just pulling the sum and doing the comparison the way you re doing now is the most straightforward method. If you want to select the result of the comparison for use in other SQL processing, you could try this:

SELECT CASE WHEN 
    (SELECT SUM(AmtCurrent)
     FROM [SisMaster].[dbo].[Loan]
     WHERE AmtCurrent > 2000) > 50000 
THEN 1 
ELSE 0 
END

Answer 6

SELECT CASE WHEN  TotalAmtCurrent > 50000  THEN 1  ELSE 0  END 
FROM
    (SELECT SUM(AmtCurrent)  as TotalAmtCurrent    
    FROM [SisMaster].[dbo].[Loan]     
    WHERE AmtCurrent > 2000) a

友情链接