orcale 三张表连接查询

select t.eid, t.opNum, t.opTime, t1.LOCATION_FULL_NAME, t2.province_name
from (select eid, count(*) opNum, to_char(op_time, 'yyyy') opTime
      from ia_loginpr
      group by eid, to_char(op_time, 'yyyy')) t
       left join (select eid, substr(LOCATION_CODE, 0, 2) LOCATION_CODE, LOCATION_FULL_NAME from DW_LC_EID) t1
         on lower(t.eid) = t1.eid
       join (select t3.code province_code, t3.name province_name
             from dw_dim_location_row t3
             where t3.dict_location_level_value = 6500010000000002) t2
         on substr(t1.LOCATION_CODE, 0, 2) = t2.province_code