达梦数据库使用常见错误及解决方案（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          (#{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_dategroup 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

来源地址：https://blog.csdn.net/evalsys/article/details/131636881