sql - 我想在我尝试过但失败的两列中随机插入表中的数据

Question

我有两张 table pay_in_out我想预先插入数据并从表中随机选择但失败了任何人都可以告诉我

INSERT INTO  ADVANCE_SALARY (EMPLOYEECODE,ADV_DATE)
    VALUES (SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 40
AND ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY');

我也试过这样但失败了

INSERT INTO  ADVANCE_SALARY (EMPLOYEECODE, ADV_DATE)
    SELECT    
     EMPLOYEECODE,
     ADV_DATE
     FROM 
     (SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    WHERE ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY'
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 40;

它说 adb_date不合法的识别符
但选择查询工作正常

SELECT EMP_CODE,ATT_DATE
FROM   (
    SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 40
AND ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY';

Answer 1

如果您对 39 行很灵活，可以考虑使用 SAMPLE ，这更干净，更快:

INSERT INTO ADVANCE_SALARY (EMPLOYEECODE, ADV_DATE)
SELECT EMP_CODE, ATT_DATE
  FROM pay_in_out SAMPLE (5)
 WHERE ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
   AND ((TO_CHAR(ATT_DATE, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH'))) != 'SUNDAY';

您需要根据数据量调整样本大小(此处为 5%)。