MariaDB:伯爵被窃听了吗？
我有三个请求：
SELECT COUNT(DISTINCT origine_client_id, annee_imputation)
FROM dossier d1;34438
SELECT COUNT(DISTINCT d2.origine_client_id, d2.annee_imputation)
FROM (SELECT origine_client_id, annee_imputation
FROM dossier) as d2;34438
SELECT COUNT(*)
FROM (SELECT DISTINCT origine_client_id, annee_imputation
FROM dossier) as d3;34478
但我没有同样的结果，为什么？(我正在使用Mariadb)
编辑:
@jarlh
SELECT COUNT(DISTINCT origine_client_id) FROM dossier; => 19 488
SELECT COUNT(DISTINCT annee_imputation) FROM dossier; => 42
@a_horse_with_no_name
是的，有空值。
SELECT COUNT(id) FROM dossier WHERE annee_imputation IS NULL; => 1
SELECT COUNT(id) FROM dossier WHERE origine_client_id IS NULL; => 289 711
SELECT COUNT(id) FROM dossier WHERE origine_client_id IS NULL AND annee_imputation IS NULL; => 1
回答 2
Stack Overflow用户
发布于 2020-11-16 18:30:28
根据医生：
计数(不同费用，[费用.]) 返回不同非空值数量的计数。
这个语法是非标准的MySQL/MariaDB扩展。它似乎将“非空”视为所有的非空被计算。
演示：
CREATE TABLE dossier
AS
SELECT 1 origine_client_id, 2 annee_imputation UNION ALL -- both values provided
SELECT NULL, NULL UNION ALL
SELECT NULL origine_client_id, 1 annee_imputation UNION ALL
SELECT 1 origine_client_id, NULL annee_imputation;查询：
SELECT COUNT(DISTINCT origine_client_id, annee_imputation) FROM dossier d1;
-- 1
SELECT COUNT(DISTINCT d2.origine_client_id, d2.annee_imputation)
FROM (SELECT origine_client_id, annee_imputation FROM dossier) as d2;
-- 1
SELECT COUNT(*)
FROM (SELECT DISTINCT origine_client_id, annee_imputation FROM dossier) as d3;
-- 4Stack Overflow用户
发布于 2020-11-16 15:42:03
我想a_horse_with_no_name问了一个相关的问题。这比较简单，而且可能类似：
CREATE TABLE t (s1 INT PRIMARY KEY, s2 INT);
INSERT INTO t VALUES (1, 1), (2, NULL), (3, NULL);
SELECT COUNT(DISTINCT s2) FROM t;
SELECT COUNT(*) FROM (SELECT DISTINCT s2 FROM t) AS x;在第一种情况下，在函数完成之前就消除了空，答案是1。在第二种情况下，在函数完成之前没有消除空，所以答案是2。随后添加了更新:很抱歉没有说清楚:这个示例显示了不违背SQL标准的预期行为，只是没有关于消除空值的警告。
https://stackoverflow.com/questions/64860203
复制相似问题
腾讯云开发者
