如何从SQL中左连接的结果中删除重复记录 [英] How to remove duplicate record from results doing left join in SQL

查看：582
本文介绍了如何从SQL中左连接的结果中删除重复记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

引用：

我有三个表用户表，查询表和活动表。当我做一个内部和LEFT JOIN，我得到了由于NULL值而重复记录。



用户表-1





user_id | user_firstName | user_lastName



1 |乔|史密斯



2 |约翰| Doe



3 |罗伯特|史密斯



查询表-2





EnquiryID | CreatedBy | |

1 | 1 |

2 | 1 |



Activiy Table - 3





ActivityID | CreatedBy | AssignedBy | AssignedTO |



1 | 1 | null | null |



2 | 1 | 2 | 3 |



预计所有三项结果的结果是



查询ID | | CreatedBy | AssignedBy | AssignedTO |



1 |乔| null | null |



2 |乔|约翰| Rober |



SQL：



SELECT DISTINCT E.EnquirdID as Inquiry，U.FirstName as CreatedBY， U1.FirstName为AssignedBY，U2，FirstName为AssignedTO

FROM Inquiry E内部联接用户U在E.UserID = U.UserID

内部联接活动A在E.Enquiry上= A.EnquiryID

在A.AssignedBY = U1.UserID上左加入用户U1

A.AssignedTO上的左加入用户U2 = U2.UserID



我从上面的查询中获得重复的查询记录，即使使用Distinct for EnquiryID









我的尝试： 



 SELECT DISTINCT E.EnquirdID as Inquiry，U.FirstName as CreatedBY，U1.FirstName as AssignedBY，U2，FirstName as AssignedTO 
 FROM Inquiry E inner join User U on E.UserID = U.UserID 
内部联接活动A在E.Enquiry = A.EnquiryID 
在A.AssignedBY上离开加入用户U1 = U1.UserID 
 A.AssignedTO上的左加入用户U2 = U2.UserID 
 
我收到重复的查询记录来自以上查询即使使用Distinct for EnquiryID

解决方案

发布的代码似乎没问题，也许你没有发布正确的表格内容/架构，供我们复制问题。下面是使用发布数据和修改模式以匹配查询的示例，它们都产生相同的结果。我的看法是，您可能没有使用错误的列发布足够的信息或SQL查询连接。很难说，因为您提供的查询与架构不匹配。 





  DECLARE   @ user   TABLE （
 UserID  INT ， 
 FirstName  VARCHAR （ 50 ），
 LastName  VARCHAR （ 50 ）
）
 
  DECLARE   @ Inquiry   TABLE （
 EnquiryID  INT ，
 CreatedBy  INT  
）
 
  DECLARE   @ Activity   TABLE （
 ActivityID  INT ，
 CreatedBy  INT ，
 AssignedBy  INT  < span class =code-keyword> NULL ，
 AssignedTO  INT   NULL  ，
 EnquiryID  INT  
）
 
  INSERT  < span class =code-keyword> INTO   @ user  
  SELECT  < span class =code-digit> 1 ，'  Joe'，'  Smith'  UNION  
  SELECT   2 ，'  John'，'  Doe'  UNION  
  SELECT   3 ，'  Robert'，'  Smith' 
 
 < span class =code-keyword> INSERT   INTO   @ Inquiry  
 < span class =code-keyword> SELECT   1 ， 1   UNION  
  SELECT   2 ， 1  
 
  INSERT   INTO   @ Activity  
  SELECT   1 ，< span class =code-digit> 1 ， NULL ， NULL ， 1   UNION  
  SELECT   2 ， 1 ， 2 ， 3 ， 2  
 
   ，不使用查询表 
  SELECT  a.EnquiryID，cb.FirstName，ab.FirstName，at.FirstName  FROM   @ Activity  a 
  JOIN   @ user  cb  ON  a.CreatedBy = cb.UserID 
  LEFT   JOIN   @ user  ab  ON  a.AssignedBy = ab.UserID 
  LEFT   JOIN   @ user  at at  ON  a.AssignedTO = at.UserID 
 
   -  -   使用查询表 
  SELECT  e.EnquiryID，eu.FirstName '  CreatedBy'，ab.FirstName '  AssignedBy'，at.FirstName '  AssignedTO' 
  FROM   @ Inquiry  e 
 加入  @ user  eu  ON  e.CreatedBy = eu.UserID 
  JOIN   @ Activity  a  ON  e.EnquiryID = a。 EnquiryID 
  LEFT   JOIN   @ user  ab  ON  a.AssignedBy = ab.UserID 
  LEFT   JOIN   @ user  at  ON  a.AssignedTO = at.UserID 
 
   -   使用查询对表模式进行少量修改 
  SELECT   DISTINCT  E.EnquiryId  as  Inquiry，U.FirstName  as  CreatedBY，U1.FirstName  as  AssignedBY，U2.FirstName  as  AssignedTO 
  FROM  < span class =code-sdkkeyword> @ Inquiry  E  inner   join   @ user  U  on  E.CreatedBy = U.UserID 
  inner   join   @ Activity    E.EnquiryId = A.EnquiryID 
 左 加入  @ user  U1 上的class =code-keyword> A.AssignedBY = U1.UserID 
 左 加入  @ user  U2  on  A.AssignedTO = U2.UserID 





 EnquiryID FirstName FirstName FirstName 
 1 Joe NULL NULL 
 2 Joe John Robert


