我正在嘗試簡化我的存盤程序，我有一個只使用基于傳入的用戶 ID 的左連接。如果用戶 ID 為空，不要進行左連接，但如果它為空，則左連接將它與另一張桌子連接起來。我應該如何重寫它？謝謝
CREATE OR ALTER PROCEDURE [dbo].[GetRoom]
@RoomId UNIQUEIDENTIFIER NULL,
@UserId UNIQUEIDENTIFIER
AS
BEGIN
IF (@UserId IS NULL)
BEGIN
SELECT r.Id, r.DisplayName
FROM Room r
INNER JOIN Game g ON r.GameId = g.Id
INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id
WHERE r.Id = @RoomId
END
ELSE
SELECT
r.Id, r.DisplayName,
ru.Description, -- this is coming from the left join table
ru.Tags -- from left join table
FROM Room r
INNER JOIN Game g ON r.GameId = g.Id
INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id
LEFT JOIN RoomUser ru ON ru.RoomId = r.Id
WHERE r.Id = @RoomId AND ru.UserId = @UserId
END
uj5u.com熱心網友回復：
當前，您的存盤程序根據 @UserId.
您可以洗掉IF條件，并通過移動ru.UserId = @UserId到ON條件將其合并為一個查詢。這將使它成為真正LEFT JOIN的餐桌RoomUser。
這意味著它總是回傳 4 列作為結果
SELECT r.Id,
r.DisplayName,
ru.Description,
ru.Tags
FROM Room r
INNER JOIN Game g ON r.GameId = g.Id
INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id
LEFT JOIN RoomUser ru ON ru.RoomId = r.Id
AND ru.UserId = @UserId
WHERE r.Id = @RoomId
uj5u.com熱心網友回復：
嘗試像下面這樣的東西，
SELECT
r.Id, r.DisplayName,
ru.Description, -- this is coming from the left join table
ru.Tags -- from left join table
FROM Room r
INNER JOIN Game g ON r.GameId = g.Id
INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id
LEFT JOIN RoomUser ru ON ru.RoomId = r.Id
AND r.Id IS NOT NULL
AND r.Id = @RoomId
AND ru.UserId IS NOT NULL
AND ru.UserId = @UserId
uj5u.com熱心網友回復：
您可以使用ISNULL函式來檢查NULL引數的值。（假設ru.userId不會NULL）
WHERE r.Id = @RoomId AND
ru.UserId = ISNULL(@UserId, ru.UserId)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/384639.html
標籤：sql sql-server 查询语句
下一篇：連續52周高點