mysql - 如何在表中迭代两次？

Question

我有一个包含两个字段的表:id1、id2，我需要根据这些字段计算新字段(newField)的值。这些键的使用方式就像另一个 sql 中的参数，用于检索新字段的值。

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE id1 INT;
  DECLARE cursor1 CURSOR FOR SELECT id1 FROM Table1;
  DECLARE id2 INT;
  DECLARE cursor2 CURSOR FOR SELECT id2 FROM Table1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 OPEN cursor1;
 read_loop: LOOP
    FETCH cursor1 INTO id1;
    IF done THEN
      LEAVE read_loop;
    END IF;

    OPEN cursor2;
    read_loop: LOOP
    FETCH cursor2 INTO id2;
    IF done THEN
       LEAVE read_loop;
    END IF;

    UPDATE Table1 SET newField = SELECT COUNT(*) FROM Table2 WHERE id1 = id1 AND id2 = id2 

END LOOP;
CLOSE cursor2;

  END LOOP;
  CLOSE cursor1;
END

例如:

表1

id1 id2 newField 
1    1    SELECT COUNT(*) FROM Table2 WHERE id1 = 1 AND id1 = 1
1    2    SELECT COUNT(*) FROM Table2 WHERE id1 = 1 AND id1 = 2
1    3    SELECT COUNT(*) FROM Table2 WHERE id1 = 1 AND id1 = 3
1    4    SELECT COUNT(*) FROM Table2 WHERE id1 = 1 AND id1 = 4
2    1    SELECT COUNT(*) FROM Table2 WHERE id1 = 2 AND id1 = 1
2    2    SELECT COUNT(*) FROM Table2 WHERE id1 = 2 AND id1 = 2
2    3    SELECT COUNT(*) FROM Table2 WHERE id1 = 2 AND id1 = 3
2    4    SELECT COUNT(*) FROM Table2 WHERE id1 = 2 AND id1 = 4

我想获得一个过程或类似的过程，允许迭代表并填充 newField 字段。

问候

Answer 1

也许您只想要一个窗口函数:

select id1, id2, count(*) over (partition by id1, id2) as newfield
from t;

从 MySQL 8+ 开始可以使用窗口函数。

在早期版本中，您可以使用 GROUP BY 和 JOIN:

select id1, id2, newfield
from t join
     (select id1, id2, count(*) as newfield
      from t
      group by id1, id2
     ) tt
     using (id1, id2);