1. 一个用逗号隔开的id字符串内，怎样用where in查找某个指定id：

    1
    2
    3
    4
    5
    6
    --将用逗号隔开的id字符串转为可供where in使用的数组
    --写法1：
    select * from table where '目标id' in any(string_to_array(id_list,','))

    --写法2：
    select * from table where id::text in (select unnest((string_to_array(id_list,','))))

  2. 时间类型用法：

    1
    2
    3
    4
    5
    6
    7
    --已有两个月初的日期，展示这两个日期之间所有月的月初：
    select generate_series(start_date,end_date,'1 month'::interval) from (
    	select start_date,end_date from table
    ) t

    --日期字符串转特定格式
    select to_timestamp('2023-10-01 21:30:00','YYYY-MM-DD');

  3. REGEXP_SPLIT_TO_TABLE:行转列，string_agg:列转行

    1
    2
    3
    4
    5
    6
    --将id字段的每个值转为某字符隔开的一个字符串：
    select string_agg(id,'-' order by id::text) as ids from table;--string_agg带order by
    select string_agg(id,';') filter (where id<>'abc') from table;--string_agg带filter

    --将一个用斜杠隔开的id字符串转为列分别展示每个id：
    select * from REGEXP_SPLIT_TO_TABLE((select ids from table), '/')--这个查询可以作为in条件

  4. json/jsonb类型用法：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    --json/jsonb内可以是对象数组，也可以是对象。

    --添加/修改jsonb属性：第四个参数，如果不填表示update，如果值为true表示如果没有该属性则增加，有则更新
    UPDATE table SET attr = jsonb_set(COALESCE(attr, '{}'::jsonb), '{property}','"content"', true) WHERE id = 123

    --移除某一属性
    update table set attr=attr::jsonb - 'property' where id=123;

    --读取整个jsonb单元格：
    select attr::jsonb from table

    --读取jsonb（对象）某一属性
    select attr->'property' as prop from table;--读出了属性值原本的数据类型
    select attr->>'property' as prop from table;--将属性值转为字符串

    --将对象数组中的每个对象转变为多行（json中的行转列）：
    SELECT (jsonb_array_elements_text(attr->'property'))::jsonb as row from table--attr是对象，attr中属性property的值是数组

    --从对象数组中取出每一对象内的某一属性，归类合并在同一行里（json中的先行转列，再列转行）：
    select string_agg(row->>'prop1',',') as ids from (--从对象数组property中获取每一组的属性prop1的值，组成逗号隔开的字符串
    	select (jsonb_array_elements_text(attr->'property'))::jsonb as row from table where id=123--attr是对象，attr中属性property的值是对象数组
    ) t

  5. 序号自增

    1
    select ROW_NUMBER() over (order by id) as no from table

  6. sql查询为空时如何显示一行空记录：

    1
    2
    3
    4
    5
    (
    	select a,b,c from table where a='aaa' and b='bbb' and c=999
    	union all
    	select null as a, '' as b, 0 as c from table
    ) limit 1

  7. 按中文拼音排序：

    1
    order by convert_to(column,'GBK')