数据库语法

拼接

select concat(first_name,last_name ) from tableA

是否包含

验证 A B
字段a 字段b
字段a,b
不能同时存在的验证

FIND_IN_SET(#{fuseorgid.companyCode},a.fuseorgid) > 0
			or FIND_IN_SET(a.fuseorgid,#{fuseorgid.companyCode}) > 0

数据库保存的字段 是否包含参数companyCode

FIND_IN_SET ( C.COMPANY_CODE, #{companyCode} ) > 0

SELECT FIND_IN_SET(‘b’, ‘a,b,c,d’); --结果 2，但是不可以判断是否包含","
instr(‘b’, ‘a,b,c,d’) --结果 2，可以判断是否包含","
instr(‘helloworld’,‘l’) --默认I第一次出现的位置

不同数据库链接的连查

SELECT
 C.FNAME,
 C.ID,
 H.FNAME,
 H.ID 
FROM
 YG_BS_SUPPLIER C,
 YG_BS_SUPPLIER@HX H 
WHERE
 c.STATUS = 0 and h.STATUS = 0
 and C.FNAME = H.FNAME
 AND C.ID != H.ID

虚拟表

WITH AA AS (),
BB AS ()
select* from AA......

entry.fqty>0 1
entry.fqty=0 0
entry.fqty<0 -1

sign( entry.fqty ) 

多表更新

UPDATE YG_PR_TRANSFERINENTRY 
SET YG_PR_TRANSFERINENTRY.FSUPPLYID = (
	SELECT
		AA.confsupplyid 
	FROM
		(
		SELECT 
		DISTINCT a.id AS entid,
			a.fsupplyid,
			con.id,
			con.fsupplyid AS confsupplyid 
		FROM
			YG_PR_TRANSFERINENTRY a
			LEFT JOIN YG_PUR_CONTRACT_MAT mat ON mat.FPACKNO = a.FPACKNO
			LEFT JOIN YG_PUR_CONTRACT con ON mat.FPARENTID = con.id 
		WHERE a.FSUPPLYID IS NULL  AND con.fsupplyid IS NOT NULL 
		)  AA 
	WHERE
	AA.entid = YG_PR_TRANSFERINENTRY.id 
	)

查询时直接拼接sql

SELECT   'update YG_PR_PPBOMENTRY set FDEVIATIONQTY ='||a.FDEVIATIONQTY||' where id ='''||nn.id||''';',

获取当天时间

to_char(sysdate,yyyy-mm-dd)

分组后取每个分组内的第一条

SELECT ROW_NUMBER() OVER(PARTITION BY pp.FPACKNO ORDER BY pp.CREATE_DATE DESC) rn,  pp.* FROM YG_PR_PPBOMENTRY pp

	SELECT *       
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY pp.FPACKNO ORDER BY pp.CREATE_DATE DESC) rn,  pp.* FROM YG_PR_PPBOMENTRY pp)       
   WHERE rn = 1