联表+分页+条件查询+排序+case when的使用 sql

SELECT
*
FROM
(
SELECT
TMP_PAGE.*,
ROWNUM ROW_ID
FROM
(
SELECT
z.BOX_ID AS BOX_ID,
( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.BOX_CODE ELSE z.BOX_CODE END ) AS BOX_CODE,
( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.CONTROL_WAY ELSE z.CONTROL_WAY END ) AS CONTROL_WAY,
( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.EXTERELC ELSE z.EXTERELC END ) AS EXTERELC,
( CASE WHEN p.ZC_UUID IS NOT NULL THEN p.IS_SHARE ELSE z.IS_SHARE END ) AS IS_SHARE,
( CASE WHEN p.DELFLAG = ‘1‘ THEN ‘2‘ WHEN p.ZC_UUID IS NOT NULL THEN ‘1‘ ELSE ‘0‘ END ) AS MEMO,
(
CASE
WHEN p.UPDATE_TIME IS NOT NULL THEN
p.UPDATE_TIME
WHEN z.UPDATE_TIME IS NOT NULL THEN
z.UPDATE_TIME
WHEN z.CREATE_TIME IS NOT NULL THEN
z.CREATE_TIME
when  z.CREATE_TIME is null then
to_date(‘0001-01-01‘,‘yyyy-MM-dd‘)
END
) AS time
FROM
USR_ZC.T_ZC_BOX z
LEFT JOIN USR_ZC.T_PC_BOX p ON z.BOX_ID = p.ZC_UUID
WHERE
1 = 1
ORDER BY
time DESC
) TMP_PAGE
WHERE
ROWNUM <= 12 ) WHERE ROW_ID > 0;