选择满足条件的所有值和一些附加值

Question

我有一个包含字段的表：

| Accompanying measures (bool) | Category (String) | Instrument (String) |

因为应用程序的设计实际上并不是要替换这些，所以我用字段ValidUntil(日期类型(修改了表Instrument，该字段要么有一个不推荐使用的日期，要么有一条NULL(如果它仍然有效(
视图显示旧数据和新数据的混合。旧数据可能包含已弃用的Instruments，而新数据则不会
如果我只显示新仪器ValidUntil IS NULL，我会中断页面，因为有选定的Accompanying measures无法显示，因为它们没有从数据库中检索到

Tl；dr
编辑：如何选择所有ValidUntil IS NULL和ValidUntil != NULL, but HasBeenSelectedAsAccompanyingMeasure为true的所有条目。

SELECT
a.AccompanyingMeasureID, a.InstrumentID, a.TaskID,         
a.HasBeenSelectedAsAccompanyingMeasure, a.CategoryID
, i.Name AS InstrumentName,     ic.Name AS CategoryName
FROM
wc_AccompanyingMeasures AS a 
INNER JOIN
wc_Instruments AS i ON a.InstrumentID = i.ID 
INNER JOIN
wc_InstrumentCategories AS ic ON i.CategoryID = ic.ID
WHERE
(a.TaskID = @TaskID) 
// AND for TaskID all rows with HasBeenSelectedAsAccompanyingMeasure = 1
AND (i.ValidUntil IS NULL) 

Answer 1

如果我理解你的问题，你必须使用UNION ALL/UNION来组合两个不同条件的数据集。

--ValidUntil IS NULL
SELECT
a.AccompanyingMeasureID, a.InstrumentID, a.TaskID,         
a.HasBeenSelectedAsAccompanyingMeasure, a.CategoryID
, i.Name AS InstrumentName,     ic.Name AS CategoryName
FROM wc_AccompanyingMeasures AS a 
INNER JOIN wc_Instruments AS i ON a.InstrumentID = i.ID 
INNER JOIN wc_InstrumentCategories AS ic ON i.CategoryID = ic.ID
WHERE (a.TaskID = @TaskID) 
AND (i.ValidUntil IS NULL) 
UNION ALL
--ValidUntil != NULL, but HasBeenSelectedAsAccompanyingMeasure is true
SELECT
a.AccompanyingMeasureID, a.InstrumentID, a.TaskID,         
a.HasBeenSelectedAsAccompanyingMeasure, a.CategoryID
, i.Name AS InstrumentName,     ic.Name AS CategoryName
FROM wc_AccompanyingMeasures AS a 
INNER JOIN wc_Instruments AS i ON a.InstrumentID = i.ID 
INNER JOIN wc_InstrumentCategories AS ic ON i.CategoryID = ic.ID
WHERE (a.TaskID = @TaskID) 
AND (i.ValidUntil IS NOT NULL AND HasBeenSelectedAsAccompanyingMeasure='TRUE')