代码之家 › 专栏 › 技术社区 › James

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

sql-server-2008 sql

James · 技术社区 · 14 年前

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

下面的第二行是由于上面的错误而失败的行。我不明白为什么这个查询不执行,因为在第二行中不需要分组,因为输出只是count(*),有什么线索说明我需要更改什么才能让它工作吗?

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

5 回复 | 直到 14 年前

zebediah49 14 年前

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

我的解决方案是连接到该子表,并将返回的列作为该子表的一个计数。

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

bobs 14 年前

你的 count(*) 是聚合函数,@lessonPlanResources的表达式不是(即使它是一个带有 COUNT(*) ). 因此,表达式必须包含在GROUPBY子句中。

您可以使用公共表表达式(CTE),这也有助于提高可读性。

WITH LPR_CTE as
    (
    SELECT LessonPlanId, COUNT(*) as LessonPlanResourcesCount
    FROM dbo.LessonPlanResource
    GROUP BY LessonPlanId
    ),

    LP_CTE(
    SELECT lpt.LessonPlanId, COUNT(*) as LessonPlansCount
    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
    )

SELECT @lessonPlans = LP_CTE.LessonPlansCount
     , @lessonPlanResources = LPR_CTE.LessonPlanResourcesCount
FROM LP_CTE
JOIN LPR_CTE on LP_CTE.LessonPlanId = LPR_CTE.LessonPlanId

Martin Smith 14 年前

你需要 GROUP BY a.LessonPlanId COUNT(*) 到 COUNT(*) OVER() 取决于你到底想做什么。

但这可能会给出多行结果,然后尝试将这些结果分配给一组标量变量。什么

kniemczak 14 年前

但是,对a.LessonPlanId的引用是一个非分组字段。将查询分组,就可以引用该字段。

试试这个:

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
GROUP BY a.LessonPlanID

dave 14 年前

在内部选择中使用COUNT DISTINCT

SELECT 
   @lessonPlans = a.B,
   @lessonPlanResources = (SELECT COUNT(*) FROM dbo.LessonPlanResource lpr where lpr.LessonPlanId = a.LessonPlanId )
FROM
( 
    SELECT COUNT (DISTINCT, lpt.LessonPlanId) AS B
    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