on duplicate key update

INSERT INTO issue_change(project_id, add_date, change_amount, base_line, gmt_create , gmt_modified) SELECT p.id project_id, date(i.commit_date) add_date, @changeAmount := count() change_amount, @baseLine := (SELECT round( ( SELECT ifnull(count(),@changeAmount) FROM issue i, project p WHERE p.deparmtent_id = i.dept_no AND p.id = project_id AND YEAR (i.commit_date) = YEAR (add_date) AND MONTH (i.commit_date) = MONTH (add_date) - 1 and i.is_deleted ='n' and i.is_valid ='Y' and i.template_id != 100373 ) / ( SELECT sum(asccr.line_count) FROM aone_statistics_code_commit_record asccr, change_request cr, project p WHERE asccr.branch_url = cr.branch_url AND p.deparmtent_id = cr.dept_no AND p.id = 1 AND YEAR (asccr.commit_time) = YEAR (add_date) AND MONTH (asccr.commit_time) = MONTH (add_date) - 1 and cr.is_deleted ='n' ) * ( SELECT sum(asccr.line_count) FROM aone_statistics_code_commit_record asccr, change_request cr, project p WHERE cr.branch_url = asccr.branch_url AND p.aone_project_id = cr.aone_project_id AND p.id = project_id and cr.is_deleted ='n' ) )) base_line, now() gmt_create, now() gmt_modified FROM issue i JOIN project p on p.aone_project_id = i.aone_project_id where i.is_valid ='Y' and i.template_id != 100373 and p.id in (SELECT id from project WHERE type > 499) GROUP BY p.id, date(i.commit_date) on duplicate key update change_amount = @changeAmount, base_line = @baseLine, gmt_modified = now()

其中，project_id,add_date 两个字段联合键为唯一约束。 alter table issue_change add unique key uk_project_id_add_date (project_id,add_date);

声明SQL变量： INSERT INTO issue_change(project_id, add_date, change_amount, base_line, gmt_create , gmt_modified) SELECT p.id project_id, date(i.commit_date) add_date, @changeAmount := count(*) change_amount, @baseLine := 1 ...

on duplicate key update change_amount = @changeAmount, base_line = @baseLine, gmt_modified = now()

统计SQL sum，count

select p.name, round(sum(case when i.is_regression_stage_issue='Y' then 1 else 0 end)/count(i.id) *100,2) c from issue i

join project p on p.aone_project_id = i.aone_project_id

where p.deparmtent_id = 20862

and p.status = 1

and i.is_valid = 'Y'

and i.template_id != 100373

group by name order by c desc limit 3;