故障003：迁移改写MySQL多字段去重计数

DM技术交流QQ群：940124259

1. 问题描述

查询SQL中对count多个字段去重（取唯一值）并分组查询，在达梦数据库中执行报错，此类错误亦是从MySQL迁移至DM数据库产生的，也是因为该SQL语法不符合达梦数据库，故我们需要手工改写此类SQL，以此满足功能需求和语法要求。

客户现场报错截图：

---

2. 解决方法

思路：
第一步：去重count中统计多个字段的重复记录；
第二步：对第一步去重后的记录进行分组查询形成一张内嵌子查询表；
第三步：再将第二步产生的子查询表与原先查询表进行连接查询，即可获得子查询表中目标列作为最后查询结论集的附加列（目标结果列值）。

改写SQL：

-- 句型1：group by去重
SELECT
        a.sp_mechanism_id                        as spMechanismId      ,
        FIRST_VALUE(a.ac_operation_area_name)    as acOperationAreaName,
        FIRST_VALUE(a.sp_mechanism)              as spMechanism        ,
        a.reg_query_model                        as regQueryModel      ,
        FIRST_VALUE(a.ac_target_area_name)       as targetAreaName     ,
        FIRST_VALUE(a.ac_target_mac_ip)          as targetMac          ,
        a.reg_archive_region                     as regArchiveRegion   ,
        SUM(a.ac_skim_num)                       as acSkimNum          ,
       count(a.ac_operation)                    as regApplyNum        ,
--    count(DISTINCT a.ac_operation, a.reg_id)  as userNum            ,       
        b.userNum,  
        SUM(a.ac_print_num)                      as acPrintNum
FROM
        AMS_ARCHIVES.t_archives_consult as a join
        (   
           select b.reg_id, b.ac_operation, count(*) as userNum
              from (
                 select bb.reg_id, bb.ac_operation
                 from AMS_ARCHIVES.t_archives_consult bb
                 where bb.reg_id != 0   -- 这个条件最好跟上，尽量提前过滤掉无用数据
                 group by bb.ac_operation, bb.reg_id  -- 里一层：多个字段去重，两种方式（group by或distinct）
              ) b  
              group by b.reg_id, b.ac_operation   --  里二层：分组统计      
                       
        ) b on a.reg_id = b.reg_id and a.ac_operation = b.ac_operation 
WHERE
        1=1
    AND a.reg_id != 0;
    
    
   
/***********************************************************************************/     
-- 句型2：distinct去重
SELECT
        a.sp_mechanism_id                        as spMechanismId      ,
        FIRST_VALUE(a.ac_operation_area_name)    as acOperationAreaName,
        FIRST_VALUE(a.sp_mechanism)              as spMechanism        ,
        a.reg_query_model                        as regQueryModel      ,
        FIRST_VALUE(a.ac_target_area_name)       as targetAreaName     ,
        FIRST_VALUE(a.ac_target_mac_ip)          as targetMac          ,
        a.reg_archive_region                     as regArchiveRegion   ,
        SUM(a.ac_skim_num)                       as acSkimNum          ,
       count(a.ac_operation)                    as regApplyNum        ,
--    count(DISTINCT a.ac_operation, a.reg_id)  as userNum            ,       
        b.userNum,  
        SUM(a.ac_print_num)                      as acPrintNum
FROM
        AMS_ARCHIVES.t_archives_consult as a join
        (   
           select b.reg_id, b.ac_operation, count(*) as userNum
              from (
                 select distinct bb.reg_id, bb.ac_operation
                 from AMS_ARCHIVES.t_archives_consult bb
                 where bb.reg_id != 0   -- 这个条件最好跟上，尽量提前过滤掉无用数据                 
              ) b  
              group by b.reg_id, b.ac_operation   --  里二层：分组统计      
                       
        ) b on a.reg_id = b.reg_id and a.ac_operation = b.ac_operation 
WHERE
        1=1
    AND a.reg_id != 0;
    

改写SQL后正确输出结果截图：