SQL排序表达式

ORACLE 排序表达

WITH ts AS ( 
SELECT row_number ( ) over ( PARTITION BY catalog_id ORDER BY rank_order ) row_Num, inf.* 
FROM RPT_AR_FR_REPORT_INFO inf ) 

SELECT
t.row_num 
FROM
	ts t 
WHERE
	t.rpt_id = info.rpt_id

MYSQL 排序表达

SELECT
		t.row_num
		FROM
		(SELECT
		b.*,
		IF
		( @p = catalog_id, @rownum := @rownum + 1, @rownum := 1 ) row_num,
		@p := catalog_id
		FROM
		( SELECT @p := NULL, @rownum := 0 ) rownum,
		RPT_AR_FR_REPORT_INFO b
		ORDER BY
		catalog_id,
		rank_order) t
		WHERE
		t.rpt_id = info.rpt_id

ORACLE 行转列

SELECT
	* 
FROM
	RPT_AR_FR_REPORT_CATALOG t START WITH t.catalog_id = : nodeId connect BY prior t.up_catalog_id = t.catalog_id

MYSQL 行转列

SELECT
	* 
FROM
	(
SELECT
	m.*,
	group_concat( m.catalog_nm SEPARATOR <![ CDATA [ '<-' ]]> ) catalog_path 
FROM
	( SELECT * FROM RPT_AR_FR_REPORT_CATALOG t ) m 
GROUP BY
	m.order_no 
ORDER BY
	m.order_no 
	) t 
WHERE
	t.catalog_id = #{catalogId}