pgsql

1.in

select * from test where name in ( '张三','李四 )；

2. cast('' as jsonb)

select cast(jsonb_array_elements(ip) as varchar) as source_ip, count(source_ip) as num \n" +
            "from test1 (network <> CAST('{}' AS jsonb )) group by sourceIp ORDER BY num desc limit 8 

3.按字母排序

SELECT * from city_config order by convert_to(city_name,'GBK') desc limit 10 offset 0;

4.第一种取第一个jsonb的第一个元素作为varchar类型

第二种是ip 是否包含这个作为ip的json类型

and CAST(ip::jsonb->>0 AS varchar) in ('10.2.1.1' )

and (ip @> CAST ( ' "10.2.1.1" ' AS jsonb )) 注意：因为库里 是 ["192.168.1.2","192.16.12.36"]

CAST(ip::jsonb->>0 AS varchar) in (?1)

(ip @> CAST ( ?1 AS jsonb ))

5.对于等级

SELECT
    COUNT (1) COUNT,
    COUNT (CASE WHEN level = 4 THEN 1 END) critical,COUNT (CASE WHEN level = 3 THEN 1 END) high,COUNT (CASE WHEN level = 2 THEN 1 END) middle ,COUNT (CASE WHEN level = 1 THEN 1 END) low
FROM
    test