pg_sql常用查询语句整理

#pg_sql之增删改查

#修改：
inset into table_name
(id,
name,
age,
address
)
select
replace(old_id,old_id,new_id),#old_id字段的old_id数据，替换为new_id
replace(name,name,substring(concat(\'【新名字】\'),name),1,20)),#name字段替换为【新名字】+name
20,
address
from
    table_name
where
    id =\'201901\'

#增加
inset into table_name
(id,
name,
age,
address,
date
)
values(
\'201901\',
\'xiaoming\',
\'23\',
\'上海\',
TO_DATE(\'2006-03-01\',\'YYYY-MM-DD\'))

#更新
update table_name
    set id =3
    where id = 2 and name =\'xiaoming\'

#联表搜索 join on
mybatis注解多条件查询
@Selct({"<script>"+"查询语句"+"</script>"})
List<DemoBo> getListByForm(Page<DemoBo> page,DemoForm demoForm)
查询语句如下：

select
    A.id,
    B.name,
    A.address,
    A.age
from table_nameA as A
    left join table_nameB as B on A.id = B.id
where A.id = 0
    <if test \'id != null\'> and A.id <![CDATA[=]]> #{id}</if>
    <if test \'name != null\'> and A.name <![CDATA[=]]> #{name}</if>
order by A.age desc,A.id desc

#条件判断 case when
selct a.id ,a.name,a.age
    case when
        b.begindate = b.enddate
        then 1
        else 0
    end as f_date
from table_nameA as a
    inner join(
            selct id,
            count(*) as count,
            sum(case when age =2 then 1 else 0 end) as age
            from table_nameB
            group by id
        ) as b on a.id = b.id
where a.id =1232

 

版权声明：本文为smuxiaolei原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/smuxiaolei/p/10847385.html