删除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