postgrepsql sql实例

1.java postgre时间类型映射

postgre	java
date(年月日)	java.sql.Date
time（时分秒）	java.sql.Time
timetz（时分秒.时区）	java.sql.Time
timestamp（年月日 时分秒纳秒（毫微秒））	java.sql.Timestamp/java.util.Date
timestamptz（年月日 时分秒.时区）	java.sql.Timestamp/java.util.Date

2.字符串截取

split_part(被分隔字段名称,分隔符号，取第几个)
concat_ws(xxx,xx,…)其中之一为null，任然拼接；
concat(xxx,xx,…)其中之一为null，不拼接，返回null;

-- 截取、拼接、重新赋值
update "process_map" set file_url=  concat_ws('https://qes.leiovo.com',SPLIT_PART(file_url,'.leiovo.com',2))  where file_url is not null;

3.sql实例

    <select id="getIssueDoneTask" resultType="com.leiovo.qit.ffrp.web.vo.IssueAssignMeVo">
    -- 1. 创建临时表tmp_isseinfo
        with tmp_issueinfo as (
        select *
        from issueinfo
        where ownerID = #{userId}
        and updatetime &gt;= (
        -- 子查询，获取最近的、有数据的一个月的时间点；
        -- 或者采用传参的方式：
        -- and  updateTime &gt;=  (#{param.lastTime}::Timestamp - interval '1 month')
            select  to_char((max(updatetime)::timestamp + '-1 month'),'yyyy-mm-dd') :: timestamp
            from issueinfo
            where ownerID = #{userId}
        )
        ORDER BY updatetime DESC
        ),
  -- 2. 创建临时表tmp_app_updatetime
        tmp_app_updatetime as (
            select a.issueid as issueid, max(logtime) as logtime
            from issuelog a
            join tmp_issueinfo b on a.issueid = b.issueid
            GROUP BY a.issueid
        )
  -- 3. 使用临时表作关联，缩小查询范围，提升性能
        select a.issueid as issueID, 'issue' as processName, c.itemname as stateName,
        a.createby as creatorName, a.createtime as createTime,
        u.itcode as updatedName, d.logtime as updateTime
        from tmp_issueinfo a
        join dictionaryItem c on a.stateID = c.itemID
        join tmp_app_updatetime d on d.issueid = a.issueid
        join issuelog l on l.issueid = d.issueid and l.logtime = d.logtime
        join userinfo u on l.ownerid = u.userid
        where 1=1
        <if test="param.keyWord != null and param.keyWord != ''">
            and concat_ws('issue',a.issueid,a.createBy,c.itemname,u.realname) ilike concat('%',#{param.keyWord},'%')
        </if>
        ORDER BY updateTime DESC;
    </select>