如何用子查询更新列？员工与部门表更新SQL语句求助

嘿，我来帮你搞定这个部门员工数更新的问题！

首先咱们先看看你原来的语句为啥没成功：你在子查询里加了group by e.dept_id，这其实是多余的——因为where条件已经把部门ID关联起来了，每个部门只会返回一个统计值。但更关键的是，如果某个部门在EMPLOYEE表里没有对应记录（或者像你这里的部门2，原来DEPARTMENT表里根本没有这个部门），子查询会返回null，而不是你想要的员工数，这就导致更新失败或者结果不对。

下面分不同数据库给你对应的解决方案，既能更新现有部门的counts，还能自动把EMPLOYEE里存在但DEPARTMENT里没有的部门加进去，正好符合你想要的最终结果：

MySQL 解决方案 #

先确保DEPT_ID是主键或者唯一键，然后用INSERT ... ON DUPLICATE KEY UPDATE一键搞定更新+插入：

INSERT INTO department (dept_id, counts)
SELECT dept_id, COUNT(*) AS counts
FROM employee
GROUP BY dept_id
ON DUPLICATE KEY UPDATE counts = VALUES(counts);

这个语句会先统计每个部门的员工数，然后往DEPARTMENT表里插：如果部门已经存在，就更新counts为最新统计值；如果不存在，直接插入新行。

Oracle/SQL Server 解决方案 #

用MERGE语句来实现“匹配就更新，不匹配就插入”：

MERGE INTO department d
USING (
    SELECT dept_id, COUNT(*) AS counts
    FROM employee
    GROUP BY dept_id
) e
ON (d.dept_id = e.dept_id)
WHEN MATCHED THEN
    UPDATE SET d.counts = e.counts
WHEN NOT MATCHED THEN
    INSERT (dept_id, counts)
    VALUES (e.dept_id, e.counts);

如果你只是想更新DEPARTMENT里已有的部门，不想加新部门，可以用下面的语句（以MySQL为例，其他数据库把COALESCE换成ISNULL就行）：

UPDATE department p
SET p.counts = COALESCE(
    (SELECT COUNT(*) FROM employee e WHERE e.dept_id = p.dept_id),
    0
);

这里用COALESCE把null转换成0，避免counts变成空值。

内容的提问来源于stack exchange，提问作者Neha