mysql - 更改表以添加使用 UPDATE JOIN 查询设置值的列

Question

我正在尝试创建一个列，该列的值是通过 UPDATE JOIN SET 语句定义的。我实际上想回答的确切问题是

“向关系表 EMPLOYEE 添加有关每个员工处理的订单总数的信息。请注意，如果员工没有处理任何订单，则该员工的订单总数必须设置为零。强制执行适当的一致性关系表 EMPLOYEE 上的约束。”

    ALTER TABLE EMPLOYEE
    ADD TOTALNUMBER VARCHAR(40) NOT NULL;

    UPDATE EMPLOYEE E JOIN ORDERS O ON(E.EMPLOYEE_ID = O.EMPLOYEE_ID)
    SET E.TOTALNUMBER = E.EMPLOYEE_ID + O.ORDER_ID;


    UPDATE EMPLOYEE
    SET TOTALNUMBER = 0
    WHERE TOTALNUMBER IS NULL;

正在使用的表

CREATE TABLE EMPLOYEE
(
    EMPLOYEE_ID     DECIMAL(9)  NOT NULL,
    LASTNAME        VARCHAR(20)     NOT NULL,
    FIRSTNAME       VARCHAR(10)     NOT NULL,
    TITLE       VARCHAR(30),
    TITLE_OF_COURTESY   VARCHAR(25),
    BIRTHDATE       DATE,
    HIREDATE        DATE,
    ADDRESS         VARCHAR(60),
    CITY        VARCHAR(15),
    REGION      VARCHAR(15),
    POSTAL_CODE     VARCHAR(10),
    COUNTRY         VARCHAR(15),
    HOME_PHONE      VARCHAR(24),
    EXTENSION       VARCHAR(4),
    PHOTO       VARCHAR(255),
    NOTES       VARCHAR(2000),
    REPORTS_TO      DECIMAL(9),
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
);

CREATE TABLE ORDERS
(
    ORDER_ID        DECIMAL(9)  NOT NULL,
    CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
    EMPLOYEE_ID     DECIMAL(9)  NOT NULL,
    ORDER_DATE      DATE        NOT NULL,
    REQUIRED_DATE   DATE,
    SHIPPED_DATE    DATE,
    SHIP_VIA        VARCHAR(40),
    FREIGHT         DECIMAL(10,2)           DEFAULT 0,
    SHIP_NAME       VARCHAR(40),
    SHIP_ADDRESS    VARCHAR(60),
    SHIP_CITY       VARCHAR(15),
    SHIP_REGION     VARCHAR(15),
    SHIP_POSTAL_CODE    VARCHAR(10),
    SHIP_COUNTRY    VARCHAR(15),
    CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
    CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),  
    CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  
    CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)  
);

我不确定确切的结果应该是什么，但我总共收到 9 行，其值范围在 252 到 296 之间。员工处理这么多订单似乎并不奇怪，但它似乎太小了一个列表。

Answer 1

我不推荐您当前的方法，确定每个员工的订单数量的最佳方法是仅聚合和连接，而不将该数字存储在实际的员工表中。话虽这么说，如果您想继续这样做，请考虑使用此更新查询:

UPDATE EMPLOYEE e
LEFT JOIN
(
    SELECT EMPLOYEE_ID, COUNT(*) AS num_orders
    FROM ORDERS
    GROUP BY EMPLOYEE_ID
) o
    ON e.EMPLOYEE_ID = o.EMPLOYEE_ID
SET TOTALNUMBER = COALESCE(o.num_orders, 0);

或者，您可以使用:

SET TOTALNUMBER = o.num_orders;

然后使用第二次更新将根本没有订单的员工总数清零:

UPDATE EMPLOYEE
SET TOTALNUMBER = 0
WHERE TOTALNUMBER IS NULL;

但请注意，这要求 TOTALNUMBER 列不能可为空。因此，您需要删除 NOT NULL 约束。