只执行查询语句是没有问题的。要是执行插入就会报错errCode = 2, detailMessage = Subquery is not supported in the select list.
insert into dm.dm_sonar_stat_product
select data_dt,
product_name,
ifnull(sum(issue_count), 0) as issue_count,
ifnull(sum(new_issue_count), 0) as new_issue_count,
ifnull(sum(bug_count), 0) as bug_count,
ifnull(sum(new_bug_count), 0) as new_bug_count,
ifnull(sum(vulnerability_count), 0) as vulnerability_count,
ifnull(sum(new_vulnerability_count), 0) as new_vulnerability_count,
ifnull(sum(code_smell_count), 0) as code_smell_count,
ifnull(sum(new_code_smell_count), 0) as new_code_smell_count
from (
select
DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) as data_dt,
pt.product_name as product_name,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
)
as issue_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.created_at >= UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(date(now()), interval 1 day), @@session.time_zone, '+08:00')) * 1000
and i.created_at <= UNIX_TIMESTAMP(CONVERT_TZ(date(now()), @@session.time_zone, '+08:00')) * 1000
)
as new_issue_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.issue_type in (2)
)
as bug_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.issue_type in (2)
and i.created_at >= UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(date(now()), interval 1 day), @@session.time_zone, '+08:00')) * 1000
and i.created_at <= UNIX_TIMESTAMP(CONVERT_TZ(date(now()), @@session.time_zone, '+08:00')) * 1000
)
as new_bug_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.issue_type in (3)
)
as vulnerability_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.issue_type in (3)
and i.created_at >= UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(date(now()), interval 1 day), @@session.time_zone, '+08:00')) * 1000
and i.created_at <= UNIX_TIMESTAMP(CONVERT_TZ(date(now()), @@session.time_zone, '+08:00')) * 1000
)
as new_vulnerability_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.issue_type in (1)
)
as code_smell_count,
(
select count(*)
from sdi_sonar_project_branches pb
join sdi_sonar_projects p on pb.project_uuid = p.uuid
join sdi_sonar_issues i on pb.uuid = i.project_uuid
where
pb.kee in ('master', 'main')
and p.kee in (
select sonar_qube_project_key from sdi_treasure_box_git_repo gr where gr.is_deleted = 0
)
and i.status not in ('RESOLVED')
and p.kee = gr.sonar_qube_project_key
and i.issue_type in (1)
and i.created_at >= UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(date(now()), interval 1 day), @@session.time_zone, '+08:00')) * 1000
and i.created_at <= UNIX_TIMESTAMP(CONVERT_TZ(date(now()), @@session.time_zone, '+08:00')) * 1000
)
as new_code_smell_count
from sdi_treasure_box_product pt join sdi_treasure_box_git_repo gr on pt.id = gr.product_id where pt.is_deleted = 0
) res
group by res.product_name ,data_dt
having sum(issue_count) is not null
