常用sql整理

1、if test 函数

<if test="name == 10">  
date= now()  
</if>

2、COALESCE 函数表示如果查询结果为空，用其他数值代替

COALESCE(money,0)

3、round 函数
参数大于 0，则四舍五入到指定的小数位。
参数等于 0，则四舍五入到最接近的整数。
参数小于 0，则在小数点左侧进行四舍五入。
如果round函数只有参数number,等同于digits 等于 0。
round(3.1415926,2)=3.14;
select round(193,-2)from dual; 200
4、case .....when ....then .....when.....then .....else 如果。。。就。。。
5、date 函数和to_char 都可以用户转化后比较日期

date(p_begin_date)=date(now())    
to_char(p_begin_date,'yyyyMMdd')='20180817'

6、插入一条记录，验证一个或这几个值是否重复，不重复才插入

INSERT INTO t_settle_log(s_type,s_create_time)select #{type}, #{createTime}from dual where not exists
    (select s_type,s_create_time from t_settle_log 
    where s_type = #{type} and to_char(s_create_time,'yyyyMMdd') = #{details})

说明：s_type,s_create_time 是要添加的字段后面的查询 dual表是表示筛选条件
如果数据库没有dual 视图就手动添加
CREATE OR REPLACE VIEW dual AS
SELECT 'X'::character varying AS dummy;

7、cast 函数，转化数据类型

cast(u_id as character varying(20)) as giveid

8、concat 函数讲两个字符串拼接起来

concat(to_char((now()::date)+tv.v_voucher_period,'yyyy-mm-dd'),'23:59:59')::timestamp as duetime,

9、插入一条数据返回数据主键或指定字段,返回值在对象取即可

//service
febActivityDao.insertGift(giftMap); 
Integer giftId = Integer.valueOf(giftMap.get("g_id").toString());
//dao
    void insertGift(Map<String, Object> giftMap);
// xml
<insert id="insertGift" parameterType="java.util.Map" useGeneratedKeys="true"  keyColumn="g_id" keyProperty="g_id">
    INSERT INTO t_gift(...)values(.....)
</insert>

10、excile 生成sql相关
--代金券
=CONCATENATE("insert into t_active_prize_give (a_active_id,a_prize_user_id,a_prize_give_id,a_prize_give_cause,a_prize_give_way,a_prize_give_time,a_prize_give_type,a_prize_is_used,a_voucher_id,a_voucher_over_time,a_voucher_use_status,a_red_value)values ('",G2,"', '",B2,"', 'SDFF20171030', '",H2,"', 0, now(), 1, 0, (select v_id from t_voucher where v_voucher_code='",F2,"'), concat(to_char((now()::date)+30,'yyyy-mm-dd'),' 23:59:59')::timestamp, 1, '",E2,"');")
=CONCATENATE("insert into t_voucher (v_voucher_code,v_voucher_value,v_voucher_period,v_voucher_invest_platform,v_voucher_product,v_voucher_least_amount,v_voucher_least_period,v_end_time,v_voucher_used)values ( '",K2,"', '",C2,"', '",D2,"', 0, 2, '",G2,"', '",I2,"', '",J2,"', 0);")
--到期红包
=CONCATENATE("insert into t_active_prize_give(a_prize_user_id,a_prize_give_id,a_prize_give_way,a_prize_give_time,a_prize_give_type,a_red_value,a_invest_money,a_invest_deadline,a_product_name, a_audit_status,a_redmoney_duetime,a_audit_time,a_active_id)values('",A2,"','",B2,"',1,now(),10,'",C2,"','",D2,"','",E2,"',2,1,'",G2,"',now(),'",H2,"');")
时间函数：=TEXT(F2,"yyyy-mm-dd HH:mm:ss")

--限制活动id、投资编号
=CONCATENATE("insert into t_active_prize_give(a_prize_user_id,a_prize_give_id,a_prize_give_way,a_prize_give_time,a_prize_give_type,a_red_value,a_invest_money,a_invest_deadline,a_product_name, a_audit_status,a_redmoney_duetime,a_audit_time,a_active_id)select '",A2,"','",B2,"',1,now(),10,'",C2,"','",D2,"','",E2,"',2,1,'",G2,"',now(),'",H2,"' from dual where not exists(select a_prize_give_id,a_active_id from t_active_prize_give where a_prize_give_id ='",B2,"' and a_active_id='",H2,"');")

11、trunc（number,0）函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
12、exists 和 in 用法
exists：
强调的是是否有返回集，不需知道具体返回的是什么，比如：

      SELECT * FROM customer
     WHERE
     not EXISTS (SELECT  0  from customer_goods
                 WHERE
            customer_id = 1
                          )

只要exists引导的子句有结果集返回，这个条件就算成立。这个返回的字段始终是0，改成1，则始终返回的是1，所以exists不
在乎返回的是什么内容，只在乎是否有结果集返回。
exists 和in 的区别
这二者最大的区别，是使用in只能返回一个字段值

     SELECT * FROM  customer c
     WHERE
     c.id not in (SELECT customer_id FROM customer_goods
              WHERE
            customer_id = 1
                     )

not in 和not exists 分别为in 和exists的对立面。

exists(sql 返回结果集为真)

not exists(sql 不返回结果集为真)

常用sql整理

推荐阅读更多精彩内容