SQL语句常用

 删除importdate是今天的表记录

DELETE FROM RISK_ALERT WHERE IMPORTDATE = (SELECT TOCHAR(SYSDATE,'YYYY-MM-DD')FROM DUAL)

清空表

TRUNCATE TABLE RISK_ALERT

分组查询 #TO_NUMBER的使用  必须要求转成数字的字段全部是数字的string

SELECT 
    CUSTNO,
    SUM(TO_NUMBER(INVESTSUM)),
    SUM(TO_NUMBER(INVESTRATIO)) 
FROM 
    LEGALINFO 
GROUP BY 
    CUSTNO

去重

SELECT
    DISTINCT
    CUSTNO
FROM 
    LEGAL_EXEC_INFO

增加临时列，临时列数值为SELECT后面的值

#增加临时列 列数值为select后面的值
SELECT 1 FROM RISK_RECORD
#无论count(a) a 为何值，都是在计算这张表中的行数即记录数
SELECT COUNT(1) FROM RISK_RECORD
#计算临时列的值乘以表的行数
SELECT SUM(1) FROM RISK_RECORD

SELECT SUM(10) FROM RISK_RECORD

 oracle中显示表定义 mysql是show table

SELECT DBMS_METADATA.GET_DDL('TABLE','WARN_RECORD') FROM DUAL;

 临时表 两张要用逗号连接

WITH NEW_TABLE_1 AS(
    SELECT 
        UUID,
        CUSTNO,
        CUSTNAME
    FROM
        LEGAL_INFO
    WHERE
        IMPORTDATE = (SELECT MAX(IMPORTDATE) FROM RISK_RECORD)
),
NEW_TABLE_2 AS(
    SELECT 
        UUID
    FROM 
        LEGAL_EXEC_INFO
    WHERE
        IMPORTDATE = (SELECT MAX(IMPORTDATE) FROM RISK_RECORD)
)

 START WITH   CONNECT BY PRIOR

 

LISTAGG(A,'B') GROUP C