【杂】sql学习

1. 当部分资料未按照原有格式进行编写，需要手动合并时
   合并公式 : H4&“(”&I4&“)”
   示例 :
   
2. 查询锁表

SELECT object_name, machine, s.sid, s.serial# ,o.owner
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid; 

3. 将锁表解锁

alter system kill session 'sid,serial#';

4. 查询是否有特殊符号

SELECT * FROM 表 WHERE NOT REGEXP_LIKE(字段,'^[0-9]+$')
regexp_replace('LVGBV87E3GG2547','\D*','') as b,        -- 剔除非数字
regexp_replace('LVGBV87E3GG2547','[a-zA-Z]*','') as c,  -- 剔除字母
regexp_replace('LVGBV87E3GG2547','[0-9]+','') as d      --剔除数字

5. 查询某表中有特殊字符的数量

select DEALERCODE,COUNT(spe_char) 
       from(select PARTSCODE,
                   DEALERCODE,
                   regexp_replace(PARTSCODE,'[^!@#$%&*()+=:;",.?\|]','') spe_char
             from  A ) t
 where  t.spe_char is not null
 GROUP BY DEALERCODE

6. 查询某表中有空格的数量

select DEALERCODE,count(1) from A
where PARTSCODE like ' %'
group by DEALERCODE

7. 查询某表中同一LOCANO>1的数量

SELECT T.DEALERCODE, COUNT(1)
  FROM (SELECT DEALERCODE, LOCANO, COUNT(1)
          FROM A
          WHERE TRIM(LOCANO) IS NOT NULL
         GROUP BY DEALERCODE, LOCANO
        HAVING COUNT(1) > 1
        ORDER BY DEALERCODE DESC) T
 WHERE T.LOCANO IS NOT NULL
 GROUP BY T.DEALERCODE

8. 修复脚本

MERGE INTO A表 X
    USING (SELECT DISTINCT TO_CHAR(A.ID) AS ID,C.QTY
    FROM A表 A
    JOIN C表 C
      ON A.NO = TRIM(C.CODE)
    ) Y ON (X.ID=Y.ID)
    WHEN MATCHED THEN 
        UPDATE SET X.QTY = Y.QTY;
COMMIT;

9. 获取修复数据语句

