SQL经典语句

1，字段附加字符串更新、根据字段长度查询

update
    "user"
set
    identity_number = concat('null_', identity_number)
where
    length(identity_number)= 32;

 

2，递归查询部门层级关系

WITH RECURSIVE T(id,code) AS(
    SELECT id,code FROM department WHERE code ='xxxxx' and flag=0
    UNION ALL
    SELECT T1.id,T1.code
    FROM department T1 JOIN T ON T1.parent_id=T.id and T1.flag=0
    )select code from T;

 

3，查询父级部门code及其name路径

select
    string_agg(dt.name, '/' order by sort desc) as path,
    string_agg(dt.code, '/' order by sort desc) as code_path
from
    (with recursive departmentTree as (
    select
        d.*
    from
        department d
    where
        d.flag = 0
        and d.code = 'xxxxxx'
union all
    select
        d1.*
    from
        department d1
    join departmentTree as B on
        d1.id = B.parent_id)
    select
        departmentTree.name,
        departmentTree.code,
        row_number() over () as sort
    from
        departmentTree) dt;

 

4，针对某个字段进行排序，将null值额外处理

样例：对updateTime进行逆序排列，当updateTime为空时，使用createTime进行代替

select
    u.*
from
    "user" u
order by
    case
        when u.update_time is null then u.create_time
        else u.update_time
    end desc;

 

5，重置文本的格式

windows中的文本上传到linux运行报错

sed -i 's/^M//g' start.sh