light-apiengine-feature/sqlconfig/system_controller/system_find_user_page_select.tpl

select
sys_user.*,
sys_org.name org_name,
sys_org.id org_id,
IFNULL(GROUP_CONCAT(distinct sys_role.name), '') role_name,
IFNULL(GROUP_CONCAT(distinct sys_role.id), '') user_role
from
(
 select sys_user.id
 from sys_user
 where sys_user.del_flag = 0
 {{if ne .name ""}}
  and sys_user.name like '%{{.name}}%'
 {{end}}
 {{if ne .login_id ""}}
  and sys_user.login_id like '%{{.login_id}}%'
 {{end}}
 order by sys_user.create_time desc
 LIMIT {{.rows}} OFFSET {{.offset}}
) a
join sys_user on sys_user.id=a.id
left join sys_org on sys_user.org_id = sys_org.id
left join sys_user_role on sys_user_role.user_id = sys_user.id
left join sys_role on sys_role.id = sys_user_role.role_id
GROUP BY sys_user.id
order by sys_user.create_time desc