Mysql 8.0.33在使用窗口函数的同时不能用count(distinct *)
October 18, 2023
Mysql 8.0.33在使用窗口函数的同时不能用count(distinct *)
比如，我想在窗口函数里使用字段apply_unit_id分组，然后求project_id列不重复值的数量：
select distinct apply_unit_id, count(distinct project_id) over (partition by apply_unit_id)
from weia join weiag on weiag.apply_id = weia.id
;
此时报错：SQL 错误 [1235] [42000]: This version of MySQL doesn't yet support '<window function>(DISTINCT ..)'
怎么办呢？ #
使用
dense_rank()间接计算：
select distinct apply_unit_id, dense_rank() over (partition by apply_unit_id order by project_id)
+ dense_rank() over (partition by apply_unit_id order by project_id desc)
- 1
from weia join weiag on weiag.apply_id = weia.id
;
dense_rank() #
Returns the rank of the current row within its partition, without gaps.
– 返回当前行在其分区内的排名，没有间隙。
...