从连接的表中删除重复行

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 

但是它给我以下错误：

"在选择列表中，列'postswithratings.avgscore'无效，因为它既不包含聚合函数也不包含GROUP BY子句。"

- timton

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 

- Georgi Raychev

---

仅通过添加SELECT DISTINCT，它就起作用了。谢谢。+1 - timton

Answer 2

你只需要像这样添加不同的关键字：-

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

- mansi

Answer 3

这将删除重复行（仅限重复行）

模式：

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

现在使用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

现在请检查 #TAB，数据将会显示

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

- Shakeer Mirza

Answer 4

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

输出

SCHOOL  AVGSCORE
kkk      1
abc      3
xyz      5

- Chanukya

Answer 5

只有在使用聚合函数时，例如max、sum、avg等，才需要使用group by。

在这种情况下，

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

- Jesse

Answer 6

使用DISTINCT关键字将使SQL使用集合而不是多重集合。因此，值只会出现一次。

- Tobias Frischknecht