我在尝试执行下面的查询时收到此错误。有什么想法或建议吗？
Error:
在包含外部引用的聚合表达式中指定多个列。如果正在聚合的表达式包含外部引用，则该外部引用必须是表达式中引用的唯一列。
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
问题是这里你不能在聚合函数中组合外部和内部引用
(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'
这里还有一个更多详尽的解释 https://www.itprotoday.com/aggregates-outer-reference.
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
