T-SQL左连接两次

Question

下面的查询按照计划运行，它显示了我所连接的方式，这很好，但是问题是，如果您有更多的"specialization"表格，例如用户可能拥有多个数据的"Mail type"或其他任何内容... 您将不得不为每个表格进行两次左连接并通过ISNULL（在这种情况下）“赋予优先级”。

我想知道如何避免使用两个连接和在一个单独的连接中“赋予”TypeId 2比TypeId 1更高的优先级，是否可能？

if object_id('tempdb..#Tab1') is not null drop table #Tab1
create table #Tab1 (UserId int, TypeId int)
if object_id('tempdb..#Tab2') is not null drop table #Tab2
create table #Tab2 (TypeId int, TypeDescription nvarchar(50))

insert into #Tab1 (UserId, TypeId)
values
(1, 1),
(1, 2)
insert into #Tab2 (TypeId, TypeDescription)
values
(1, 'User'),
(2, 'Admin')

select *, ISNULL(t2.TypeDescription, t3.TypeDescription) [Role]
from #Tab1 t1
    LEFT JOIN #Tab2 t2 on t1.TypeId = t2.TypeId and
                          t2.TypeId = 2
    LEFT JOIN #Tab2 t3 on t1.TypeId = t3.TypeId and
                          t3.TypeId = 1

- Veljko89

Answer 1

第一个问题是确定优先级。在这种情况下，您可以使用最大的TypeId，但这似乎不是一个好主意。您可以添加另一列作为优先级序数。

从那里开始，它是一个每组前1个查询：

使用top with ties和row_number()：

select top 1 with ties
    t1.UserId, t1.TypeId, t2.TypeDescription
from #Tab1 t1
  left join #Tab2 t2
    on t1.TypeId = t2.TypeId
order by row_number() over (
      partition by t1.UserId 
      order by t2.Ordinal
      --order by t1.TypeId desc
)

使用公共表达式和row_number()：

;with cte as (
select t1.UserId, t1.TypeId, t2.TypeDescription
  , rn = row_number() over (
      partition by t1.UserId 
      order by t2.Ordinal
      --order by t1.TypeId desc
    )
from #Tab1 t1
  left join #Tab2 t2
    on t1.TypeId = t2.TypeId
)
select UserId, TypeId, TypeDescription
from cte
where rn = 1

---

我会为两个案例提供rextester演示：http://rextester.com/KQAV36173

两者均返回：

+--------+--------+-----------------+
| UserId | TypeId | TypeDescription |
+--------+--------+-----------------+
|      1 |      2 | Admin           |
+--------+--------+-----------------+

- SqlZim

---

甚至不知道top with ties的存在...感谢您的解释。 - Veljko89

Answer 2

实际上，我认为您根本不需要连接，但是您必须获取最大的TypeID而不考虑TypeDescription，因为这些差异可能会破坏Group By。 因此，解决方法是最初在没有TypeDescription的情况下获取最大值，然后对结果进行子查询以获取TypeDescription。

SELECT dT.*
      ,(SELECT TypeDescription FROM #Tab2 T2 WHERE T2.TypeId = dT.TypeId) [Role] --2. Subqueries TypeDescription using the Max TypeID
  FROM (
         select t1.UserId
               ,MAX(T1.TypeId) [TypeId] 
                --, T1.TypeDescription AS [Role] --1. differences will defeat group by. Subquery for value later in receiving query.
          from #Tab1 t1                
         GROUP BY t1.UserId
       ) AS dT

生成输出：

UserId  TypeId  Role
1       2       Admin

- Zorkolot