无法弄清楚我在做什么错。它说字段HasNoteDate无效。我只希望它在有日期的情况下返回1,在没有日期的情况下返回0,并且我希望将其分组以便可以得到计数和贷款金额的总和。我看过按案例分组的stackoverflow答案,但没有一个能为我解决。谢谢!
SELECT dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, COUNT(*) AS TierRegionCount,
SUM(dbo.LOAN.LOAN_AMT) AS LoanAmtSum, dbo.LOAN.DEAL_STATUS, CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL
THEN 0 ELSE 1 END AS HasNoteDate
FROM dbo.BRKRMAST INNER JOIN
dbo.BRKRREF ON dbo.BRKRMAST.BRKRMASTID = dbo.BRKRREF.BRKRMASTID INNER JOIN
dbo.LOAN ON dbo.BRKRREF.LoanId = dbo.LOAN.LoanId INNER JOIN
dbo.WORKFLOW ON dbo.LOAN.LoanId = dbo.WORKFLOW.LoanId LEFT OUTER JOIN
dbo.BDONAMES ON dbo.BRKRMAST.BDONAMESID = dbo.BDONAMES.BDONAMESID
WHERE (LEN(dbo.BDONAMES.UserID) > 0) AND (dbo.BRKRMAST.Tier > 0) AND (LEN(dbo.BDONAMES.REGION) > 0)
GROUP BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, dbo.LOAN.LOAN_AMT, dbo.LOAN.DEAL_STATUS, HasNoteDate
ORDER BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION
SQL查询执行的逻辑不像普通的编程语言那么简单
这是查询的示例执行阶段:
FROM CLAUSE
GROUP BY
-这将导致错误,HasNoteDate
目前尚未定义ORDER BY
SELECT
-仅在此阶段HasNoteDate
定义列因此,您不能HasNoteDate
在GROUP BY中使用未定义的列,必须将其替换为您的statement (CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL THEN 0 ELSE 1 END)
,然后sql server将能够GROUP
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句