[mariadb] 행번호 매기기

사용법은 다양하지만 단순 행번호는 아래와 같이 사용


ROW_NUMBER() OVER(ORDER BY REGISTER_DATE ASC) AS ROWNUM


계정 정보를 가져오는 예제 쿼리 (mybatis)


SELECT
T1.ACCOUNT_ID
,T1.COMPANY_ID
,T1.ACCOUNT
,T1.ACCOUNT_TYPE_ID
,T1.ACCOUNT_NAME
,T1.ACCOUNT_TEL
,T1.ACCOUNT_EMAIL
,T1.MOD_ACCOUNT_ID
,IFNULL( T1.LOGIN_IP , '-') AS LOGIN_IP
,T1.LOGIN_DEVICE_TYPE
,T1.REGISTER_DATE
,T1.UPDATE_DATE
,T1.USE_YN
,IFNULL( T2.COMPANY_NAME , '-') AS COMPANY_NAME
,T3.ACCOUNT_TYPE_CODE
,T3.ACCOUNT_TYPE_NAME
,T1.ROWNUM
FROM (
SELECT
ACCOUNT_ID
,COMPANY_ID
,ACCOUNT
,ACCOUNT_TYPE_ID
,ACCOUNT_NAME
,ACCOUNT_TEL
,ACCOUNT_EMAIL
,MOD_ACCOUNT_ID
,LOGIN_IP
,LOGIN_DEVICE_TYPE
,REGISTER_DATE
,UPDATE_DATE
,USE_YN
,ROW_NUMBER() OVER(ORDER BY REGISTER_DATE ASC) AS ROWNUM
FROM ACCOUNT
WHERE 1=1
<if test='accountTypeId != null and accountTypeId != 0'>
AND ACCOUNT_TYPE_ID = #{accountTypeId}
</if>
<if test='startDate != null and startDate != ""'>
AND REGISTER_DATE <![CDATA[>=]]> #{startDate}
</if>
<if test='endDate != null and endDate != ""'>
AND REGISTER_DATE <![CDATA[<]]> #{endDate}
</if>
<if test='useYn != null and useYn != ""'>
AND USE_YN = #{useYn}
</if>
<if test='searchText != null and searchText != ""'>
AND (
ACCOUNT LIKE CONCAT('%', #{searchText}, '%')
OR ACCOUNT_NAME LIKE CONCAT('%', #{searchText}, '%')
OR ACCOUNT_TEL LIKE CONCAT('%', #{searchText}, '%')
)
</if>
<if test="companyId != null and companyId != 0">
AND COMPANY_ID = #{companyId}
</if>
ORDER BY ROWNUM DESC
LIMIT #{start}, #{end}
) T1
LEFT OUTER JOIN COMPANY T2 ON T1.COMPANY_ID = T2.COMPANY_ID
LEFT OUTER JOIN ACCOUNT_TYPE T3 ON T1.ACCOUNT_TYPE_ID = T3.ACCOUNT_TYPE_ID