select ttt.ORG_NAME as "客户",
to_char(ttt.create_Date_,'yyyy-MM-dd') as "入驻时间",
ttt.USERNAME_ as "账号",
ttt.ORG_CONTACT_NAME as "负责人",
to_char(uuu.maxCreateDate,'yyyy-MM-dd') as "最后一次使用时间"
from
(select o.ORG_NAME,
NVL(u.CREATE_DATE_,(select t.CREATE_DATE from CORE_USER_PASSWORD_UPDATE t where t.USERNAME_ = u.USERNAME_)) as create_Date_,
u.USERNAME_,
o.ORG_CONTACT_NAME
from BIZ_ORG o left join BDF2_USER_DEPT d on o.ORGANIZINGCODE = d.DEPT_ID_ left join BDF2_USER u on d.USERNAME_ = u.USERNAME_ ) ttt
left join
(select tt.CREATE_USER,MAX(tt.CREATE_DATE) maxCreateDate,count(tt.CREATE_DATE) countCreateDate
from BIZ_ORDER_MASTER tt
where tt.CREATE_USER in (select u.USERNAME_ from BDF2_USER u) GROUP BY tt.CREATE_USER) uuu on ttt.USERNAME_ = uuu.CREATE_USER
order by ttt.ORG_NAME desc,uuu.maxCreateDate desc;
to_char(ttt.create_Date_,'yyyy-MM-dd') as "入驻时间",
ttt.USERNAME_ as "账号",
ttt.ORG_CONTACT_NAME as "负责人",
to_char(uuu.maxCreateDate,'yyyy-MM-dd') as "最后一次使用时间"
from
(select o.ORG_NAME,
NVL(u.CREATE_DATE_,(select t.CREATE_DATE from CORE_USER_PASSWORD_UPDATE t where t.USERNAME_ = u.USERNAME_)) as create_Date_,
u.USERNAME_,
o.ORG_CONTACT_NAME
from BIZ_ORG o left join BDF2_USER_DEPT d on o.ORGANIZINGCODE = d.DEPT_ID_ left join BDF2_USER u on d.USERNAME_ = u.USERNAME_ ) ttt
left join
(select tt.CREATE_USER,MAX(tt.CREATE_DATE) maxCreateDate,count(tt.CREATE_DATE) countCreateDate
from BIZ_ORDER_MASTER tt
where tt.CREATE_USER in (select u.USERNAME_ from BDF2_USER u) GROUP BY tt.CREATE_USER) uuu on ttt.USERNAME_ = uuu.CREATE_USER
order by ttt.ORG_NAME desc,uuu.maxCreateDate desc;