sql-server - SQL Server 消除函数调用并替换为连接

Question

我正在尝试通过删除函数来重写“查询”部分。
由于该表有超过 800k 条记录，因此为所有 800k 条记录调用该函数三次。
我想消除函数并用 JOIN 代替？

--Function
CREATE FUNCTION [dbo].[MapValue] 
(
    @ObjCode AS INT,
    @ObjName AS VARCHAR(50),
    @ObjValue AS INT
)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @returnValue AS VARCHAR(100)

    SELECT @returnValue = Value FROM dbo.tblMap
    WHERE ObjCode = @ObjCode AND ObjName = @ObjName AND ObjValue = @ObjValue

    -- Return the result of the function
    RETURN @returnValue

END

--Query
SELECT  ObjectId, ObjectTypeCode,
        dbo.MapValue(4, 'ACode', ACode) AS AType,
        dbo.MapValue(4, 'SCode', SCode) AS SCode,
        dbo.MapValue(4, 'PCode', PCode) AS PCode
FROM    dbo.APoint
WHERE   ObjectTypeCode = 1

这是我使用 JOIN 提出的查询。
有没有更好的方法来做到这一点？我们可以只使用一个联接而不是三个联接吗？

--Modified query
SELECT  ObjectId, ObjectTypeCode,
        A.Value,
        s.Value,
        p.Value
FROM    dbo.APoint ap
    LEFT JOIN tblMap A ON A.ObjCode = 4 AND A.ObjName = 'ACode' AND A.ObjValue = ap.TypeCode
    LEFT JOIN tblMap s ON s.ObjCode = 4 AND s.ObjName = 'SCode' AND s.ObjValue = ap.TypeCode
    LEFT JOIN tblMap p ON p.ObjCode = 4 AND p.ObjName = 'PCode' AND p.ObjValue = ap.TypeCode
WHERE   ObjectTypeCode = 1

Answer 1

您可以使用 case 和一个左连接:

SELECT  ObjectId, ObjectTypeCode,
        CASE WHEN map.ObjName = 'ActivityTypeCode' THEN map.Value END AS AType,
        CASE WHEN map.ObjName = 'statecode' THEN map.Value END As SCode,
        CASE WHEN map.ObjName = 'PriorityCode' THEN map.Value END As PCode
FROM    dbo.APoint ap
LEFT JOIN tblMap map ON map.ObjCode = 4 
                    AND map.ObjName IN('ActivityTypeCode', 'statecode', 'PriorityCode') 
                    AND map.ObjValue = ap.ActivityTypeCode
WHERE   ObjectTypeCode = 1
GROUP BY ObjectId, ObjectTypeCode