mysql - 是什么导致了错误 "Column does not match the value count at row 1"？

Question

SQL 代码:

CREATE TABLE EMPLOYEE_ORDERS (
        TOTAL_HANDLED INT,
        CONSTRAINT PK_TOTAL PRIMARY KEY (TOTAL_HANDLED)
        );

        INSERT INTO EMPLOYEE_ORDERS (
        SELECT COUNT(ORDERS.EMPLOYEE_ID), EMPLOYEE.EMPLOYEE_ID
        FROM ORDERS LEFT JOIN EMPLOYEE
        ON ORDERS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
        GROUP BY EMPLOYEE.EMPLOYEE_ID
        ORDER BY COUNT(ORDERS.EMPLOYEE_ID));

我尝试过:INSERT INTO EMPLOYEE_ORDERS (TOTAL_HANDLED) (SELECT....

我正在尝试返回处理超过 0 个订单的所有员工的 ID，任何建议将不胜感激

Answer 1

您的子查询返回两个值，但表中只有一列。您需要重写子查询以删除 COUNT 值，并在 HAVING 子句中使用它来过滤员工 ID:

INSERT INTO EMPLOYEE_ORDERS
    SELECT EMPLOYEE.EMPLOYEE_ID
    FROM ORDERS 
    LEFT JOIN EMPLOYEE ON ORDERS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
    GROUP BY EMPLOYEE.EMPLOYEE_ID
    HAVING COUNT(ORDERS.EMPLOYEE_ID) > 0

请注意，在子查询中使用 ORDER BY 是没有意义的，因为它不会对插入表中的行产生任何影响。

另请注意，通过将 LEFT JOIN 替换为 JOIN 并交换表的顺序，您将自动仅获取具有 1 个或多个匹配订单的员工，因此允许您简化查询:

INSERT INTO EMPLOYEE_ORDERS
    SELECT DISTINCT EMPLOYEE.EMPLOYEE_ID
    FROM EMPLOYEES
    JOIN ORDERS ON ORDERS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID