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

) / (

sum(asccr.line_count)

aone_statistics_code_commit_record asccr,

change_request cr,

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'

) * (

cr.branch_url = asccr.branch_url

AND p.aone_project_id = cr.aone_project_id

)

)) 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 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变量：

@changeAmount := count(*) change_amount,

@baseLine := 1

...

统计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'

group by name order by c desc limit 3;