基于PostgreSQL的sql

表备份
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.mons>0 and a.days=0 then a.mons||'个月'
when a.years=0 and a.mons=0 and a.days>0 then a.days||'天'
when a.years=0 and a.mons>0 and a.days>0 then a.mons||'个月'||a.days||'天'
when a.years>0 and a.years<12 and a.mons=0 then a.years||'岁'
when a.years>0 and a.years<12 and a.mons>0 then a.years||'岁'||a.mons||'个月'
when a.years>=12 then a.years||'岁'
end) as age
 from (select cast(extract(year from age(timestamp '2005-02-24')) as int4) as years,
                cast(extract(month from age(timestamp '2005-02-24')) as int4) as mons,
                cast(extract(day from age(timestamp '2005-02-24')) as int4) as days) as a

update outp_patient set age = tmp.age from
(select a.id,(case when a.years=0 and a.mons=0 and a.days=0 then ''
        when a.years=0 and a.mons>0 and a.days=0 then a.mons||'月'
        when a.years=0 and a.mons=0 and a.days>0 then a.days||'天'
        when a.years=0 and a.mons>0 and a.days>0 then a.mons||'月'||a.days||'天'
        when a.years>0 and a.years<12 and a.mons=0 then a.years||'岁'
        when a.years>0 and a.years<12 and a.mons>0 then a.years||'岁'||a.mons||'月'
        when a.years>=12 then a.years||'岁' end) as age
        from (select p.id,cast(extract(year from age(p.birthdate)) as int4) as years,
              cast(extract(month from age(p.birthdate)) as int4) as mons,
              cast(extract(day from age(p.birthdate)) as int4) as days
              from outp_patient p where p.birthdate is not null) as a) tmp
where tmp.id = outp_patient.id

------同列字符串拼接-----
SELECT emrid,array_to_string(ARRAY(SELECT distinct diagname FROM outp_patient_diag u1 WHERE u1. emrid = u2. emrid),',') AS diagnames FROM outp_patient_diag u2 group BY emrid order by emrid;

//更简单
select t1.resa_doc_id, string_agg(t2.settle_name,',') paytype from rsa_receivable t1 left join rsa_settle t2 on t1.settle_id=t2.id group by t1.resa_doc_id

------动态行转列-----------
select to_char(doc.rcptdate, 'yyyy-MM-dd') as rcptdate";
            if(items!=null&&items.size()>0){//items是动态列的集合
                for(Map<String, Object> map:items){
                    String itemid=formatHelper.getStringValue(map, "itemid");//初始列名
                    String itemname=formatHelper.getStringValue(map, "itemname");//转化列名
                    sql+=",round(coalesce(sum(case rpt.paytype when "+itemid+" then rpt.payamount end),0),2) as "+itemname;
                }
            }
sql+=" from outp_rcpt_doc doc left join outp_rcpt_paytype rpt on doc.id=rpt.rcptid where 1=1";
sql+=" group by to_char(doc.rcptdate, 'yyyy-MM-dd'）";

-------合并结果集----------
select d.id,d.drugname as itemname,1 as itemclass from pub_drug d 
union all 
select fi.id,fi.itemname,2 as itemclass from pub_fee_item fi;

---------把表中一列更新成表中几列的拼接------
update pub_drug set drugspec = tmp.drugspec2 from (select d.id,d.drugspec,d.dose,oi1.itemname as unit1,oi2.itemname as unit2,oi3.itemname as unit3,d.sizerate,
CAST(d.dose AS FLOAT)||oi1.itemname||'*'||CAST(d.sizerate AS FLOAT)||oi2.itemname||'/'||oi3.itemname drugspec2 from pub_drug d
left join sys_option_item oi1 on (oi1.optionid=268 and d.doseunit=to_number(oi1.itemid,'999999'))
left join sys_option_item oi2 on (oi2.optionid=265 and d.leastunit=to_number(oi2.itemid,'999999')) 
left join sys_option_item oi3 on (oi3.optionid=265 and d.packunit=to_number(oi3.itemid,'999999')) 
where d.id<240 order by d.id ) tmp
where tmp.id = pub_drug.id
and pub_drug.id < 240

-----------------------COALESCE用法-------------
 COALESCE返回它的第一个非NULL的参数的值。它常用于在为显示目的检索数据时用缺省值替换NULL值。
    COALESCE(value[, ...])
    和CASE表达式一样，COALESCE将不会计算不需要用来判断结果的参数。也就是说，在第一个非空参数右边的参数不会被计算。

select COALESCE(p1.price,p2.price,0) from pub_fee_item item
left join price_list p1 on (item.id=p1.itemid and p1.itemclass=2 and p1.pricetype=1 and p1.branchid=41)
left join price_list p2 on (item.id=p2.itemid and p2.itemclass=2 and p2.pricetype=1 and p2.branchid is null)
where item.id=12

-------------------------case when then else end------------------------

(case f.manualchargeflag when 1 then '是' else '否' end) as manualchargeflag1

----------------------------row_number() over()------------------------
在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by  order by 的执行。
partition by 用于给结果集分组.它和聚合函数不同的地方在于它能够返回一个分组中的多条记录

//例如：employee,根据部门分组排序
SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (partition by workdept ORDER BY salary desc) rank FROM employee

//对查询结果进行排序：（无分组）
SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (ORDER BY salary desc) rank FROM employee

-------------------------------------多级查询--------------------
找出分类及其所有子类（包含多级）
WITH RECURSIVE goodsclass (id,parent_id) as(select aa.id,aa.parent_id from pub_goods_class aa
 where aa.class_code in ('101','0')
 union all 
select aa.id,aa.parent_id from pub_goods_class aa 
inner join goodsclass pp on aa.parent_id = pp.id)
SELECT id FROM goodsclass