拼接
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