使用联接优化 SQL 查询

debugcn 发表于 Dev

健身房代码

我使用了 2 个变量，SELECT每个变量都有一个语句。

有人告诉我，我可以通过使用JOIN只执行一次查询来优化我的查询。

我设法将声明合二为一。但是它没有使用JOIN，并且SELECT在我当前的查询中还有 2 个语句。

当前查询：

BEGIN

DECLARE @EntityId int, @Country nvarchar(10), @OrganizationId int, 
@Username nvarchar(100) = 'user1'

Set @OrganizationId = (SELECT BU.OrganizationId 
FROM [Company].[Config].[BusinessUnit] BU 
WHERE BU.EntityId = (SELECT BU.EntityId
FROM [Company].[PES].[EmployeeProfile] EP
LEFT JOIN   Config.BusinessUnit bu on EP.EntityCode = BU.EntityCode
WHERE EP.Username = @Username))

IF(@OrganizationId = 3)
    SET @Country = 'MS'
ELSE
    SET @Country = 'SG'
SELECT @Country

END

我当前的查询是否可以进行，还是可以进一步改进？谢谢你。

上一个查询：

BEGIN

DECLARE @EntityId int, @Country nvarchar(10), @OrganizationId int, 
@Username nvarchar(100) = 'user1'

Set @EntityId = (SELECT BU.EntityId
FROM [Company].[PES].[EmployeeProfile] EP
LEFT JOIN   Config.BusinessUnit bu on EP.EntityCode = BU.EntityCode
WHERE EP.Username = @Username)

Set @OrganizationId = (SELECT BU.OrganizationId 
FROM [Company].[PES].[BusinessUnit] BU 
WHERE @EntityId = BU.EntityId)

IF(@OrganizationId = 3)
    SET @Country = 'MS'
ELSE
    SET @Country = 'SG'
SELECT @Country

END

戈登·利诺夫

为什么不将其合并为一个查询？

SELECT @Country = (CASE WHEN @OrganizationId = 3 THEN 'MS'
                        ELSE 'SG'
                   END)
FROM [Company].[PES].[EmployeeProfile] EP JOIN
     Config.BusinessUnit cbu 
     ON EP.EntityCode = cbu.EntityCode JOIN
     [Company].[PES].[BusinessUnit] BU 
     ON cbu.EntityId = bu.EntityId
WHERE EP.Username = @Username)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。