主页 / dba / 问题 / 13434
Accepted
René Nyffenegger
Asked: 2012-02-20 12:04:37 +0800 CST

如何使用相关表的聚合值（最小值、最大值）快速更新表？

  • 772

我有一张桌子

create table w (
  id integer primary key,
  --
  min_xyz double,
  max_xyz double,
  min_abc double,
  max_abc double
);

和值目前min*是。max*null

我还有一张桌子

create table n (
    id   integer primary key,
    ---
    xyz  float,
    abc  float
);

这些表之间存在n:n关系：

create table n_in_w (
  n_id   integer not null references n,
  w_id   integer not null references w
  --- further attributes
);

现在，我想通过连接使用各自的值来填充min*和max*属性。以下会做wnn_in_w

update w set 
  min_xyz = (select min(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_xyz = (select max(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  min_abc = (select min(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_abc = (select max(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id);

但我担心这个 sql 查询的性能不是最优的，因为它必须扫描n4 次n.id；

理想情况下，我正在寻找类似的东西

update (
  select
    w.id,
    /*----*/
    w.min_xyz,
    w.max_xyz,
    w.min_abc,
    w.max_abc,
    /*-----*/
    min(n.xyz) min_n_xyz,
    max(n.xyz) max_n_xyz,
    min(n.abc) min_n_abc,
    max(n.abc) max_n_abc
  from 
    w, n_in_w, n
  where
    w.id = n_in_w.w_id and
    n.id = n_in_w.n_id
) i
set
  i.min_xyz = i.min_n_xyz,
  i.max_xyz = i.max_n_xyz,
  i.min_abc = i.min_n_abc,
  i.max_abc = i.max_n_abc;

（或类似的东西），但似乎用 mysql 是不可能的。

mysql

1 个回答

  1. Best Answer

    MySQL 语法：

    UPDATE 
      w 
  JOIN
      ( SELECT
              n_in_w.w_id,
              MIN(n.xyz) AS min_n_xyz,
              MAX(n.xyz) AS max_n_xyz,
              MIN(n.abc) AS min_n_abc,
              MAX(n.abc) AS max_n_abc
        FROM 
              n_in_w
          JOIN
              n
            ON 
              n.id = n_in_w.n_id
        GROUP BY
              n_in_w.w_id 
      ) nw
    ON  
      nw.w_id = w.id
SET
      w.min_xyz = nw.min_n_xyz,
      w.max_xyz = nw.max_n_xyz,
      w.min_abc = nw.min_n_abc,
      w.max_abc = nw.max_n_abc;
    • 8

相关问题