2020-09-08

MySQL语句改Oracle语句

1.DATE_FORMAT函数（将日期转换成字符串）

DATE_FORMAT( MIN( WARNING_DATE ), ‘%Y%m%d’ ) MIN
to_char( MIN( WARNING_DATE ), ‘yyyy-MM-dd’ ) MIN

2.Orcale执行语句 出现错误“ORA-01791: 不是 SELECTed 表达式”

原因：DISTINCT字段和orderby共用时，order by中的字段不是查询出来的字段就会报错，可在外套一层表

SELECT
	DISTINCT PUSH_VERSION,
	POLICY_TITLE,
	ATTACHMENT_FILE_NAME,
	ATTACHMENT_CONTENT AS attachmentFileId,
	SEND_TIME
FROM
	edcip_tax_policy_push
ORDER BY
	ID DESC

改为

SELECT
PUSH_VERSION,
	POLICY_TITLE,
	ATTACHMENT_FILE_NAME,
	attachmentFileId,
	SEND_TIME
FROM
(SELECT
	DISTINCT PUSH_VERSION,
	ID,
	POLICY_TITLE,
	ATTACHMENT_FILE_NAME,
	ATTACHMENT_CONTENT AS attachmentFileId,
	SEND_TIME
FROM
	edcip_tax_policy_push
ORDER BY
	ID DESC) c

3.limit语句（此处注意不可在where里直接添加rownum<=100，会先取前一百个再排序）

SELECT
	MIN( a.trade_co ) trade_co,
	a.social_credit_code social_credit_code,
	MIN( a.trade_name ) trade_name,
	MIN( SUBSTR( a.the_month, 0, 4 )) the_month,
	SUM( NVL( a.total_tax_amt, 0 )) total_tax_amt
FROM
	od_ch_cop_summary_sd a
LEFT JOIN enterprise_customs b ON
		a.social_credit_code = b.uni_code
	WHERE
		SUBSTR( a.the_month, 0, 4 ) BETWEEN 1 AND 1
	GROUP BY
		social_credit_code
	ORDER BY
		total_tax_amt
	limit 0,100

改为

SELECT trade_co,social_credit_code,trade_name,the_month,total_tax_amt FROM(
SELECT
	MIN( a.trade_co ) trade_co,
	a.social_credit_code social_credit_code,
	MIN( a.trade_name ) trade_name,
	MIN( SUBSTR( a.the_month, 0, 4 )) the_month,
	SUM( NVL( a.total_tax_amt, 0 )) total_tax_amt
FROM
	od_ch_cop_summary_sd a
LEFT JOIN enterprise_customs b ON
		a.social_credit_code = b.uni_code
	WHERE
		SUBSTR( a.the_month, 0, 4 ) BETWEEN 1 AND 1
	GROUP BY
		social_credit_code
	ORDER BY
		total_tax_amt) 
		WHERE rownum <=100

4.if函数

(Mysql)IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值

(Oracle)NVL2函数，和Mysql的IF函数效果一样，但是查询SQL

// mysql语法
SELECT
	SUM( IF(  a.i_e_flag = 'I'  , NVL( total_tax_amt, 0 ), 0 )) AS importTax,
FROM a

//Oracle语法
SELECT
// SUM( NVL2(   a.i_e_flag = 'I'  , NVL( total_tax_amt, 0 ) ,0 ) 报错，第一个参数这样子取不到值
//NULLIF函数，相等为null，将后两个参数调换即可实现IF功能
	SUM( NVL2( NULLIF( a.i_e_flag, 'I' ) , 0 , NVL( total_tax_amt, 0 ) )))),
FROM a