更新多个列一个子查询
我有一个经典的父母 - 子女关系的sqlite:更新多个列一个子查询
create table tq84_parent (
id int primary key,
avg_val float,
min_val float,
max_val float
);
create table tq84_child (
id_parent int references tq84_parent,
val int
);
这是充满如下
insert into tq84_parent (id) values(1);
insert into tq84_parent (id) values(2);
insert into tq84_child values (1, 1);
insert into tq84_child values (1, 2);
insert into tq84_child values (1, 3);
insert into tq84_child values (2, 6);
insert into tq84_child values (2, 7);
insert into tq84_child values (2, 8);
insert into tq84_child values (2, 9);
insert into tq84_child values (2, 10);
现在,我想更新tq84_parent
其列avg_val
,min_val
和max_val
反映其子表的相应聚合值。
我可以用这个语句实现这一目标:
update
tq84_parent
set
avg_val = (select avg(val) from tq84_child where id_parent = id),
min_val = (select min(val) from tq84_child where id_parent = id),
max_val = (select max(val) from tq84_child where id_parent = id);
的结果,期望是:
.mode columns
select * from tq84_parent;
1 2.0 1.0 3.0
2 8.0 6.0 10.0
我担心,但是,这样的语句的性能不是最优的,因为记录数在tq84_parent
和tq84_child
中都增长,因为几乎相同的查询是三次而不是一次。
所以,我想一起去像
update
tq84_parent
set
(
avg_val,
min_val,
max_val
)
= (select
avg(val),
min(val),
max(val)
from
tq84_child
where
id_parent = id
);
不工作。
那么,有没有办法重写我的查询?
由于id
和id_parent
是主键和外键,这意味着id
是唯一的,而id_parent
总是引用现有的ID。
这意味着你可以使用REPLACE
更改表,因为唯一密钥总是违反(又名没有插入会做),并REPLACE
可以采取一个子查询,并立即设置多个栏目;
REPLACE INTO tq84_parent (id, avg_val, min_val, max_val)
SELECT id_parent, AVG(val), MIN(val), MAX(val)
FROM tq84_child
GROUP BY id_parent;
注:由于CL指出,由于行是更换,不更新未在查询中设置字段将有自己的缺省值。这意味着如果表格中的其他字段需要保持不变,那么旧方法仍然是实现它的方法。
REPLACE从不更新表格,它只是删除旧的行。 –
@CL。你当然是对的,这就是我在第一杯咖啡前回答数据库问题所得到的结果。谢谢。 –
查询执行的实际速度主要取决于需要完成的磁盘I/O数量。
通过上面显示的数据库模式,每个子查询都需要扫描整个表。 如果您在计算聚合值所需的所有列上都有覆盖索引,则每个子查询只需扫描索引的一小部分,因此实际上,在执行多个子查询时,确保数据已存在于缓存中相同的ID:
CREATE INDEX xxx ON tq84_child(id_parent, val);
这是您的实际'tq84_parent'表,还是有更多的列? –
'tq84_parent'中有更多列。如果它有所作为,我仍然对它的实际'tq84_parent'表有兴趣。 –