如何在连接两个表时获得不同的记录，并且一个名称在左表中出现两次，而在右表中仅出现一次. [英] How to get distinct records while joining two tables and a name appears twice in left table while its only once their in right table.

问题描述

在sql中获取不同的记录
假设我有一个名称为
的表
表1(左表)
ID TenantID软件包
1 10金
2 10银
3 6白金
4 6钻石

我有另一个表，TenantName来自(右表)

表2
ID TenantID TenantName
1 10 Catra Traders
2 6个Meena供应商
3 4 Arun Enterprise

如何在join中获得不同的租户名称
当我通过写作加入他们的时候

从
中选择T1.TenantID，T2.TenantName，T1.Packagename Table1 T1 T1上的左外部联接Table2 T2.TenantID = T2.TenantID

我得到的结果是

TenantID TenantName包
10 Catra Traders金币
10个Catra Traders银币
6个Meena供应商白金
6个Meena供应商钻石

但我想要它
TenantID TenantName包
10 Catra Traders金币
6个Meena供应商白金

Getting distinct records In sql
Suppose I have a table having Name

Table1 (Left Table)
ID TenantID Package
1 10 Gold
2 10 Silver
3 6 Platinum
4 6 Diamond

I have Another table from where the TenantName is coming from (Right Table)

Table 2
Id TenantID TenantName
1 10 Catra Traders
2 6 Meena Suppliers
3 4 Arun Enterprise

How can i get distinct Tenant names in join
When I join them by writing

Select T1.TenantID,T2.TenantName,T1.Packagename from
Table1 T1 Left outer join Table2 T2 on T1.TenantID=T2.TenantID

I am getting result as

TenantID TenantName Package
10 Catra Traders Gold
10 Catra Traders Silver
6 Meena Suppliers Platinum
6 Meena Suppliers Diamond

But i want it as
TenantID TenantName Package
10 Catra Traders Gold
6 Meena Suppliers Platinum

How to do?

推荐答案

Create PROCEDURE sp_Proc1

AS
BEGIN

 With TEMP as
(

  Select  ROW_NUMBER()
  OVER(partition by T1.TenantId Order by T1.ID)As Rownumber,T1.Id,
  T2.TenantName,T1.PackageName
  FROM Table1 T1
  INNER JOIN Table2 T2 on T1.TenantId=T2.TenantId
 )
  SELECT * from TEMP where Rownumber=1

这篇关于如何在连接两个表时获得不同的记录，并且一个名称在左表中出现两次，而在右表中仅出现一次.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！