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. ...