mysql根据某个字段去重数据,使用any_value函数和group by函数
简单例子
sql分析
- 原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