#test、test2为同样的2张表,把A表数据更新到B表
update A t1 set t1.project = (select project from B t2 where t1.product=t2.product)
update A t1,B t2 set t1.project = t2.project where t1.product=t2.product
#-----------分别统计记录中每个产品对应的记录数,并更新对应的统计表prdCount-------------------------
#-----------------------------------------------------------------------
#5万条数据约需要0.09ms
#18万条数据:0.573ms/0.414ms/0.431ms/0.399ms
update prdCount set total = (select count(*) as total from zt_build tab group by product having tab.product = prdCount.product)
#------------------------------------------------------------------------
#------------------------------------------------------------------------
#5万条数据约需要0.008ms
#18万条数据:0.045ms/0.031ms/0.032ms/0.038ms
update prdCount A, (select product,count(*) as total from zt_build tab group by product ) B
set A.total=B.total
where A.product = B.product
#------------------------------------------------------------------------
#------------游标更新---------------------------------------------
#5万条数据约0.008ms
#18万条数据约0.041ms/0.034ms/0.038ms/0.031ms
#创建存储过程,通过游标更新数据
drop procedure if exists update2Tab;
CREATE PROCEDURE `update2Tab`()
BEGIN
DECLARE no_more_record INT DEFAULT 0;
DECLARE v_product int;
DECLARE v_count int;
DECLARE cur_record CURSOR FOR SELECT product,count(*) as tatol from zt_build group by product; /*首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
OPEN cur_record; /*接着使用OPEN打开游标*/
FETCH cur_record INTO v_product, v_count; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
WHILE no_more_record != 1 DO
update prdCount set total = v_count
where product = v_product;
FETCH cur_record INTO v_product, v_count;
END WHILE;
CLOSE cur_record; /*用完后记得用CLOSE把资源释放掉*/
END;
call update2Tab();
#--------------------------------------------------
#子查询创建产品统计表prdCount
create table prdCount(select distinct product from zt_build );
#新增一个统计列
alter table prdCount add column total int;
select count(*) from zt_build;
update prdcount set total = 0;
select * from prdcount;
#子查询创建表
create table zt_build2(select * from zt_build union select * from zt_build )
#子查询插入
insert into zt_build (`product`, `branch`, `project`, `name`, `scmPath`, `filePath`, `date`, `stories`, `bugs`, `builder`, `desc`, `deleted`)
(SELECT `product`, `branch`, `project`, `name`, `scmPath`, `filePath`, `date`, `stories`, `bugs`, `builder`, `desc`, `deleted` FROM `zt_build`)
update A t1 set t1.project = (select project from B t2 where t1.product=t2.product)
update A t1,B t2 set t1.project = t2.project where t1.product=t2.product
#-----------分别统计记录中每个产品对应的记录数,并更新对应的统计表prdCount-------------------------
#-----------------------------------------------------------------------
#5万条数据约需要0.09ms
#18万条数据:0.573ms/0.414ms/0.431ms/0.399ms
update prdCount set total = (select count(*) as total from zt_build tab group by product having tab.product = prdCount.product)
#------------------------------------------------------------------------
#------------------------------------------------------------------------
#5万条数据约需要0.008ms
#18万条数据:0.045ms/0.031ms/0.032ms/0.038ms
update prdCount A, (select product,count(*) as total from zt_build tab group by product ) B
set A.total=B.total
where A.product = B.product
#------------------------------------------------------------------------
#------------游标更新---------------------------------------------
#5万条数据约0.008ms
#18万条数据约0.041ms/0.034ms/0.038ms/0.031ms
#创建存储过程,通过游标更新数据
drop procedure if exists update2Tab;
CREATE PROCEDURE `update2Tab`()
BEGIN
DECLARE no_more_record INT DEFAULT 0;
DECLARE v_product int;
DECLARE v_count int;
DECLARE cur_record CURSOR FOR SELECT product,count(*) as tatol from zt_build group by product; /*首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
OPEN cur_record; /*接着使用OPEN打开游标*/
FETCH cur_record INTO v_product, v_count; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
WHILE no_more_record != 1 DO
update prdCount set total = v_count
where product = v_product;
FETCH cur_record INTO v_product, v_count;
END WHILE;
CLOSE cur_record; /*用完后记得用CLOSE把资源释放掉*/
END;
call update2Tab();
#--------------------------------------------------
#子查询创建产品统计表prdCount
create table prdCount(select distinct product from zt_build );
#新增一个统计列
alter table prdCount add column total int;
select count(*) from zt_build;
update prdcount set total = 0;
select * from prdcount;
#子查询创建表
create table zt_build2(select * from zt_build union select * from zt_build )
#子查询插入
insert into zt_build (`product`, `branch`, `project`, `name`, `scmPath`, `filePath`, `date`, `stories`, `bugs`, `builder`, `desc`, `deleted`)
(SELECT `product`, `branch`, `project`, `name`, `scmPath`, `filePath`, `date`, `stories`, `bugs`, `builder`, `desc`, `deleted` FROM `zt_build`)