Microsoft SQL - 从查询结果中删除重复数据 [英] Microsoft SQL - Remove duplicate data from query results
问题描述
我是 SQL Server 的新手，需要有关我的 SQL 查询之一的帮助.
I am new to SQL Server and need help with one of my SQL query.
我有 2 个表(Rating 和 LikeDislike).
I have 2 tables (Rating and LikeDislike).
我正在尝试使用这样的 LEFT JOIN 从这两个表中获取数据:
I am trying to get data from both of these tables using a LEFT JOIN like this:
SELECT distinct LD.TopicID, R.ID, R.Topic, R.CountLikes, R.CountDisLikes, LD.UserName, LD.Clikes
FROM Rating As R
LEFT JOIN LikeDislike AS LD on LD.TopicID = R.ID
上面的 SELECT 语句显示结果很好，但也包含重复项.我想在显示数据时删除重复项，我尝试使用 DISTINCT 和 GROUP BY，但没有运气，可能是因为我没有正确使用它.
The above SELECT statement displays results fine but also includes duplicates. I want to remove duplicates when the data is displayed, I tried using DISTINCT and GROUP BY, but with no luck, maybe because I am not using it correctly.
为了更清晰和更少混淆，让我告诉您每个表的确切作用以及我想要实现的目标.
To be more clear and less confusing let me tell you what exactly each table does and what I am trying to achieve.
Rating 表具有以下列(ID、Topic、CountLikes、CountDisLikes、Extra、CreatedByUser).它存储每个主题的主题信息和喜欢和不喜欢的数量以及创建该主题的用户的 UserID.
The Rating table has following columns (ID, Topic, CountLikes, CountDisLikes, Extra, CreatedByUser). It stores topic information and number of likes and dislikes for each topics and the UserID of the user who created that topic.
带有样本数据的评分表
ID Topic CountLikes CountDisLikes Extra CreatedByUser
1 Do You Like This 211 58 YesId 2
2 Or This 17 25 This also 3
79 Testing at home 1 0 Testing at home 2
80 Testing at home again 1 0 Testing 2
82 testing dislikes 0 1 Testing 2
76 Testing part 3 7 5 Testing 3 4
77 Testing part 4 16 6 Testing 4 5
LikeDisLike 表具有以下列(ID、TopicID、UserName、Clikes).TopicID 是 Rating 表中 ID 列的 FK.
The LikeDisLike table has following columns (ID, TopicID, UserName, Clikes). TopicID is a FK to the ID column in Rating table.
包含示例数据的 LikeDislike 表
LikeDislike table with sample data
ID TopicID UserName Clikes
213 77 2 TRUE
214 76 2 FALSE
215 77 5 TRUE
194 77 3 TRUE
195 76 3 FALSE
196 2 3 TRUE
197 1 3 FALSE
现在我要做的是从这两个表中获取信息，而没有重复的行.我需要从 Rating 表 + UserName 和 Clikes 列中获取数据所有列 LikeDislike 表中没有任何重复行
Now what I am trying to do is get information from both of this table without duplicate rows. I need to get data all the columns from Rating table + UserName and Clikes columns from LikeDislike table without any duplicate rows
以下是重复的结果
TopicID ID Topic CountLikes CountDislikes UserName Clikes
NULL 79 Testing at home 1 0 NULL NULL
NULL 80 Testing at home2 1 0 NULL NULL
NULL 82 testing dislikes 0 1 NULL NULL
1 1 Do You Like This 211 58 3 FALSE
2 2 Or This 17 25 3 TRUE
76 76 Testing part 3 7 5 2 FALSE
76 76 Testing part 3 7 5 3 FALSE
77 77 Testing part 4 16 6 2 TRUE
77 77 Testing part 4 16 6 3 TRUE
77 77 Testing part 4 16 6 5 TRUE
推荐答案
就像昨天的 post，我认为您不明白 DISTINCT 应该返回给您什么.因为您的 LikeDislike 表中有不同的值，所以您将返回 DISTINCT 行.
Just like in yesterday's post, I don't think you understand what DISTINCT is suppose to return you. Because you have different values in your LikeDislike table, you are returning the DISTINCT rows.
让我们以 TopicId 77 为例.它返回 3 个 DISTINCT 行，因为您的 LikeDislike 表中有 3 个匹配的记录.如果您想要的输出是单行，其中 UserName 和 Clikes 以逗号分隔，那是可能的——考虑使用 for xml 和 stuff (这里 是关于该主题的最新答案).或者，如果您想返回与 TopicId 匹配的第一行，那么这也是可能的——考虑使用带有 row_number 的子查询.
Let's take TopicId 77 for instance. It returns 3 DISTINCT rows because you have 3 matching records in your LikeDislike table. If your desired output is a single row where the UserName and Clikes are comma delimted, that is possible -- look into using for xml and perhaps stuff (here is a recent answer on the subject). Or if you want to return the first row that matches the TopicId, then that is possible as well -- look into using a subquery with row_number.
请告诉我们您想要的输出，我们可以帮助提供解决方案.
Please let us know your desired output and we can help provide a solution.
祝你好运.
这篇关于Microsoft SQL - 从查询结果中删除重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
