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;
