[-4080]: 不是 group by 表达式

【例子】：

select ri.*,count(bd.id) bindDeviceCount
        from room_ip ri left join bids_device bd on ri.name = bd.room_name

【问题原因】：

• GROUP BY 和 ORDER BY 一起使用时，ORDER BY 要在 GROUP BY 的后面。
• 在 select 需要查询的语句中选中的字段，必须出现在 GROUP BY 子句中。

  【解决方法】：

  若不想修改 SQL 语句，可以通过以下方法解决： 方法 1：修改 dm.ini 的 compatible_mode 参数为 4，来兼容 MySQL 语法，修改参数后需要重启数据库服务。 方法 2: 非 mysql 兼容模式下（即 COMPATIBLE_MODE 不等于 4），修改 GROUP_OPT_FLAG（动态会话级）参数包含 1 取值，即支持查询项不是 GROUP BY 表达式。

  alter  system set 'GROUP_OPT_FLAG'=1 both;

   使用Map接受数据列名会变成大写

   【解决方法】：加上columnNameUpperCase=false配置，如下

          jdbc:dm://192.168.0.96:5236?columnNameUpperCase=false

   【注意事项】：有些关键词还是会出现大写的情况，比如【count，enable】，可以加上双引号的方式来解决，如下：

  

  在DM8上对大字段类型列进行排序、分组等操作时，会报错-2685:试图在blob或者clob列上排序或比较 

  【例子】：C1为大字段

  • SELECT ID,C1 FROM T ORDER BY C1;
  • SELECT ID,COUNT(*) FROM T GROUP BY ID;（虽然没有使用到大字段C1，但是因为表中含有大字段，分组的时候依然会报错）

    【解决方法】：

    将数据库参数ENABLE_BLOB_CMP_FLAG设置为1后，数据库支持DISTINCT、ORDER BY、分析函数和集函数支持对大字段进行处理。

    【注意事项】：

    该参数并不能支持GROUP BY 对大字段进行处理。即不能 GROUP BY C1，正常来说也不会对TEXT字段进行分组

     ON DUPLICATE KEY UPDATE语法的改写

    【mysql】

        insert into "user"(third_id,third_status,mk_time,flag,card_type,valid_start_time,valid_end_time,user_name,
        organization,department,face_photo,access_card,type,can_access,enable_app,status,register_code,create_time,update_time,tel_extension,card_id)
        values
        
          (#{item.thirdId}, #{item.thirdStatus},#{item.mkTime},#{item.flag},#{item.cardType},#{item.validStartTime},
          #{item.validEndTime},#{item.userName},#{item.organization},
          #{item.department}, #{item.facePhoto},#{item.accessCard},0,#{item.canAccess},0,0,#{item.registerCode},#{item.createTime},#{item.updateTime},#{item.telExtension},#{item.cardId})
        
        ON DUPLICATE KEY UPDATE
        third_status = values(third_status),
        mk_time = values(mk_time),
        flag = values(flag),
        card_type = values(card_type),
        valid_start_time = values(valid_start_time),
        valid_end_time = values(valid_end_time),
        user_name = values(user_name),
        organization = values(organization),
        department = values(department),
        face_photo = values(face_photo),
        access_card = values(access_card),
        update_time = values(update_time),
        tel_extension = values(tel_extension),
        card_id=values(card_id);

    【dm】

          MERGE INTO "user" T1
          USING (
          
            SELECT
            #{item.thirdId} thirdId, #{item.thirdStatus} thirdStatus,#{item.mkTime} mkTim,#{item.flag} flag,#{item.cardType} cardType,#{item.validStartTime} validStartTime,
            #{item.validEndTime} validEndTime,#{item.userName} userName,#{item.organization} organization,
            #{item.department} department, #{item.facePhoto} facePhoto,#{item.accessCard} accessCard,0 type,#{item.canAccess} canAccess,0 enableApp,
            0 status,#{item.registerCode} registerCode,#{item.createTime} createTime,#{item.updateTime} updateTime,#{item.telExtension} telExtension,#{item.cardId} cardId
            FROM dual
          
          ) T2 ON (T1.third_id = T2.thirdId )
          WHEN NOT MATCHED THEN INSERT(third_id,third_status,mk_time,flag,card_type,valid_start_time,valid_end_time,user_name,
          organization,department,face_photo,access_card,type,can_access,enable_app,status,register_code,create_time,update_time,tel_extension,card_id) VALUES
          (T2.thirdId, T2.thirdStatus, T2.mkTim, T2.flag, T2.cardType, T2.validStartTime, T2.validEndTime, T2.userName, T2.organization, T2.department, T2.facePhoto, T2.accessCard,
          T2.type, T2.canAccess, T2.enableApp, T2.status, T2.registerCode, T2.createTime, T2.updateTime, T2.telExtension, T2.cardId
          )
          WHEN MATCHED THEN UPDATE
          SET
          T1.third_status = T2.thirdStatus,
          T1.mk_time = T2.mkTim,
          T1.flag = T2.flag,
          T1.card_type = T2.cardType,
          T1.valid_start_time = T2.validStartTime,
          T1.valid_end_time = T2.validEndTime,
          T1.user_name = T2.userName,
          T1.organization = T2.organization,
          T1.department = T2.department,
          T1.face_photo = T2.facePhoto,
          T1.access_card = T2.accessCard,
          T1.update_time = T2.updateTime,
          T1.tel_extension = T2.telExtension,
          T1.card_id=T2.cardId;

      GROUP_CONCAT改写为LISTAGG

    【mysql】

            SELECT
                location_id,
                GROUP_CONCAT( personnel_id ORDER BY snap_time DESC ) ids,
                COUNT(personnel_id) count
            FROM
                user_access_record
            WHERE
                door_no = 1
            GROUP BY
                location_id

    【dm】

            SELECT
                location_id,
                LISTAGG( personnel_id,',')WITHIN GROUP(ORDER BY snap_time DESC) ids,
                COUNT(personnel_id) count
            FROM
                user_access_record
            WHERE
                door_no = 1
            GROUP BY
                location_id

    SELECT
            pr.id,
            pr.project_no,
            LISTAGG(DISTINCT eir.expert_name,',') stockName,
            LISTAGG(DISTINCT eir2.expert_name,',') expertName
            FROM
            project_sync_record pr
            left join expert_sync_record eir
            on eir.expert_type = '采购人' and pr.project_no = eir.project_no and TO_CHAR (pr.actual_start_time, 'yyyy-mm-dd') = eir.create_date
            left join expert_sync_record eir2
            on eir2.expert_type = '专家' and pr.project_no = eir2.project_no and eir.create_date = eir2.create_date
    group by pr.project_no,pr.meeting_type
    

    分组后按组里某个字段排序取最大一条记录的其他字段

    【mysql】注意mysql8.0之前是不支持该语法的，顾这里不列例子

    【dm】按user_id分组，并取check_time最大的一条记录的result

            SELECT
                "user".id,
                "user".user_name name,
                "user".phone tel,
                "user".photo_url photoUrl,
                ah.check_time checkTime,
                ah.result "result"
            FROM
                ( SELECT id, row_number() over ( PARTITION BY user_id ORDER BY check_time DESC ) AS f_part FROM alarm_handler WHERE record_id = #{id} ) t
                    INNER JOIN alarm_handler ah ON t.id = ah.id
                    LEFT JOIN "user" ON ah.user_id = "user".id
            WHERE
                t.f_part = 1