oreacl相关

oreacl相关

1.分页查询

    SELECT * FROM (
	SELECT ROWNUM no ,T.* FROM (
			SELECT * from KB_ARTICLE_MOULD_CONTENT
	) T
    ) WHERE no >= 1 and no <=2;

2.行转列

    SELECT AMT.MOULD_ID,
    LISTAGG(AMT.TITLE_NAME, ',') WITHIN GROUP(ORDER BY AMT.MOULD_ID) TITLE_NAME 
    FROM KB_ARTICLE_MOULD_CONTENT AMT 
    WHERE AMT.DEFLAG = 0 
    GROUP BY AMT.MOULD_ID;

3.递归查询

    SELECT LEVEL LEVEL_NUM, T.CAT_ID ,SYS_CONNECT_BY_PATH(T.CAT_NAME, '/') CAT_NAME FROM KB_CATEGORY T
    START WITH T.PARENT_ID = 'f1141cfffffcba6a096a80d10fc6acf9'
    CONNECT BY PRIOR T.CAT_ID  = T.PARENT_ID;