[-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
达梦建表时主键默认是非聚集索引
达梦建表时主键默认是非聚集索引,默认聚集索引键是 ROWID,即记录默认以 ROWID 在页面中排序。ROWID 是 B 树为记录生成的逻辑递增序号,表上不同记录的 ROWID 是不一样的,并且最新插入的记录 ROWID 最大。很多情况下,以 ROWID 建的默认聚集索引并不能提高查询速度,因为实际情况下很少人根据 ROWID 来查找数据。
如果想和mysql一样,建表时主键即是聚集索引,可以参考如下:
在 dm.ini 配置文件中,可以通过指定 PK_WITH_CLUSTER 使表中的主键为聚集主键。默认情况下,PK_WITH_CLUSTER 为 0,即建表时指定的主键是非聚集主键;若为 1,则主键为聚集主键。
但是有两个注意点:
1.聚集索引只能有一个
2.聚集索引不能和大字段(比如TEXT)同时存在
所以要不要聚集索引还是看实际情况,这一点是和mysql不同的。
关联查询排序索引失效
不知道为什么关联之后达梦索引就变得很不智能,需要用hint提示它该怎么用索引,mysql这方面会好很多
select /*+ TOP_ORDER_OPT_FLAG(1) */pm.*,
"user".organization guikou_organization,
"user".department guikou_department,
"user".phone guikou_phone
from park_manage pm
LEFT JOIN "user"
ON pm.guikou_id = "user".id
order by pm.create_time desc limit 20;
/*+ TOP_ORDER_OPT_FLAG(1) */的意思参考
查询所有表所占的大小及所在空间
SELECT
A.OWNER AS "模式",
A.SEGMENT_NAME AS "表名",
A.BYTES/1024 AS "大小(KB)",
A.TABLESPACE_NAME AS "所属表空间",
B.COMMENTS AS "表注释"
FROM DBA_SEGMENTS A,DBA_TAB_COMMENTS B
WHERE A.OWNER=B.OWNER
AND A.SEGMENT_NAME = B.TABLE_NAME
--AND A.OWNER='SYSDBA'
ORDER BY SEGMENT_NAME ASC;