替换 SQL 查询

Question

我想将此查询替换

DECLARE @tmpUser TABLE( UserId INT, UserType INT )

INSERT INTO @tmpUser
SELECT
    u.UserId,
    1
FROM
    Users u (nolock)
WHERE
    u.UPIN = @AttendingDoctorID

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO @tmpUser
    SELECT
        u.UserId,
        1
    FROM
        Users u (nolock)
    WHERE
        u.FirstName = @AttendingDoctorFirstName AND
        u.LastName = @AttendingDoctorLastName
END
SELECT * FROM @tmpUser

为单个 SQL SELECT 语句，而不使用 @tmpUser 或任何其他临时表

Answer 1

以下使用了额外的查找，但它满足您的要求。我不确定这是否比使用临时表更有效，但如果 UPIN 被索引，那么我怀疑它会更有效。

IF EXISTS(
    SELECT 
        1 
    FROM 
        Users u 
    WHERE 
        u.UPIN = @AttendingDoctorID)
BEGIN
    SELECT 
        u.UserId, 1 
    FROM 
        Users u  WITH(nolock)
    WHERE 
        u.UPIN = @AttendingDoctorID
END ELSE BEGIN
    SELECT
        u.UserId,
        1
    FROM
        Users u (nolock)
    WHERE
        u.FirstName = @AttendingDoctorFirstName AND
        u.LastName = @AttendingDoctorLastName
END

Answer 2

使用 OR 而不是两个单独的查询怎么样？

我认为这会达到目的。

SELECT
    u.UserId, 1
FROM
    Users u (nolock)
WHERE
    (u.UPIN = @AttendingDoctorID)
    OR
    (u.FirstName = @AttendingDoctorFirstName AND
    u.LastName = @AttendingDoctorLastName)