问题描述
我在尝试执行以下查询时收到此错误.有什么想法或建议吗?
I am receiving this error when trying to execute the query below. Any ideas or suggestions?
错误:
在包含外部引用的聚合表达式中指定了多个列.如果聚合的表达式包含外部引用,则该外部引用必须是表达式中唯一引用的列.
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
SELECT TestInstances.pkTestInstanceID AS 'pkTestInstanceID', bands.pkPerformanceLevelReportBandID AS 'BandID', bands.StackPosition AS 'StackPosition', (SELECT TOP 100 PERCENT SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END FROM PerformanceLevelReportBands b WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId ORDER BY SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END) AS 'Percent', COUNT(StudentScores_Subject.pkStudentScoreID) AS 'Count' FROM StudentScores_Subject INNER JOIN StudentTests ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID INNER JOIN TestInstances ON TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID INNER JOIN CAHSEE_TestPeriods ON CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID INNER JOIN PerformanceLevelReportBands bands ON bands.fkPerformanceLevelReportID = @intPerfLevelReportId LEFT JOIN MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID AND cutScores.fkGradeID = @intGradeId AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs) INNER JOIN PerfLevelReportBandComponents bandComponents ON bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID AND((bandComponents.ScoreValue = StudentScores_Subject.ScoreValue) OR ((CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN bandComponents.minScore and bandComponents.maxScore) OR (CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN cutScores.minScore and cutScores.maxScore))) RIGHT JOIN MM_SchoolYears ON MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID WHERE MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N',')) AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests) AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs) GROUP BY TestInstances.pkTestInstanceID, bands.pkPerformanceLevelReportBandID, bands.StackPosition ORDER BY TestInstances.pkTestInstanceID, bands.pkPerformanceLevelReportBandID, bands.StackPosition
推荐答案
这里的问题是你不能在聚合函数中组合外部和内部引用
The problem is here you can't combine an outer and inner reference in an aggregate function
(SELECT TOP 100 PERCENT SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END FROM PerformanceLevelReportBands b WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId ORDER BY SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END) AS 'Percent'
所以改成
(SELECT TOP 100 PERCENT SUM(CASE WHEN bb.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END FROM PerformanceLevelReportBands b JOIN PerformanceLevelReportBands bb ON bb.fkPerformanceLevelReportID =bands.fkPerformanceLevelReportID AND b.fkPerformanceLevelReportID =bb.fkPerformanceLevelReportID WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId ORDER BY SUM(CASE WHEN bb.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END) AS 'Percent'
这里有更详细的详细解释.