错误：``在包含外部引用的聚合表达式中指定了多个列。''

Question

尝试执行以下查询时，我收到此错误。有什么想法或建议吗？

错误：

在包含外部引用的聚合表达式中指定了多个列。如果要聚合的表达式包含外部引用，则该外部引用必须是表达式中唯一引用的列。

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

Answer 1

问题是您不能在聚合函数中组合外部和内部引用

(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'

这是更详尽的解释。