mysql - "union all"的多重选择不起作用

Question

查询不起作用:

select * from
    (select
        department as "Delegación",
         company as Compañía,
         config as "Configuración Vehículos",
         docs as "Documentos Vehículos",
         count(distinct idClientTruck) as vehículos,
         sum(ko) as "Doc. incorrectos Vehículos",
         round(sum(ko)/(docs*count(distinct idClientTruck))*100) as "% Docs. Vehículos"
      from
         (
         select
           cd.name as department,
           cfg_g.name as config, 
           c.businessname as company, 
           (select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as docs, 
           wv.idClientTruck as idEntity, 
           cfg_g.idwidocconfiggroup as idWidocConfigGroup, 
           cfg.idwidocconfig as idWidocConfig, 
           cfg.iddocument as idDocument, 
           a.idstatus as idStatus, 
           if(a.idstatus=43002,"0","1") as ko 
         from
           widoc_config_group cfg_g
             join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
               join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
             join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
             join widoc_vehicle wv on wv.idwidocclient=wc.idwidocclient and wv.idstatus=71001
             join client_truck t on t.idclient_truck=wv.idclienttruck and t.idstatus_truckpart=33005
             join widoc_fulfill_vehicle fv on fv.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fv.idwidocvehicle=wv.idwidocvehicle
             left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wv.idClientTruck
             left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
         where
         cfg_g.idclient = 3683
             #wc.idclientAssociated = 3683
             and cfg_g.identitytype=73004
             and cfg_g.idstatus=71001
              )v 
               ) t UNION ALL
    (select
        config as "Configuración Conductores",
        docs as "Documentos Conductores",
        count(distinct idDriver) as Conductores,
        sum(ko) as "Doc. incorrectos Conductores",
        round(sum(ko)/(docs*count(distinct idDriver))*100) as "% Docs. Conductores"
     from
        (
        select 
           cd.name as department,
           cfg_g.name as config, 
           c.businessname as company, 
           (select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as docs, 
           wd.idDriver as idEntity, 
           cfg_g.idwidocconfiggroup as idWidocConfigGroup, 
           cfg.idwidocconfig as idWidocConfig, 
           cfg.iddocument as idDocument, 
           a.idstatus as idStatus, 
           if(a.idstatus=43002,"0","1") as ko
        from
            widoc_config_group cfg_g
            join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
             join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
            join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
            join widoc_driver wd on wd.idwidocclient=wc.idwidocclient and wd.idstatus=71001
            join driver d on d.iddriver=wd.iddriver and d.idstatus=71001
            join widoc_fulfill_driver fd on fd.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fd.idwidocdriver=wd.idwidocdriver
            left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wd.idDriver
            left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
        where
             cfg_g.idclient = 3683
            #wc.idclientAssociated = 1865
            and cfg_g.identitytype=73003
            and cfg_g.idstatus=71001
            )x) d
            group by department,company,config,idWidocConfigGroup
        ;

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd, group by department,company,config,idWidocConfigGroup' at line 74

分隔查询:

select
    department as Delegación,
    company as Compañía,
    config as Configuración,
    docs as Documentos,
    count(distinct idDriver) as Conductores,
    sum(ko) as "Doc. incorrectos",
    round(sum(ko)/(docs*count(distinct idDriver))*100) as "% incumplimiento"
 from
    (
    select 
       cd.name as department,
       cfg_g.name as config, 
       c.businessname as company, 
       (select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as docs, 
       wd.idDriver, 
       cfg_g.idwidocconfiggroup, 
       cfg.idwidocconfig, 
       cfg.iddocument, 
       a.idstatus, 
       if(a.idstatus=43002,"0","1") as ko
    from
        widoc_config_group cfg_g
        join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
         join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
        join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
        join widoc_driver wd on wd.idwidocclient=wc.idwidocclient and wd.idstatus=71001
        join driver d on d.iddriver=wd.iddriver and d.idstatus=71001
        join widoc_fulfill_driver fd on fd.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fd.idwidocdriver=wd.idwidocdriver
        left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wd.idDriver
        left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
    where
         cfg_g.idclient = 3683
        #wc.idclientAssociated = 1865
        and cfg_g.identitytype=73003
        and cfg_g.idstatus=71001
        ) t
 group by department,company,config,idwidocconfiggroup;

第二个查询:

select
    department as "Delegación",
     company as Compañía,
     config as Configuración,
     docs as Documentos,
     count(distinct idClientTruck) as vehículos,
     sum(ko) as "Doc. incorrectos",
     round(sum(ko)/(docs*count(distinct idClientTruck))*100) as "% incumplimiento"
  from
     (
     select
      cd.name as department,    
        cfg_g.name as config, 
        c.businessname as company, 
        (select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as docs, 
        wv.idClientTruck, 
        cfg_g.idwidocconfiggroup, 
        cfg.idwidocconfig, 
        cfg.iddocument, 
        a.idstatus, 
        if(a.idstatus=43002,"0","1") as ko     
     from
       widoc_config_group cfg_g
         join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
           join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
         join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
         join widoc_vehicle wv on wv.idwidocclient=wc.idwidocclient and wv.idstatus=71001
         join client_truck t on t.idclient_truck=wv.idclienttruck and t.idstatus_truckpart=33005
         join widoc_fulfill_vehicle fv on fv.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fv.idwidocvehicle=wv.idwidocvehicle
         left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wv.idClientTruck
         left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
     where
     cfg_g.idclient = 3683
         #wc.idclientAssociated = 3683
         and cfg_g.identitytype=73004
         and cfg_g.idstatus=71001
         ) t
     group by department,company,config,idwidocconfiggroup;

它们都工作得很好。

Answer 1

我在您的查询中发现了很多问题

1. 对于联合和联合，两个查询选择中的所有列数都必须相同，但两个查询中都没有相同的值第一个选择的列数是 7

        select
        department as "Delegación",
        company as Compañía,
        config as "Configuración Vehículos",
        docs as "Documentos Vehículos",
        count(distinct idClientTruck) as vehículos,
        sum(ko) as "Doc. incorrectos Vehículos",
        round(sum(ko)/(docs*count(distinct idClientTruck))*100) as "% Docs. Vehículos"
   

和

其中第二个选择的列数是 5

    select
    config as "Configuración Conductores",
    docs as "Documentos Conductores",
    count(distinct idDriver) as Conductores,
    sum(ko) as "Doc. incorrectos Conductores",
    round(sum(ko)/(docs*count(distinct idDriver))*100) as "% Docs. Conductores"

按部门、公司、配置、idWidocConfigGroup 分组需要放置在应位于alias x之后的正确位置

删除不必要的别名，例如 d 和 t