SELECT 
'UPDATE A表 SET QTY = ''' || QTY || ''' WHERE ID = ''' || ID || ''';'
FROM A表
--数据为数值类型时
SELECT 
'UPDATE A表 SET QTY = ' || QTY || ' WHERE ID = ''' || ID || ''';'
FROM A表

10. 迁移时部分数据操作

--迁移时数据需要转换
DECODE(TYPE,0,1,1,0,2) --0转为1,1转为0,其余转为2
TO_NUMBER(TRIM(ID)) --将char或varchar2类型的string转换为一个number类型的数值
--迁移时数据需要去空格
TRIM(TYPE)--去字段全部空格
LTRIM(TYPE)--去左空格
RTRIM(TYPE)--去右空格
--迁移时需要查询不包含另一张表内数据
SELECT * FROM T1表
 WHERE NOT EXISTS (SELECT 1
          FROM T2表 T2
         WHERE T2.NO = TRIM(T1.NO)
           AND T2.ORDERNO=TRIM(T1.ORDERNO))
           AND T1.STATUS='1'
--日期转换
TO_DATE(TDATE,'yyyy-mm-dd hh24:mi:ss') --将TDATE转为DATE的数据类型
TO_CHAR(TDATE,'yyyymmdd') --将一个日期转换为VARCHAR2数据类型
--拼接函数
CONCAT(CONCAT(TO_CHAR(SYSDATE,'yyyymmdd'),'00'),lpad(rownum,8,'0'))--8位日期+10位流水样式：202109070000000001
--若数据为空则将此数据赋值6
NVL(DECODE(TRIM(status),'0','6','1','3'),'6')
--将Ages=KM的数据替换成1
replace(Ages,'KM','1')
--查询UID为中文的数据
SELECT * FROM G表
 WHERE regexp_instr(UID,'[' || unistr('\4e00') || '-' || unistr('\9fa5') || ']') = 1
 --获取当前时间
 SYSDATE
 --ORDERNO第一位为S则转1,为P则转2
 DECODE(SUBSTR(TRIM(ORDERNO),1,1),'S','1','P','2')

11. 清空表数据

TRUNCATE TABLE 表名

12. 固化脚本

--将所有A表数据复制进B表中
DROP TABLE B--固化脚本名;
CREATE TABLE B AS
SELECT *
FROM A;
GRANT SELECT ON B TO BBASE; --将此表查询权限授权给另一数据库
CREATE INDEX GH1 ON B(ORDERNO) TABLESPACE TACTINDEX01; --创建索引

13. 固化脚本(例固化已结算与未结算的数据)

DROP TABLE S;
CREATE TABLE S AS
SELECT * FROM A表 A
WHERE  EXISTS ( SELECT 1 FROM 
   (SELECT C.DNO, C.DCODE, D.WNO
          FROM (SELECT B.WNO, A.DCODE, A.DNO
                  FROM A表 A  --申请主表
                  JOIN B表 B  ---结算明细表
                    ON A.DCODE = B.DCODE
                   AND A.DNO = B.DNO) C --通过结算明细表关联到的单子
           JOIN D表 D ---结算主表
            ON C.WNO = D.WNO AND C.DCODE=D.DCODE
         AND D.CTE IS  NULL) E  ---未结算的单子
    WHERE A.DCODE = E.DCODE
   AND A.DNO = E.DNO) 
AND A.WTYPE='类型'  UNION ALL
SELECT A.*
FROM A表 A
JOIN   G表 G ---工单
    ON A.ORDERNO = G.ORDERNO  
  WHERE G.TTYPE IN ('M') AND NOT EXISTS ( SELECT 1 FROM 
   (SELECT C.DNO, C.DCODE, D.WNO
          FROM (SELECT B.WNO, A.DCODE, A.DNO
                  FROM A表 A  --申请主表
                  JOIN B表 B  ---结算明细表
                    ON A.DCODE = B.DCODE
                   AND A.DNO = B.DNO) C --结算明细表关联到的单子
           JOIN D表 D ---结算主表
            ON C.WNO = D.WNO AND C.DCODE=D.DCODE
         AND D.CTE IS  NULL) E  ---未结算的单子
    WHERE A.DCODE = E.DCODE
   AND A.DNO = E.DNO) AND A.WTYPE='类型' ;
GRANT SELECT ON S TO S表;

14. 模糊查询

where id like '%1' --查询id首位任意但后位为1的数据
where id like ' %' --查询首位为空格后位任意的数据
where id like '%1%' --查询数据内有1的数据

15. 优化可参考步骤
    
16. 窗口函数

SELECT *
FROM T1表 T1
LEFT JOIN (SELECT ENO,DFLAG FROM 
(SELECT ROW_NUMBER() OVER(PARTITION BY A.ENO ORDER BY A.CID DESC) RN,
	A.* FROM A表 A)WHERE RN=1) T2--别名 
ON T1.ENO = T2.ENO

17. CASE WHEN 函数

--若ENO不为空或不等于null就将此数据赋值为1,否则赋值为0
CASE WHEN ENO IS NOT NULL AND ENO <> 'null'
THEN '1'
ELSE '0'
END
--例(查询ostatus为2的数量)
decode(
SUM(CASE WHEN ostatus='2' THEN 1 END),null,0)已完成,

18. 查询更新日期的年份,与每家店最大的自增序列

--例1
SELECT substr(b.seq,-4)as seq , b.yyear ,T1.CODE
  FROM T1表 T1
  JOIN (select max(substr(TRIM(T1.TNO), -6)) OVER(PARTITION BY T1.CODE) as seq,--店_年份后两位_四位自增序号
               T1.CODE,
               T1.TNO,
               TO_CHAR(T1.ATIME, 'YYYY') as yyear
          from T1表 T1
         where substr(TRIM(T1.TNO), -4, 1) <> 'D') B
    ON T1.TNO = B.TNO
 WHERE substr(TRIM(T1.TNO), -6) = B.SEQ 
 --例2
 SELECT MAX(B.yyear),MAX(B.seq),T1.CODE
  FROM T1表 T1
  JOIN (select max(substr(TRIM(T1.TNO), -4)) OVER(PARTITION BY T1.CODE) as seq,
               T1.CODE,
               T1.TNO,
               TO_CHAR(T1.ATIME, 'YYYY') as yyear
          from T1表 T1
         where substr(TRIM(T1.TNO), -4, 1) <> 'D') B
    ON T1.TNO = B.TNO
 WHERE substr(TRIM(T1.TNO), -4) = B.SEQ  GROUP BY T1.CODE
 --例3：取最大创建时间的最大序列值
 SELECT MAX(B.SEQ) AS SEQ,substr(MAX(B.yyear),0,4) AS YEAR,T1.DCode AS DCode
FROM T1表 T1
  JOIN (SELECT max(TO_CHAR(T1.ATIME,'YYYYMMDD'))OVER(PARTITION BY T1.DCODE)AS yyear,
               substr(TRIM(T1.DNO), -4)AS SEQ,
               T1.DCode ,
               T1.DNO  
          FROM T1表 T1
          where substr(TRIM(T1.DNO), -4, 1) <> 'D')B
          on T1.DNO=B.DNO 
          WHERE substr(TRIM(T1.DNO), -4) = B.SEQ AND TO_CHAR(T1.ATIME,'YYYYMMDD')= B.yyear
          GROUP BY T1.DCode ;

19. 复制一张表，复制A表的表结构至B表

CREATE TABLE B AS
SELECT *
FROM A 
where 1=1 ; --若条件成立复制表结构与数据，若不成立复制表结构(1=2)

20. 更新语句

update A表 A
join
(
select C.po_no,C.p_no,C.d_code,MAX(p_qty) as p_qty from C表 C
group by C.po_no,C.p_no,C.d_code
)C
on A.po_no = C.po_no
AND A.op_no = C.p_no
AND A.D_CODE = C.d_code

SET A.original_order_qty = C.p_qty