oracle实战之ORA-00907: 缺失右括号

SELECT ref.id, com.code, 
	IF(
		ref.check_state=1, 
		(SELECT sc.content FROM str_com_save_content sc WHERE sc.ref_id=ref.id ORDER BY sc.id DESC LIMIT 1),
	 	com.content
	 ) content,
	CASE
	 	WHEN (SELECT COUNT(1) FROM str_com_reference cr WHERE cr.strategy_id=ref.strategy_id AND cr.component_id=ref.component_id) > 0 THEN 0
    	ELSE 1 
    END AS use_state
	FROM str_component_ref ref 
		LEFT JOIN str_directory dir ON ref.directory_id=dir.id, 
	str_component com

sql做了简化，只描述具体问题

• 报错原因分析：
  在子句中使用了order by，会导致缺失右括号的错误

• 解决方案：
  使用join on的方式，将子句拆分。然后用case替换if函数。然后limit 1使用row_number() OVER(PARTITION BY * ORDER BY * ) 替换

SELECT ref.id, com.code,
	CASE
		WHEN ref.check_state=1 THEN scr.CONTENT
        ELSE com.CONTENT
   	END AS content,
    CASE
    	WHEN (SELECT COUNT(1) FROM str_com_reference cr WHERE cr.strategy_id=ref.strategy_id AND
        cr.component_id=ref.component_id) > 0 THEN 0
    	ELSE 1
    END AS use_state
    FROM str_component_ref ref 
    LEFT JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY REF_ID ORDER BY ID DESC) rn, sc.* FROM STR_COM_SAVE_CONTENT sc) scr ON  scr.rn=1 and scr.ref_id=ref.id
    LEFT JOIN str_directory dir ON ref.directory_id=dir.id,
    str_component com