达梦数据库使用常见错误及解决方案（MySQL）

[-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
    <foreach collection="userList" index="index" item="item" separator=",">
      (#{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})
    </foreach>
    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 (
      <foreach collection="userList" item="item" index="index" separator="UNION ALL">
        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
      </foreach>
      ) 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