2张桌子：

Employees
- EmployeeID
- LeadCount

Leads
- leadID
- employeeID

我想通过计算Leads表中具有相同EmployeeID的线索的数量来更新Employees.LeadCount列。

注意：相同的employeeID可能有1个以上的线索，因此我必须执行DISTINCT(SUM(employeeID))。

解决方案

UPDATE Employees SET LeadCount = (
  SELECT Distinct(SUM(employeeID)) FROM Leads WHERE Leads.employeeId = Employees.employeeId
)

UPDATE
    Employees E
SET
    E.LeadCount = (
        SELECT COUNT(L.EmployeeID)
        FROM Leads L
        WHERE L.EmployeeID = E.EmployeeID
    )

从上方进行加固并删除相关的子查询。

// create tmp -> TBL (EmpID, count)

insert into TBL 
   SELECT employeeID COUNT(employeeID) Di
   FROM Leads WHERE Leads.employeeId = Employees.employeeId GROUP BY EmployeeId
UPDATE Employees SET LeadCount = (
  SELECT count FROM TBL WHERE TBL.EmpID = Employees.employeeId
)

// drop TBL

编辑这是"分组依据"而不是"不同"：b(感谢Mark Brackett)

联接对更新(和删除)的作用相同，就像对选择(编辑：在某些流行的RDBMS中，至少*)所做的一样：

UPDATE Employees SET
  LeadCount = Leads.LeadCount
FROM Employee
JOIN (
  SELECT EmployeeId, COUNT(*) as LeadCount 
  FROM Leads 
  GROUP BY EmployeeId
) as Leads ON
  Employee.EmployeeId = Leads.EmployeeId

SUM(DISTINCT EmployeeId)毫无意义，我们只需要一个COUNT(*)。

• 与MySql一样，MS SQL Server支持UPDATE ... FROM和DELETE ... FROM语法，但SQL-92标准不支持。 SQL-92将让我们使用行表达式。我知道DB2支持这种语法，但不确定其他语法。坦白地说，我发现SQL-92版本令人困惑-但是标准和理论上的困惑会认为FROM语法违反了关系理论，并且使用不精确的JOIN子句或者切换RDBMS供应商时可能导致不可预测的结果。

我们正在为数据同步问题做好准备。当Leads表中的行被插入，更新或者删除时，我们需要不断更新Employees.LeadCount列。

最好的解决方案是根本不存储LeadCount列，而是根据需要使用SQL聚合查询重新计算销售线索的数量。这样，它将永远是正确的。

SELECT employeeID, COUNT(leadId) AS LeadCount
FROM Leads
GROUP BY employeeID;

另一种解决方案是在Leads表上为INSERT，UPDATE和DELETE创建触发器，以便始终将Employees.LeadCount列保持为最新状态。例如，使用MySQL触发语法：

CREATE TRIGGER leadIns AFTER INSERT ON Leads
FOR EACH ROW BEGIN
  UPDATE Employees SET LeadCount = LeadCount + 1 WHERE employeeID = NEW.employeeID;
END

CREATE TRIGGER leadIns AFTER UPDATE ON Leads
FOR EACH ROW BEGIN
  UPDATE Employees SET LeadCount = LeadCount - 1 WHERE employeeID = OLD.employeeID;
  UPDATE Employees SET LeadCount = LeadCount + 1 WHERE employeeID = NEW.employeeID;
END

CREATE TRIGGER leadIns AFTER DELETE ON Leads
FOR EACH ROW BEGIN
  UPDATE Employees SET LeadCount = LeadCount - 1 WHERE employeeID = OLD.employeeID;
END

如果使用的是MySQL，则另一个选择是使用多表UPDATE语法。这是SQL的MySQL扩展，不能移植到其他品牌的RDBMS。首先，将所有行中的LeadCount重置为零，然后对Leads表进行联接，并在联接产生的每一行中增加LeadCount。

UPDATE Employees SET LeadCount = 0;
UPDATE Employees AS e JOIN Leads AS l USING (employeeID)
  SET e.LeadCount = e.LeadCount+1;