[Sphinx] 스핑크스를 이용한 인사이동 현황 쿼리

select 

a.regDate,

a.compName,

a.empName,

    a.workStatus,

    cc.nextDeptName, // 바꾼부서

    cc.nextDutyName,

    cc.nextPositionName,

    a.previousDeptName, // 이전부서

    a.previousDutyName,

    a.previousPositionName

from

(SELECT 

@rownum:=@rownum + 1 as rownum,

    regDate,

    compName,

    empName,

    workStatus,

    previousDeptName,

    previousDutyName,

    previousPositionName,

    mainDeptYn

FROM

movePersonnelStatus_Y,

                (select @rownum:=0) tmp

WHERE

    `query` = '"*유천혜*";limit=100050;mode=boolean;maxmatches=100050;') a left outer join

                ( select bb.previousDeptName as nextDeptName, bb.previousDutyName as nextDutyName, bb.previousPositionName as nextPositionName, bb.rownum2 from

(SELECT 

@rownum2:=@rownum2 + 1 as rownum2,

regDate,

compName,

empName,

workStatus,

previousDeptName,

previousDutyName,

previousPositionName,

mainDeptYn

FROM

movePersonnelStatus_Y,

(select @rownum2:=0) tmp

where

`query` = '"*유천혜*";limit=100050;mode=boolean;maxmatches=100050;'

) bb

) cc on cc.rownum2 = a.rownum + 1;

부서를 변경할때 기록을 남기기위한 쿼리