表备份
select * into price_list1708151648 from price_list;
---日期格式化---
to_char(birthdate, 'yyyy-MM-dd') 返回string 'yyyy-MM-dd HH24:mi:ss'
to_date(to_char(birthdate, 'yyyy-MM-dd'),'yyyy-MM-dd') 返回date
----函数----
coalesce(num,0) num如果是null返回0
round(num,2); 四舍五入保留两位小数
floor(num); 向下取整 (类型不变,double还是double)
age(date1,date2); 返回如:16 years 7 mons 8 days
cast(3.33 as int4) 类型转换,如转为整型,返回3
ROW_NUMBER() OVER() 行号
-----更新----
update war_stqty_lst set unit=a.leastunit from (select b.id,b.leastunit from pub_drug b) a
where war_stqty_lst.unit is null and a.id=war_stqty_lst.drugid
--插入
INSERT into o2o_other_retail_goods (channel,goods_id,retail_id,o2o_spec_id,defprice,_version)
select aa.* from (select 2 channel,b.goodsid goods_id,a.id retail_id,b.o2o_spec_id,b.defprice,1 _version
from (select id from pub_retail where mt_flag=true and status=1) a,a1_test b) aa
------求年龄------
select id,username,birthdate,(('2017-07-20'-to_date(to_char(birthdate, 'yyyy-MM-dd'),'yyyy-MM-dd'))/365) as age from pub_user;//方法一
SELECT cast(extract(year from age(current_date,birthdate)) as int4) as age from pub_user;//方法二
select (case when a.years=0 and a.mons=0 and a.days=0 then ''
when a.years=0 and a.m