查询语句:
select * from (
select t_album_page.*, ROWNUM rn from (
select t_album_final.* from V_ALBUM t_album_final where t_album_final.ID in(
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84')
)
) order by DECADE desc
) t_album_page
) where rn >= 1 and rn <= 2147483647以上语句结果某些该有值的字段变成了空,但直接执行内层的子查询没问题。
更奇怪的是下面的语句没问题,只是内层多了join语句。
select * from (
select t_album_page.*, ROWNUM rn from (
select t_album_final.* from V_ALBUM t_album_final where t_album_final.ID in (
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84') and ( t_album.TITLE in ( '321','魔戒三(王者归来)','魔戒一(双塔)') or t_keywords.KEYWORD in ( '321') )
)
) order by DECADE desc
) t_album_page
) where rn >= 1 and rn <= 2147483647
select * from (
select t_album_page.*, ROWNUM rn from (
select t_album_final.* from V_ALBUM t_album_final where t_album_final.ID in(
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84')
)
) order by DECADE desc
) t_album_page
) where rn >= 1 and rn <= 2147483647以上语句结果某些该有值的字段变成了空,但直接执行内层的子查询没问题。
更奇怪的是下面的语句没问题,只是内层多了join语句。
select * from (
select t_album_page.*, ROWNUM rn from (
select t_album_final.* from V_ALBUM t_album_final where t_album_final.ID in (
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84') and ( t_album.TITLE in ( '321','魔戒三(王者归来)','魔戒一(双塔)') or t_keywords.KEYWORD in ( '321') )
)
) order by DECADE desc
) t_album_page
) where rn >= 1 and rn <= 2147483647
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84')
and ( t_album.TITLE in ( '321','魔戒三(王者归来)','魔戒一(双塔)') or t_keywords.KEYWORD in ( '321') )
)
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84')
)。
这个子查询单独运行结果也正确
select t_album_page.* from (
select t_album_final.*,rownum rn from V_ALBUM t_album_final where t_album_final.ID in(
select distinct ID from (
select t_album.* from V_ALBUM t_album
left join V_KEYWORDS t_keywords on t_album.ID = t_keywords.ALBUMID
where PARENT_ALBUM_ID is null and ALBUM_STATUS = 3 and t_album.COLUMN_CODE in ( '679a35bff1d34ccbacea86119bbe2bd7','ef2714538c284cb181d86d0a40d5e119','d1986d9e748c4579a0572b2c51dba1c4','a8c6572924aa4480ae5c40b5dd3a8a84')
)
) order by DECADE desc
) t_album_page
where rn >= 1 and rn <= 2147483647