在选择列表中无效，因为它不是在聚合函数或GROUP BY子句

问题描述：

载我收到与下面的代码以下错误：在选择列表中无效，因为它不是在聚合函数或GROUP BY子句

列“qualitystd.QualityStd_id”是在选择列表中，因为它无效不包含在聚合函数或GROUP BY子句中。

代码如下：

set dateformat dmy 
select qualitystd.qualitystd_id, qualitystd.qualitystd, count(edrec.qualitystd_id) as n, targetreacheddate, treelocation1 
from qualitystd  
join (  
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1 
from reports r 
    right join(
     select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date  
     from edit_history  
     where (edit_date < dateadd(day, 1, '11/21/2014')) 
     group by edithistory_id,qualitystd_id  
    ) res 
on r.edithistory_id = res.edithistory_id 
where r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec 
on edrec.qualitystd_id = qualitystd.qualitystd_id 
group by qualitystd.qualitystd_id 
order by qualitystd.qualitystd_id

谁能告诉我在哪里，我去错了吗？前两个选项（从内到外）工作正常。这只是导致问题的最后一个。我以为我已经把qualitystd.qualitystd_id字段放在group by语句中。

当我运行下面的代码它能正常工作。

select qualitystd.qualitystd_id, qualitystd.qualitystd, targetreacheddate, treelocation1 
from qualitystd  
join (  
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1 
from reports r 
    right join(
     select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date  
     from edit_history  
     where (edit_date < dateadd(day, 1, '11/21/2014')) 
     group by edithistory_id,qualitystd_id  
    ) res 
on r.edithistory_id = res.edithistory_id 
where r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec 
on edrec.qualitystd_id = qualitystd.qualitystd_id

不过，我需要补充的计数（qualitystd.qualitystd）或计数（qualitystd.qualitystd_id）的，但我只是不能似乎得到它的工作。

帮助请...

在最后一组group by子句中添加qualitystd.qualitystd列。 – radar 2014-11-21 02:54:23

[列的原因在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中]（http://*.com/questions/13999817/reason-for-column-is -in-the-select-list-because-it-is-not-contained-in-e） – radar 2014-11-21 02:56:21

谢谢。我找到了解决方案。我没有结束使用计数。 – user3681948 2014-12-04 03:46:02

答

您需要从Select语句删除qualitystd.qualitystd，你只能使用在GROUP BY子句中使用select语句这些领域，所以如果你需要不同的计数qualitystd.qualitystd_id，您可以在qualitystd.qualitystd_id上按数据分组时使用qualitystd.qualitystd_id和count（qualitystd.qualitystd_id）。因此，SQL语句将是这样的 `

select qualitystd.qualitystd_id, count(edrec.qualitystd_id) as n, targetreacheddate, treelocation1 
from qualitystd  
join (  
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1 
from reports r 
    right join(
     select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date  
     from edit_history  
     where (edit_date < dateadd(day, 1, '11/21/2014')) 
     group by edithistory_id,qualitystd_id  
    ) res 
on r.edithistory_id = res.edithistory_id 
where r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec 
on edrec.qualitystd_id = qualitystd.qualitystd_id 
group by qualitystd.qualitystd_id 
order by qualitystd.qualitystd_id

，或者如果你需要不同的qualitystd.qualitystd的计数的SQL语句会是这样

select qualitystd.qualitystd, count(edrec.qualitystd_id) as n, targetreacheddate, treelocation1 
from qualitystd  
join (  
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1 
from reports r 
    right join(
     select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date  
     from edit_history  
     where (edit_date < dateadd(day, 1, '11/21/2014')) 
     group by edithistory_id,qualitystd_id  
    ) res 
on r.edithistory_id = res.edithistory_id 
where r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec 
on edrec.qualitystd_id = qualitystd.qualitystd_id 
group by qualitystd.qualitystd 
order by qualitystd.qualitystd

希望这种威力帮助并解决您的问题

在选择列表中无效，因为它不是在聚合函数或GROUP BY子句

相关推荐