使用两个表使用sql从数据库中检索教员名称 [英] using two tables retrieve the faculty name from the database using sql

问题描述

在表格记录中如下（表格名称为Tb_Presea_Faculty_Feedback）



反馈意见FacQ1 FacQ2 FacQ3 FacQ4 FacQ5



1 1 5 4 3 2 2

1 2 2 3 4 5 2

1 3 3 4 5 2 3


查询如下



选择Feedbackid，sum（b.FacQ1）/3.0为Q1，sum（b.FacQ2）/3.0为Q2，sum （b.FacQ3）/3.0为Q3，sum（b.FacQ4）/3.0为Q4，sum（b.FacQ5）/3.0为Q5，来自Tb_Presea_Faculty_Feedback为b，其中b.Feedbackid ='1'组由Feedbackid



当我在查询输出上方运行时如下



Feedbackid Q1 Q2 Q3 Q4 Q5



1 3.33 3.66 4.00 3.00 2.33



表记录如下（表名Tb_Presea_Faculty_Master）



Facid Facname

1 Gopi

2 Ram

3 Suresh





i想要从数据库中显示Facname。在上面的查询中，我希望输出如下。



Feedbackid Facname Q1 Q2 Q3 Q4 Q5



1 Gopi 3.33 3.66 4.00 3.00 2.33





获得以上输出。我写了如下查询，



选择a.Facname，b.Feedbackid，sum（b.FacQ1）/3.0为Q1，sum（b.FacQ2）/ 3.0为Q2，sum（b.FacQ3）/3.0为Q3，sum（b.FacQ4）/3.0为Q4，sum（b.FacQ5）/3.0为Q5，Tb_Presea_Faculty_Feedback为b，Tb_Presea_Faculty_Master为b，其中b.Feedbackid =' 1'和a.Facid = b.Facid group by Feedbackid





但我执行上面的查询显示错误如下，



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





请帮帮我。我上面的代码中有什么问题。

In table records as follows(Table Name Tb_Presea_Faculty_Feedback)

Feedback Facid FacQ1 FacQ2 FacQ3 FacQ4 FacQ5

1 1 5 4 3 2 2
1 2 2 3 4 5 2
1 3 3 4 5 2 3

Query as follows

select Feedbackid,sum(b.FacQ1) /3.0 as Q1,sum(b.FacQ2)/3.0 as Q2,sum(b.FacQ3)/3.0 as Q3,sum(b.FacQ4) /3.0 as Q4,sum(b.FacQ5) /3.0 as Q5 from Tb_Presea_Faculty_Feedback as b where b.Feedbackid = '1' group by Feedbackid

When i run above query output as follows

Feedbackid Q1 Q2 Q3 Q4 Q5

1 3.33 3.66 4.00 3.00 2.33

in Table records as follows(Table Name Tb_Presea_Faculty_Master)

Facid Facname
1 Gopi
2 Ram
3 Suresh


i want to display the Facname from the database. in the above query i want output as follows.

Feedbackid Facname Q1 Q2 Q3 Q4 Q5

1 Gopi 3.33 3.66 4.00 3.00 2.33


For getting a above output. i written a query as follows,

select a.Facname,b.Feedbackid,sum(b.FacQ1) /3.0 as Q1,sum(b.FacQ2)/3.0 as Q2,sum(b.FacQ3)/3.0 as Q3,sum(b.FacQ4) /3.0 as Q4,sum(b.FacQ5) /3.0 as Q5 from Tb_Presea_Faculty_Feedback as b,Tb_Presea_Faculty_Master as a where b.Feedbackid = '1' and a.Facid=b.Facid group by Feedbackid


But i execute a above query shows error as follows,

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


please help me. what is the problem in my above code.

推荐答案

每当你在sql中进行聚合查询时，select语句中包含的所有列都必须按分组或聚合，即sum（）或count（）等。



因此，要解决此问题，请从选择列表中删除a.Facname，将其包含在group by子句中，或汇总（例如，计数（a.facName）



仔细检查您的问题后，您似乎需要从选择列表和分组中删除feedbackId子句，并将facname添加到group by子句中，这将为每个facname值提供总和。

Whenever you are doing an aggregate query in sql all columns included in the select statement must either be grouped by, or aggregated, i.e. sum() or count() etc.

So to solve the issue either remove a.Facname from your select list, include it in your group by clause, or aggregate on it (e.g. count(a.facName)

Upon closer inspection of your issue, it appears that you need to remove feedbackId from your select list and group by clause, and add facname to your group by clause, this would give your sums for each facname value.

这篇关于使用两个表使用sql从数据库中检索教员名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！