关于如果一条返回单个值的查询不返回行,则如何返回NULL,这里有很多问题。但是,我没有找到任何关于当多值查询不返回结果时返回NULL的内容。
主查询:
SELECT UserOpinion.*, x, y, z... FROM subquery, (x) AS x, (y) AS y...
I trust you get the gist.
我要查询的是一个子查询:
(SELECT TOP 1
u.BADId,
Date,
State,
Note,
Comment,
Alias,
Title,
UserId,
o.Id AS OpinionId
FROM
[Opinion] o
RIGHT JOIN
[User] u
ON
o.UserId = u.Id
WHERE
(Date IS NULL OR (Date = (SELECT MAX(Date)
FROM [Opinion]
WHERE UserId = o.UserId )))
AND
u.BADId = 'myvalue') AS UserOpinion;
例如,我尝试了以下方法:
(SELECT TOP 1 * FROM (SELECT TOP 1
u.BADId,
Date,
State,
Note,
Comment,
Alias,
Title,
UserId,
o.Id AS OpinionId
FROM
[Opinion] o
RIGHT JOIN
[User] u
ON
o.UserId = u.Id
WHERE
(Date IS NULL OR (Date = (SELECT MAX(Date)
FROM [Opinion]
WHERE UserId = o.UserId )))
AND
u.BADId = 'myvalue'
UNION SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) t) AS UserOpinion;
无论 'myvalue' 是否存在,此代码块都会返回 NULL。我希望的是,如果存在条目,则返回查询结果的值,否则在每列上返回 null 值。
我还尝试了以下方法:
(COALESCE(SELECT TOP 1
u.BADId,
Date,
State,
Note,
Comment,
Alias,
Title,
UserId,
o.Id AS OpinionId
FROM
[Opinion] o
RIGHT JOIN
[User] u
ON
o.UserId = u.Id
WHERE
(Date IS NULL OR (Date = (SELECT MAX(Date)
FROM [Opinion]
WHERE UserId = o.UserId )))
AND
u.BADId = 'myvalue'), NULL) AS UserOpinion;
主要问题在于,如果'myvalue'没有匹配项,则整个查询将返回空值。其他查询不需要myvalue,而进行多个独立查询对于性能约束来说不是一个选项。
我并不是SQL专家,任何朝着正确方向的建议都将不胜感激。
类似的问题但在这里不适用: