在Sqlserver中执行以下查询时显示错误 [英] Show error when i execute the below query in Sqlserver

问题描述

我的查询如下





选择不同的平均值（r.rate）作为Rate，f.bfid为BFid

来自bthfac AS f，批次b，facfeedback r其中f.facid =''60,75,6''

和f.Bthid = b.Bthid和r.bfid = f.bfid和b.class =''PST''

和年份（b.examdate）='''2011''和月份（b.examdate）='''10''Group By r .Bfid



当我执行上述查询时显示错误如下





列''bthfac.BFid''在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。





我上面的SQL查询有什么问题。



请帮帮我。



问候，

Narasiman P.

解决方案

基本上，你是平均所有r.rate，然后在第一名为什么你需要BFid吗？



我觉得它不是r在那里得到了它不应该;



并且SQL也不会允许它，因为它不一致而且在你进行Group by时无法实现。



因此我建议你了解Group by及其工作原理。



一些链接供你参考：http://www.w3schools.com/sql/sql_groupby.asp [ ^ ]

当你使用聚合函数时，你有顶级使用组，所以你必须提到< pre lang =SQL> r.Rate

也在Group By Clause中

当您按某些列值进行分组时，您可以只选择SELECT语句中的那些列。您不能选择那些不在GROUP BY子句中的列。在你的陈述中，你是由r.Bfid分组，但是，选择f.bfid。这是不允许的。请自己考虑一下为什么SELECT语句中不允许使用非GROUP BY列。



我已经更改了查询。检查一下。

 选择  distinct  avg（r.rate） As  Rate，r.Bfid  as  BFid 
  FROM  bthfac  AS  f，批次b，facfeedback r 其中 f.facid = '  60,75,6' 
 < span class =code-keyword>和 f.Bthid = b.Bthid 和 r.bfid = f.bfid 和 b.class = '  PST' 
 和年（b.examdate）= '  2011' 和月（b.examdate）= '  10'  Gro up  按 r.Bfid 

My query as follows


select distinct avg(r.rate) As Rate,f.bfid as BFid
FROM bthfac AS f,batch b,facfeedback r where f.facid= ''60,75,6''
and f.Bthid=b.Bthid and r.bfid = f.bfid and b.class= ''PST''
and year(b.examdate)= ''2011'' and month(b.examdate)= ''10'' Group By r.Bfid

When i execute the above query shows error as follows


Column ''bthfac.BFid'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


what is the problem in my above sql query.

please help me.

Regards,
Narasiman P.

解决方案

Basically, you are perfoming an average of all r.rate then in first place why you need BFid?

I think id its not required there and it should not be;

and also SQL will not allow it because its not consistent and cannot be achieved while you do Group by.

Therefore I would recommend you to understand what is Group by and how it works.

Some link for your reference: http://www.w3schools.com/sql/sql_groupby.asp[^]

When u use aggregate functions u have top use group by so u have to mention

r.Rate

also in Group By Clause

When you are grouping by some column(s) value, then you may select only those column(s) in the SELECT statement. You can not SELECT any column(s) those are not in GROUP BY clause. In you statement, you are grouping by r.Bfid, but, selecting f.bfid. This is not allowed. Just think it yourself why non GROUP BY column(s) are not allowed in SELECT statement.

I have changed the query. Check this.

select distinct avg(r.rate) As Rate, r.Bfid as BFid
FROM bthfac AS f,batch b,facfeedback r where f.facid= '60,75,6'
and f.Bthid=b.Bthid and r.bfid = f.bfid and b.class= 'PST'
and year(b.examdate)= '2011' and month(b.examdate)= '10' Group By r.Bfid

这篇关于在Sqlserver中执行以下查询时显示错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！