sql - 从连接表中删除重复行

Question

我有以下sql查询

SELECT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode 

提供以下结果

School| avgscore
xyz   |  5
xyz   |  5
xyz   |  5
abc   |  3
abc   |  3
kkk   |  1

我的问题是如何删除这些重复项并仅获得以下内容。

 School| avgscore
    xyz   |  5 
    abc   |  3
    kkk   |  1

我尝试过

   SELECT m.School, c.avgscore 
   FROM postswithratings c 
   join ZEntrycriteria on c.fk_postID= m.schoolcode 
   group by m.School 

但它给了我以下错误

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

Answer 1

没必要把事情搞复杂。只需选择:

SELECT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode 
group by m.School, c.avgscore 

或

SELECT DISTINCT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode