Subquery is not supported in the select list, 子查询的时候没有问题。使用insert语句的时候报错

Viewed 49

只执行查询语句是没有问题的。要是执行插入就会报错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 
2 Answers

具体是什么版本的?可以重开一下新优化器看看