dm（达梦）_达梦groupconcat

1分组

后拼接字段(GROUP_CONCAT)

分组的字段需要加

 

        SELECT
        /*+ GROUP_OPT_FLAG(1)*/ r.role_id as id,
        r.role_name as name,
        r.role_code as code,
        WM_CONCAT(u.user_id) as userIds ,
        WM_CONCAT(u.user_name) as userNames
        from
        role r
        left join user_role ur on r.id = ur.role_id and ur.IS_DEL=0
        left join user u on ur.user_id =u.id and u.IS_DEL=0
        WHERE
        r.IS_DEL = 0
        group by r.role_id

多条数据 分组 展示字段用逗号隔开

LISTAGG(bus.ORG_NAME, ';')  可以自定义分隔符

wm_concat()  默认逗号隔开

注意：以上俩个函数，分组后 字段太长会报 字符串截断异常。

select
        study.ID                      ,
        study.PROJECT_NAME            ,
        LISTAGG(bus.ORG_NAME, ';')    ,
                     wm_concat(bus.BUSINESS_SUPERVISOR_NAME)
   
from
        "ITMS_PROJECT"."STUDY" study
LEFT JOIN "ITMS_PROJECT"."ORG" bus
ON
        bus.BUSINESS_ID=study.ID
 
group by
        study.ID
ORDER by
        study.ID

2 时间格式化String

to_char(PLANNED_TIME,'yyyy-mm-dd') as PLANNED_TIME
 

3 字段拼接||

 SELECT * ,VALID_START_TIME||'-'||VALID_END_TIME as validStartEndTime,。。。。。

4 时间范围查询

方式一

startTime /endTime 都是date类型  

字符串类型必须符合时间格式

     select *
        FROM
        user u
        WHERE 1=1
        <if test="startTime != null and endTime != null">
            AND u.start_time BETWEEN #{startTime } AND #{endTime }
        </if>

方式二（注意时间标度）

为什么我的时间后面加了['2023-03-30 23:59:59.999999' ]  因为数据库的标度是6，['2023-03-30 23:59:59' ] 这样的写法等于['2023-03-30 23:59:59.000000' ]，会查询不到 某些数据

select
*
from
        "aa"."aa" where  
CREATE_TIME >='2023-03-29' and  CREATE_TIME <= '2023-03-30 23:59:59.999999'  
ORDER by  CREATE_TIME DESC;

xml拼接

  AND TIME >= CONCAT(#{timeStart},' 00:00:00')
   AND TIME <= CONCAT(#{timeEnd},' 23:59:59')

5 展示序号 排序要跟整个sql保持一致才是你想要的递增序号

row_number() over (order by a.update_time desc) as number,