大家好,我有以下查询:
SELECT TOP(1) CommitmentLog.CommitAmt
FROM leveldetails
INNER JOIN CommitmentLog
ON leveldetails.App_ID = CommitmentLog.app_id
AND leveldetails.Intro_ID = '999'
AND leveldetails.Side = 'right'
AND CommitmentLog.flag != '1'
AND CommitmentLog.CommitNo NOT IN (SELECT commit_no
FROM binary_bal
WHERE Intro_ID = '999'
AND Side = 'right'
AND llevel BETWEEN 1 AND 100000)
第一行CommitAmt值:1500
第二行CommitAmt值:500
如果我选择CommitmentLog.CommitAmt的前1个总和,则返回值为2000而不是1500
如果我选择CommitmentLog.CommitAmt的前2个总和,则返回正确的返回值2000
如果我选择前1,则求和(CommitmentLog.CommitAmt)
它应该返回1500,
如果我选择前2,则
sum(CommitmentLog.CommitAmt)它应该返回1500 + 500 = 2000
最佳答案
您想对结果集的前N个求和?尝试这个
SELECT SUM(t1.CommitAmt) from (SELECT TOP(1) CommitmentLog.CommitAmt
FROM leveldetails
INNER JOIN CommitmentLog
ON leveldetails.App_ID = CommitmentLog.app_id
AND leveldetails.Intro_ID = '999'
AND leveldetails.Side = 'right'
AND CommitmentLog.flag != '1'
AND CommitmentLog.CommitNo NOT IN (SELECT commit_no
FROM binary_bal
WHERE Intro_ID = '999'
AND Side = 'right'
AND llevel BETWEEN 1 AND 100000)
) AS t1