python - 使用 "case"时获取空行(实际上是使用聚合时)

Question

我正在使用 python 脚本查询 sqlite 数据库表。

SELECT servername, 
     date('now','localtime'),
     sum(CASE WHEN locked="NO" THEN 1 END),
     sum(CASE WHEN locked="YES" THEN 1 END) 
FROM nodes
WHERE servername="SOME_SERVER"

它工作正常(给我服务器名称、今天的日期、解锁节点的数量和锁定节点的数量)...

...除非查询没有匹配项(即没有提供的名称的服务器名称)。那么查询结果就是

null,"2013-04-15",null,null

(为了清楚起见，我添加了空值)

某些服务器没有节点是有效的。如果我从查询中删除“案例”，我不会得到任何查询结果，因此当我不想要结果时，它们会导致我得到结果。

如何让它在 SQL 查询中不返回任何内容，而不需要在 python 中检测它或事先查询计数？

Answer 1

嗯，添加一个

group by servername

正如我偶然发现的那样，如果没有匹配项，

会使空行消失。我在解释这一点时遇到了困难，但我认为 sqlite 文档中的内容涵盖了它:

If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.

The single row of result-set data created by evaluating the aggregate and non-aggregate expressions in the result-set forms the result of an aggregate query without a GROUP BY clause. An aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data.