使用复选框访问SQL -多个OR条件

Question

我的查询，哪一种作品如下:

SELECT [copyright status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0))
GROUP BY [copyright status]
UNION
SELECT null,
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0))
UNION
SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0)) AND [lw status] = 'In Reserve'
GROUP BY [lw status]
UNION
SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0)) AND [lw status] = 'Published'
GROUP BY [lw status];

该查询的所有四个部分中的WHERE子句都试图确定选中了提到的三个复选框(识字、算术或贫穷)。这三者的任意组合都可以检查我想要的结果。

原则上，查询工作。但是，输出返回第三部分的两个结果和第四部分的两个结果。

如果我运行查询时只定义了一个复选框，那么:

WHERE (IIF(literacy,1,0)) [lw status] = 'In Reserve'

查询工作得很好，只是添加了一个或多个这些条件似乎会导致问题。

我还尝试通过使用=-1来定义真实值，它返回相同的问题。

许多谢谢。

Answer 1

看看这是否更清楚。您不需要IIF函数来检查WHERE子句中的Yes/No值。另外，还需要括号来表示逻辑:(x OR y OR z) AND w

SELECT null as Status,
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty)
UNION
SELECT [copyright status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty)
GROUP BY [copyright status]
UNION
SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty) AND [lw status] = 'In Reserve'
GROUP BY [lw status]
UNION
SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty) AND [lw status] = 'Published'
GROUP BY [lw status];

对