SQL 高级查询 - select 子句中单行聚合子查询的问题

Question

我正在尝试执行以下查询，但收到一个运行时错误，指出:

"The column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

下面的第二行是因上述错误而失败的行。我不明白为什么这个查询不执行，因为第二行不需要分组，因为输出只是计数(*)，关于我需要改变什么才能让它工作的任何线索？

SELECT @lessonPlans = COUNT(*)
, @lessonPlanResources = (SELECT COUNT(*) FROM dbo.LessonPlanResource lpr where lpr.LessonPlanId = a.LessonPlanId )
FROM
( 
    SELECT DISTINCT lpt.LessonPlanId
    FROM dbo.LearningTargetBreakout ltb
    JOIN dbo.LessonPlanLearningTarget lpt 
        on lpt.LearningTargetId = ltb.LearningTargetId
    WHERE (CASE 
            WHEN ltb.LearningTargetId = @learningTargetId and @learningTargetId is not null THEN 1
            WHEN ltb.CategoryId = @categoryId and @categoryId is not null THEN 1
            WHEN ltb.Grade = @grade and @grade is not null THEN 1
            WHEN ltb.SubjectId = @subjectId and @subjectId is not null THEN 1
            ELSE 0 END) = 1
) a

[编辑]

使用 Zeb 解决方案的轻微变化 - 这是我最终使用的修改后的代码，它产生一行带有聚合，这就是我所追求的。

SELECT @lessonPlans = ISNULL(COUNT(*), 0)
        , @lessonPlanResources = ISNULL(SUM(a.ResCount), 0)
FROM
( 
    SELECT DISTINCT lpt.LessonPlanId, lpr.ResCount
    FROM dbo.LearningTargetBreakout ltb
    JOIN dbo.LessonPlanLearningTarget lpt 
        on lpt.LearningTargetId = ltb.LearningTargetId
    JOIN (SELECT LessonPlanId, COUNT(*) ResCount FROM dbo.LessonPlanResource lpr GROUP BY LessonPlanId) lpr
        ON lpr.LessonPlanId = lpt.LessonPlanId          
    WHERE (CASE 
            WHEN ltb.LearningTargetId = @learningTargetId and @learningTargetId is not null THEN 1
            WHEN ltb.CategoryId = @categoryId and @categoryId is not null THEN 1
            WHEN ltb.GradeId = @grade and @grade is not null THEN 1
            WHEN ltb.SubjectId = @subjectId and @subjectId is not null THEN 1
            ELSE 0 END) = 1
) a

Answer 1

我的猜测是 @lessonPlanResources 链接到 LessonPlanId，它不是聚合的。

我的解决方案是加入该子表，并将返回的列设为 COUNT。

SELECT @lessonPlans = COUNT(*)
, @lessonPlanResources = SUM(zlpr.reses)
FROM
( 
    SELECT DISTINCT lpt.LessonPlanId, zlpr.reses
    FROM dbo.LearningTargetBreakout ltb
    JOIN dbo.LessonPlanLearningTarget lpt 
        on lpt.LearningTargetId = ltb.LearningTargetId
    JOIN (SELECT LessonPlanId, COUNT(*) reses FROM dbo.LessonPlanResource lpr) zlpr
        ON zlpr.LessonPlanId = lpt.LessonPlanId
    WHERE (CASE 
            WHEN ltb.LearningTargetId = @learningTargetId and @learningTargetId is not null THEN 1
            WHEN ltb.CategoryId = @categoryId and @categoryId is not null THEN 1
            WHEN ltb.Grade = @grade and @grade is not null THEN 1
            WHEN ltb.SubjectId = @subjectId and @subjectId is not null THEN 1
            ELSE 0 END) = 1
) a