在2别名sql server中使用相同的值

Question

我有这样的疑问。

SELECT
  ( SELECT StatusName
   FROM dbo.LMS_MST_Status
   WHERE MainStatusId = a.ParentID ) +'-'+ StatusName AS Value,

  ( SELECT StatusName
   FROM dbo.LMS_MST_Status
   WHERE MainStatusId = a.ParentID ) +'-'+ StatusName AS [Text]
FROM dbo.LMS_MST_Status a
WHERE ParentID > 0
  AND MainStatusID IS NOT NULL
ORDER BY [Text]

我有两个别名，即值和文本，但两者的值是相同的。这些列是在前端处理的，所以我不能更改前端代码。我连化名都改不了。所以我像上面这样做了这个查询。但是我觉得这会影响我的应用程序的性能，因为我对这两个别名都使用子查询。它能被优化吗？

Answer 1

试试left join

select
    b.StatusName +'-'+ a.StatusName AS [Value],
    b.StatusName +'-'+ a.StatusName AS [Text],
FROM dbo.LMS_MST_Status a
left join dbo.LMS_MST_Status b on a.ParentID = b.MainStatusId
WHERE ParentID > 0
AND MainStatusID IS NOT NULL
ORDER BY [Text]

您还可以尝试使用inner join(由@Damien_The_Unbeliever建议)：

select
    b.StatusName +'-'+ a.StatusName AS [Value],
    b.StatusName +'-'+ a.StatusName AS [Text],
FROM dbo.LMS_MST_Status a
inner join dbo.LMS_MST_Status b on a.ParentID = b.MainStatusId
WHERE ParentID > 0
AND MainStatusID IS NOT NULL
ORDER BY [Text]

它不会创建任何null值，只有在满足a.ParentID = b.MainStatusId条件时才能获取数据。

Answer 2

您还可以为此使用APPLY(CROSS or OUTER)：

SELECT  ca.StatusName AS Value ,
        ca.StatusName AS [Text]
FROM    dbo.LMS_MST_Status a
        CROSS APPLY ( SELECT    StatusName + '-' + a.StatusName AS StatusName
                      FROM      dbo.LMS_MST_Status
                      WHERE     MainStatusId = a.ParentID
                    ) ca
WHERE   ParentID > 0
        AND MainStatusID IS NOT NULL
ORDER BY [Text]