mysql根据某个字段去重数据,使用any_value函数和group by函数

简单例子

sql分析

1. 原sql
   SELECT
   rt.name_ procActivityName,
   rt.ID_ procActivityId,
   re.BUSINESS_KEY_,
   rp.KEY_ processKey,
   a.*,
   u1.user_id createUserId,
   u1.nick_name createUserName,
   u2.user_id assigneeUserId,
   u2.nick_name assigneeUserName
   FROM
   act_ru_task rt
   LEFT JOIN act_ru_execution re ON re.ID_ = rt.PROC_INST_ID_
   RIGHT JOIN asset_bills_run a ON a.bills_id = REPLACE ( re.BUSINESS_KEY_, ‘asset_’, ‘’ )
   LEFT JOIN act_re_procdef rp ON re.PROC_DEF_ID_ = rp.id_
   LEFT JOIN sys_user u1 ON u1.user_name = a.create_by
   LEFT JOIN sys_user u2 ON u2.user_id = rt.ASSIGNEE_
   WHERE
   a.process_run_id IS NOT NULL

由于这条业务数据对应了两个处理人,导致前面的数据都是一样的,而我只需要展示其中一个处理人就好了.

解决方式

2.新sql
SELECT
any_value(a.bills_id),
any_value(rt.name_) procActivityName,
any_value(rt.ID_) procActivityId,
any_value(re.BUSINESS_KEY_) BUSINESS_KEY_,
any_value(rp.KEY_) processKey,
a.*,
any_value(u1.user_id) createUserId,
any_value(u1.nick_name) createUserName,
any_value(u2.user_id) assigneeUserId,
any_value(u2.nick_name) assigneeUserName
FROM act_ru_task rt
LEFT JOIN act_ru_execution re ON re.ID_ = rt.PROC_INST_ID_
RIGHT JOIN asset_bills_run a ON a.bills_id = REPLACE ( re.BUSINESS_KEY_, ‘asset_’, ‘’ )
LEFT JOIN act_re_procdef rp ON re.PROC_DEF_ID_ = rp.id_
LEFT JOIN sys_user u1 ON u1.user_name = a.create_by
LEFT JOIN sys_user u2 ON u2.user_id = rt.ASSIGNEE_
WHERE
a.process_run_id IS NOT NULL GROUP BY a.bills_id

参考资料

参考资料:https://www.cnblogs.com/chancy/p/10026097.html