在SQL Server中显示NULL值加入

Question

我正在使用SQL Server。我有3个表：

1. tblUser
2. tblAudit
3. tblEnum

的tblAudit得到了4列：

nUserId，的NID：这两列指向tblUser

vFromValue，vToValue：这两列指向tblEnum

现在，我需要根据tblAudit表中存在的ID显示来自tblUser和tblEnum的实际值。 我写的查询看起来是这样的：

SELECT A.aEventID, 
U.tName AS MakerChecker, 
U2.tName AS OnUser, 
VLE.tDisplayName AS FromValue, 
VLE2.tDisplayName AS ToValue, 
A.dChangeTime AS Timestamp 
FROM tblAudit A INNER JOIN tblUser U ON A.nUserId = U.aUserId 
INNER JOIN tblUser U2 ON A.nID = U2.aUserId 
INNER JOIN tblEnum VLE ON A.vFromValue = VLE.nIndex OR (A.vFromValue IS NULL) 
INNER JOIN tblEnum VLE2 ON A.vToValue = VLE2.nIndex OR (A.vToValue IS NULL) 

因为在审核表中的值是零对于一些行的列vFromValue和vToValue，这些值都不会再为NULL，但以前的值。 如何通过更改上述查询来完整显示空值？请帮忙。

Answer 1

由于tblAudit.vFromValue和tblAudit.vToValue有NULL S，试试这个：

SELECT A.aEventID, 
     U.tName AS MakerChecker, 
     U2.tName AS OnUser, 
     VLE.tDisplayName AS FromValue, 
     VLE2.tDisplayName AS ToValue, 
     A.dChangeTime AS Timestamp 
FROM 
    (SELECT aEventID, nUserId, nID, dChangeTime 
      , COALESCE(vFromValue, -9999) AS vFromValue --- some non-existent 
      , COALESCE(vToValue, -999) AS vToValue  --- value 
     FROM tblAudit 
    ) AS A 
    INNER JOIN tblUser U 
    ON A.nUserId = U.aUserId 
    INNER JOIN tblUser U2 
    ON A.nID = U2.aUserId 
    LEFT JOIN tblEnum VLE 
    ON A.vFromValue = VLE.nIndex 
    LEFT JOIN tblEnum VLE2 
    ON A.vToValue = VLE2.nIndex 

或本：

SELECT A.aEventID, 
     U.tName AS MakerChecker, 
     U2.tName AS OnUser, 
     (SELECT VLE.tDisplayName 
     FROM tblEnum VLE 
     WHERE A.vFromValue = VLE.nIndex 
     ) AS FromValue, 
     (SELECT VLE2.tDisplayName 
     FROM tblEnum VLE2 
     WHERE A.vToValue = VLE.nIndex 
     ) AS ToValue, 
     A.dChangeTime AS Timestamp 
FROM tblAudit AS A 
    INNER JOIN tblUser U 
    ON A.nUserId = U.aUserId 
    INNER JOIN tblUser U2 
    ON A.nID = U2.aUserId 

Answer 2

如果我理解你的问题 - 尝试对最后两行使用LEFT JOIN（并在OR之后删除条件）。