如何在MariaDB中查询各scope去重公司数及全局总公司数

MariaDB 10.5之前的版本不支持在窗口函数中使用COUNT(DISTINCT)语法，这是此前用OVER()+PARTITION BY实现失败的常见原因。下面给出两种兼容不同版本的实现方案，假设你的表名为your_table：

方案1：兼容所有MariaDB版本（子查询关联实现） #

SELECT 
  t.scopeID,
  COUNT(DISTINCT t.companyID) AS scope_company_cnt,
  g.total_company_cnt
FROM your_table t
CROSS JOIN (
  SELECT COUNT(DISTINCT companyID) AS total_company_cnt FROM your_table
) g
GROUP BY t.scopeID, g.total_company_cnt;

该写法先用子查询算出全局去重的companyID总数，再和按scopeID分组统计的结果做笛卡尔关联，即可让每一行都带上全局总数，所有版本的MariaDB都支持，性能稳定。

方案2：MariaDB 10.5及以上版本（窗口函数实现） #

如果你的数据库版本 >=10.5，支持窗口函数的distinct计数，可以用更简洁的写法：

SELECT 
  scopeID,
  COUNT(DISTINCT companyID) AS scope_company_cnt,
  MAX(total_company_cnt) AS total_company_cnt
FROM (
  SELECT 
    scopeID,
    companyID,
    COUNT(DISTINCT companyID) OVER() AS total_company_cnt
  FROM your_table
) t
GROUP BY scopeID;

内层查询先用窗口函数算出全局去重的companyID总数，外层按scopeID分组统计每个scope下的去重公司数，用MAX取全局总数（每行的全局总数一致，用MAX/MIN/ANY_VALUE都可）即可。

示例验证参考：
输入数据：

ID	companyID	scopeID
1	100	1
2	200	1
3	100	2
4	300	2
5	200	3

两种方案输出结果均为：

scopeID	scope_company_cnt	total_company_cnt
1	2	3
2	2	3
3	1	3

内容的提问来源于stack exchange，提问作者Vml11