从已连接的表中删除重复的行

I have following sql query

我有以下sql查询

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

Which provide following result

提供以下结果

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

My question is how to remove those duplicates and get only following.

我的问题是如何删除这些重复，并只获得跟踪。

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

I tried with

我试着用

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

But it gives me following error

但它给了我以下的错误

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

postswithratings”专栏”。avgscore'在select列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

6 个解决方案

#1

No need to make things complicated. Just go with:

没有必要让事情变得复杂。去:

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

#2

You have to only add distinct keyword like this :-

你只能添加这样的关键字:-。

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

#3

CREATE TABLE #Table2
    ([School] varchar(3), [avgscore] int)

INSERT INTO #Table2
    ([School], [avgscore])
VALUES
    ('xyz', 5),
    ('xyz', 5),
    ('xyz', 5),
    ('abc', 3),
    ('abc', 3),
    ('kkk', 1)
;
SELECT  SCHOOL,AVGSCORE FROM (SELECT *,ROW_NUMBER() OVER( PARTITION BY [AVGSCORE] ORDER BY (SELECT NULL)) AS RN FROM #TABLE2)A
WHERE RN=1
ORDER BY AVGSCORE
-------
;WITH CTE AS
(SELECT *,ROW_NUMBER() OVER( PARTITION BY [AVGSCORE] ORDER BY (SELECT NULL)) AS RN FROM #TABLE2)
SELECT SCHOOL,AVGSCORE  FROM CTE WHERE RN=1

output

输出

SCHOOL  AVGSCORE
kkk      1
abc      3
xyz      5

#4

Using the DISTINCT keyword will make sql use sets instead of multisets. So values only appear once

使用独特的关键字将使sql使用集合而不是多集。所以值只出现一次

#5

This will delete the Duplicate rows (Only Duplicate)

这将删除重复的行(仅重复)

Schema:

模式:

CREATE TABLE #TAB (School varchar(5) , avgscore int)
INSERT INTO #TAB
SELECT 'xyz', 5
UNION ALL
SELECT 'xyz', 5
UNION ALL
SELECT 'xyz', 5
UNION ALL
SELECT 'abc', 3
UNION ALL
SELECT 'abc', 3
UNION ALL
SELECT 'kkk', 1

Now use CTE as your Tempprary View and delete the data.

现在使用CTE作为临时视图并删除数据。

;WITH CTE AS(
SELECT ROW_NUMBER() OVER (PARTITION BY School,avgscore  ORDER BY (SELECT 1)) DUP_C, 
School,  avgscore FROM #TAB
)

DELETE FROM CTE WHERE DUP_C>1

Now do check #TAB, the data will be

现在检查#标签，数据将会是。

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

#6

you only use group by if you're using aggregated function, eg. max. sum, avg

如果使用聚合函数，则只使用group by。max。和,avg

in that case,

在这种情况下,

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

#1