昨天我在Postgres中遇到了一个奇怪的问题,当时我试图从统计表中过滤出用户ID.例如,当我们计算user_id != 24
时,postgres排除了user_id
也是NULL
的行.
我创建了以下测试代码,显示了相同的结果.
CREATE TEMPORARY TABLE test1 (
id int DEFAULT NULL
);
INSERT INTO test1 (id) VALUES (1), (2), (3), (4), (5), (2), (4), (6),
(4), (7), (5), (9), (5), (3), (6), (4), (3), (7),
(NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
SELECT COUNT(*) FROM test1;
SELECT id, COUNT(*) as count
FROM test1
GROUP BY id;
SELECT id, COUNT(*) as count
FROM test1
WHERE id != 1
GROUP BY id;
SELECT id, COUNT(*) as count
FROM test1
WHERE (id != 1 OR id IS NULL)
GROUP BY id;
第一个查询只对所有行进行计数.
对于我试图使用此查询的内容,应该始终包含null值.
这是唯一的办法吗?这是预期的研究生行为吗?