在Mybatis中PostgresSQL语法常用SQL语句

查询操作

#基本查询
	 select * from A 
#带有日期的查询，将日期转化为字符串进行返回
		 select cycle,bdate,edate from 
				  (    select cycle,to_char(bdate,'YYYY-MM-DD HH24:MI:SS') bdate,
					   to_char(edate,'YYYY-MM-DD HH24:MI:SS') edate from billing_cycle ) A    

插入语句

#当数据库中是日期类型，传递的是String类型时
  	insert into billing_cycle (cycle,bdate,edate)   		
    	values(#{cycle},(to_timestamp(#{bdate},'YYYY-MM-DD HH24:MI:SS')),(to_timestamp(#{edate},'YYYY-MM-DD HH24:MI:SS'))) 

增加新的数据的时候增加的是多条语句[一个List集合]

<sql id="accountsColumnList" >
		custid, account, accttype, acctname, maxfee, cycle, lastcycle, initbalance, balance, autopayfee ,addbalance,memo,productcode
	</sql>
	
	<!-- 新增账户信息 -->
	<insert id="addAccounts" parameterType="java.util.List">
		<foreach collection="list" item="item" index="index" separator=";">
		insert into accounts(<include refid="accountsColumnList"/>)
			values(#{item.custid}, #{item.account}, #{item.accttype}, #{item.acctname}, 0, #{item.cycle}, 0,0, 0, 0, 0,#{item.memo},#{item.productcode})
    	</foreach>
	</insert>

删除语句

     ④批量删除语句
	<delete id="deleteBillingCycleTest" parameterType="java.util.List" >
		   delete from biliing_cycle where  cycle in 
		<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
			#{item.cycle}
		</foreach>
	</delete>

获取到字段的最大数值并且加一，

select COALESCE(max(sid),0)+1 sid from switch

在mybatis对应的数据库中关于select等其他语句中嵌套部分sql语句

<sql id="switchColumnList" >
		 sid, sname, aid,memo,stype,productcode,bindmode,linemode
	</sql>
	<insert id="addSwitch" parameterType="Switch" >
		insert into switch(<include refid="switchColumnList" />)
			values(#{sid},#{sname},#{aid},#{memo},#{stype},#{productcode},#{bindmode},#{linemode})
	</insert>

当更新的语句为中传递的是一个List<>集合的时候

<foreach collection="list" item="item" index="index" open="" separator=";" close="">
		update job_flow
			set ifreceive='Y',receivestaff=#{item.receivestaff},receivedate=to_timestamp(#{item.receivedate},'YYYY-MM-DD HH24:MI:SS')
				where jobid=#{item.jobid} and jfno=#{item.jfno}
	</foreach>

在数据库中对某一列进行去重的操作distinct

select distinct operatorsname from billing_calltype_net	

当数据的文件为空的情况下赋值为0，coalesce

select 
		coalesce(userid,0) userid,caller,called,to_char(begindate,'yyyy-mm-dd hh24:mi:ss') begindate,
		sduration,coalesce(netcalltype,'') netcalltype,netcalltypeid,basefee,specifee,fee
	from 
		bill_201811 b

截取在sql中获取到的消息例如：返回的年限是201811，进行截取字符串2018,substr(字段名称,0,5) [0,5）

	select distinct substr(cycle,0,5) annual from accountbill_imp order by annual desc

存储的sql语句将字符类型转化为日期类型

to_timestamp(#{bdate},'YYYY-MM-DD HH24:MI:SS')