对于同一张表，多个左联接是否更好更快

Question

SELECT
E.Id, 
E.Name, 
CASE EL.Application_Level WHEN 33 then 1 ELSE 0 END As MyDisplayName,
CASE EL1.Application_Level WHEN 34 then 1 ELSE 0 END As FoodTag
FROM
Entity E 
INNER JOIN dbo.Countries CN ON cn.id=E.COUNTRY_ID  
LEFT JOIN dbo.Entity_Levels EL ON E.ID = EL.Entity_ID AND EL.Application_Level = 33
LEFT JOIN dbo.Entity_Levels EL1 ON E.ID = EL1.Entity_ID AND EL1.Application_Level = 34

我不想使用同一个表的两个联接，而是使用OR条件，因为会添加更多的应用程序级别，而且我不想添加更多的联接。

我是否可以使用或条件和一个连接，并将多个记录合并为一个记录。

Answer 1

由于您没有显示任何样本数据，这只是一个猜测，但看起来您只需要对ENTITY_LEVELS表进行1次连接就可以完成这样的操作：

SELECT
E.Id, 
max(E.Name) as Name,
max(case when EL.application_level = 33 then 1 else 0 end) as MyDisplayName,
max(case when EL.application_level = 34 then 1 else 0 end) as FoodTag,
max(case when EL.application_level = 35 then 1 else 0 end) as RandomOtherThing
from entity E
INNER JOIN dbo.Countries CN ON cn.id=E.COUNTRY_ID  
LEFT JOIN dbo.Entity_Levels EL ON E.ID = EL.Entity_ID 
group by E.Id

Answer 2

SELECT E.Id
,E.Name
,CASE WHEN EL.Application_Level = 33
THEN 1
ELSE 0
END AS MyDisplayName
,CASE WHEN EL.Application_Level = 34
THEN 1
ELSE 0
END AS FoodTag
FROM Entity E WITH(NOLOCK) 
INNER JOIN dbo.Countries CN WITH(NOLOCK) ON CN.id=E.COUNTRY_ID
LEFT JOIN dbo.Entity_Levels EL WITH(NOLOCK) ON E.ID=EL.Entity_ID