Quote:

I have three tables User Table,Enquiry table and Activity Table .when I do a inner and LEFT JOIN, I am getting duplicate records because of NULL values.

User Table -1


user_id | user_firstName | user_lastName

1 | Joe | Smith

2 | John | Doe

3 | Robert | Smith

Enquiry Table -2


EnquiryID | CreatedBy| |
1 | 1 |
2 | 1 |

Activiy Table - 3


ActivityID | CreatedBy| AssignedBy| AssignedTO|

1 | 1 | null | null |

2 | 1 | 2 | 3 |

Expected Output of all three combining result is

Enquiry ID | | CreatedBy| AssignedBy| AssignedTO|

1 | Joe | null | null |

2 | Joe | John | Rober |

SQL:

SELECT DISTINCT E.EnquirdID as Enquiry,U.FirstName as CreatedBY ,U1.FirstName as AssignedBY , U2,FirstName as AssignedTO
FROM Enquiry E inner join User U on E.UserID = U.UserID
inner join Activity A on E.Enquiry = A.EnquiryID
Left Join User U1 on A.AssignedBY = U1.UserID
Left Join User U2 on A.AssignedTO = U2.UserID

I am getting duplicate Enquiry record from above query even though using Distinct for EnquiryID




What I have tried:

SELECT DISTINCT E.EnquirdID as Enquiry,U.FirstName as CreatedBY ,U1.FirstName as AssignedBY  , U2,FirstName as AssignedTO
FROM Enquiry E inner join User U on E.UserID = U.UserID
               inner join Activity A on E.Enquiry = A.EnquiryID
               Left Join User U1  on A.AssignedBY = U1.UserID
               Left Join User U2 on  A.AssignedTO = U2.UserID

I am getting duplicate Enquiry record  from above query even though using Distinct for EnquiryID

解决方案

The posted code seem ok, maybe you didn't post the correct table content/schema for us to replicate the issue. Below is an example using the posted data and modify schema to match the query and they all produce the same results. My take is that maybe you didn't post enough information or your SQL query join using wrong column. Is tough to tell because your provided query doesn't match the schema. 


DECLARE @user TABLE (
	UserID INT,
	FirstName  VARCHAR(50),
	LastName  VARCHAR(50)
)

DECLARE @Enquiry TABLE (
	EnquiryID  INT,
	CreatedBy  INT
)

DECLARE @Activity TABLE (
	ActivityID   INT,
	CreatedBy  INT,
	AssignedBy  INT NULL,
	AssignedTO  INT NULL,
	EnquiryID  INT
)

INSERT INTO @user
	SELECT 1,'Joe','Smith' UNION
	SELECT 2,'John ','Doe' UNION
	SELECT 3,'Robert ','Smith'

INSERT INTO @Enquiry
	SELECT 1,1 UNION
	SELECT 2,1

INSERT INTO @Activity
	SELECT 1,1,NULL,NULL,1 UNION
	SELECT 2,1,2,3,2

--without using Enquiry table
SELECT a.EnquiryID, cb.FirstName, ab.FirstName, at.FirstName FROM @Activity a 
	JOIN @user cb ON a.CreatedBy = cb.UserID
	LEFT JOIN @user ab ON a.AssignedBy = ab.UserID	
	LEFT JOIN @user at ON a.AssignedTO = at.UserID	

--using Enquiry table
SELECT e.EnquiryID, eu.FirstName 'CreatedBy' ,ab.FirstName 'AssignedBy',  at.FirstName 'AssignedTO'
	FROM @Enquiry e
	JOIN @user eu ON e.CreatedBy = eu.UserID
	JOIN @Activity a ON e.EnquiryID = a.EnquiryID
	LEFT JOIN @user ab ON a.AssignedBy = ab.UserID	
	LEFT JOIN @user at ON a.AssignedTO = at.UserID	

--Using your query with small modification to the table schema
SELECT DISTINCT E.EnquiryId as Enquiry,U.FirstName as CreatedBY ,U1.FirstName as AssignedBY , U2.FirstName as AssignedTO
FROM @Enquiry E inner join @user U on E.CreatedBy = U.UserID
inner join @Activity A on E.EnquiryId = A.EnquiryID
Left Join @user U1 on A.AssignedBY = U1.UserID
Left Join @user U2 on A.AssignedTO = U2.UserID



EnquiryID	FirstName	FirstName	FirstName
1	Joe	NULL	NULL
2	Joe	John 	Robert


这篇关于如何从SQL中左连接的结果中删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