Oracle 常用sql

一、LISTAGG函數

SELECT DISTINCT PRODUCT_INFO_PACKAGE_NAME FROM PRODUCT_INFO_PACKAGE;
           

 執行結果如下

 當某些場景需要把查詢結果按逗号分割時，sql需如下

SELECT
	LISTAGG (
		PIP.PRODUCT_INFO_PACKAGE_NAME,
		','
	) WITHIN GROUP (
		ORDER BY
			PIP.PRODUCT_INFO_PACKAGE_NAME
	)
FROM (SELECT DISTINCT PRODUCT_INFO_PACKAGE_NAME FROM PRODUCT_INFO_PACKAGE) PIP;
SELECT DISTINCT PRODUCT_INFO_PACKAGE_NAME FROM PRODUCT_INFO_PACKAGE;
           

執行如下

二、ROW_NUMBER () OVER ()函數

SELECT
	prd_cont_id,
	CREATE_DATE
FROM
	PUBLISH_LOG
WHERE
	PRD_CONT_ID IN (
		100000015,
		100000016,
		100000017
	)
ORDER BY
	PRD_CONT_ID;
           

執行結果如下，有些都有多條資料

但某些場景，我們隻需要最後的一條或最前的一條時，sql如下

SELECT
	prd_cont_id,
	CREATE_DATE
FROM
	(
		SELECT
			prd_cont_id,
			CREATE_DATE,
			ROW_NUMBER () OVER (
				PARTITION BY prd_cont_id
				ORDER BY
					CREATE_DATE
			) AS row_flg
		FROM
			PUBLISH_LOG
	)
WHERE
	PRD_CONT_ID IN (
		100000015,
		100000016,
		100000017
	)
AND row_flg = 1;
           

執行結果如下

三、wmsys.wm_concat()函數

SELECT content_id,DP_FILE_PATH FROM DISPLAY_FILE where CONTENT_ID in 
(1000000048,1000000037,1000000018) ORDER BY CONTENT_ID;
           

執行結果如下

某些場景下需要把查詢到的結果合并為一行時，sql如下

SELECT
	content_id,
	wmsys.wm_concat (DP_FILE_PATH) AS result
FROM
	DISPLAY_FILE
WHERE
	CONTENT_ID IN (1000000048,1000000037,1000000018)
GROUP BY
	content_id;
           

執行結果如下，會把查詢到的結果按照逗号分割合并為一行

四、in和EXISTS

select * from prd_cont where prd_info_id in(select prdinfoid from PRD_INFO where 
name like '%貨架同步%');

select * from prd_cont pc where EXISTS (select 1 from PRD_INFO pi where PC.PRD_INFO_ID=pi.prdinfoid 
and pi.name like '%貨架同步%');
           

執行結果如下