postgrepsql sql实例

左手的ㄟ右手 2022-12-10 14:54 216阅读 0赞

#### 1.java postgre时间类型映射 ####

<table> 
 <thead> 
  <tr> 
   <th>postgre</th> 
   <th>java</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date(年月日)</td> 
   <td>java.sql.Date</td> 
  </tr> 
  <tr> 
   <td>time（时分秒）</td> 
   <td>java.sql.Time</td> 
  </tr> 
  <tr> 
   <td>timetz（时分秒.时区）</td> 
   <td>java.sql.Time</td> 
  </tr> 
  <tr> 
   <td>timestamp（年月日 时分秒纳秒（毫微秒））</td> 
   <td>java.sql.Timestamp/java.util.Date</td> 
  </tr> 
  <tr> 
   <td>timestamptz（年月日 时分秒.时区）</td> 
   <td>java.sql.Timestamp/java.util.Date</td> 
  </tr> 
 </tbody> 
</table>

#### 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 >= (
            -- 子查询，获取最近的、有数据的一个月的时间点；
            -- 或者采用传参的方式：
            -- and updateTime >= (#{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>