sql语句
select af.id id,af.aftersaleapplication_id aftersaleapplicationId,
af.time time,af.sysuser_id sysuserId,af.status status,af.description description,
af.last_type lastType,GROUP_CONCAT(w.name separator ',') role,w.nickname sysUser
from
( select
r.name name,su.id uid,su.nickname nickname
from role r,sysuser su,sysuser_role sr where
r.id=sr.role_id and
sr.user_id = su.id and su.id in (select sysuser_id
from aftersaleprocess)
) w
left join aftersaleprocess af
on w.uid=af.sysuser_id WHERE af.aftersaleapplication_id=21 group by af.id
order by time desc
查询结果如下
下面这个是查询总条数
select count(id) from (select af.id id,af.aftersaleapplication_id aftersaleapplicationId,
af.time time,af.sysuser_id sysuserId,af.status status,af.description description,
af.last_type lastType,GROUP_CONCAT(w.name separator ',') role,w.nickname sysUser
from
( select
r.name name,su.id uid,su.nickname nickname
from role r,sysuser su,sysuser_role sr where
r.id=sr.role_id and
sr.user_id = su.id and su.id in (select sysuser_id
from aftersaleprocess)
) w
left join aftersaleprocess af
on w.uid=af.sysuser_id WHERE af.aftersaleapplication_id=21 group by af.id) sd