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')