MYSQL - 根据另外两个连接的表更新一个表中的计数

Question

我有 3 个相关表。成人、 child 和空调。 Adults 包含一个 INT 列来统计高中生的数量。 Children 包含一个包含高中毕业年份的列。 AC 将adult.id 链接到children.id。

CREATE TABLE adults (
  id INT,
  name VARCHAR(10),
  seniors INT DEFAULT 0
) ;

INSERT INTO adults (id, name) VALUES
(1, 'adam'),
(2, 'bob');

CREATE TABLE children (
  id INT,
  name VARCHAR(10),
  grad VARCHAR(4)
) ;

INSERT INTO children (id, name, grad) VALUES
(1, 'sally', '2016'),
(2, 'johnny', '2017'),
(3, 'eric', '2016'),
(4, 'billy', '2016'),
(5, 'rachel', '2016');

CREATE TABLE pc (
  id INT,
  a_id INT,
  c_id INT
) ;

INSERT INTO pc (id, a_id, c_id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 3),
(5, 2, 2);

SQLFiddle:http://sqlfiddle.com/#!2/89281e

所以我想将adults.seniors 更新为与他们关联的“2016” child 的计数。因此，成人#1 为“2”(莎莉和埃里克)，成人#2 为“1”(埃里克)。

真实数据将在 25,000 多个 child 中运行，与 40,000 多个家长相匹配，“pc”表上的行数超过 3,000,000 行 - 因此需要提高效率。我开始沿着这条路工作，但是a)由于明显的原因它不起作用，b)我怀疑它是否有效......

UPDATE adults a SET
    seniors = (
       SELECT p.a_id, count(*)
       FROM pc p
       INNER JOIN children c ON c.id = p.c_id
       WHERE c.grad = '2016'
       GROUP BY p.c_id)
   WHERE p.a_id = a.id;

我认为必须有一种更好的方法来通过连接来做到这一点，但我似乎无法理解它。

Answer 1

您应该寻找此更新声明:

UPDATE adults a
        JOIN
    (SELECT 
        p.a_id, COUNT(*) childrencount
    FROM
        pc p
    INNER JOIN children c ON c.id = p.c_id
    WHERE
        c.grad = '2016'
    GROUP BY p.a_id) c ON (a.id = c.a_id)
SET 
    seniors = c.childrencount;