PostgreSQL 常用函数（持续更新）

基础知识

SQL语句的执行顺序：FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY、LIMIT
圆括号 > and > or

concat_ws(分隔符，value1, value2...)：几个值合并成一条
string_agg([distinct] 字段, 分隔符)：多行聚合成一个字段
to_char(日期， 日期格式)：日期转换为指定格式字符串
to_date(日期， 日期格式)：字符串转换为指定格式日期

UNION 和 UNION all 的区别：前者

and 和 or 的混合使用

-- 先执行B = 'b'  and C = 'c' ，再加上A = 'a'的并集
where A = 'a' or B = 'b' and C = 'c' 

-- 等同于A = 'a' and B = 'b' 和 A = 'a' and C = 'c'的并集
where A = 'a' or ( B = 'b' and C = 'c' )

HAVING

GROUP BY id_card HAVING count(*) >1

空字符串和null
一般过滤掉空最好是 A is not null and A <> ''

常用sql

With用法

作为临时表，把复杂sql简单化

-- 方式一
with t1 as (
	select a, b, c from table1 where ...
	group by ...
	union all
	select a, b, c from table2 where ...
	group by ...
)
select a, b, c from a where ...
group by ...

方式二
with t1 as (
	select a, b, c from table1 where ...
	group by ...
), 
with t2 as (
	select a, b, c from a where ...
	group by ...
)
select ... from t1, t2 where ...

with递归查询

with RECURSIVE t1 as (
	select * from table1 where id = ''
	union all
	select t2.* from table1 t2, t1 where t2.parent_id = t1.id
)
select * from t1 
order by id

case when用法

1. 作用于查询字段，最好字段少
2. 作用于分组统计
3. 作用于排序

-- 修改查询结果
select 
	case
		when A = 'a' then 'aa'
		when A = 'b' then 'bb'
		when A <> '' then A
	end A1,
	B
from table1
where ...

-- 分组
select 
	case
		when A = 'a' then 'aa'
		when A = 'b' then 'bb'
	end A1,
	B, count(1)
from table1
where ...
group by B, 
case
	when A = 'a' then 'aa'
	when A = 'b' then 'bb'
end

-- 排序
select A, B
from table1
where ...
order by 
case 
	when A like 'a%' then 1
	when A like 'b%' then 2
end, A asc

row_number() over

row_number() over(partition by 分组列 order by 排序列 desc)
用处：对查询结果进行排序
执行顺序：over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行

-- 获取分组后的row值作为条件，得出A, B分组后的第一位，实际未group by
select * from (
	select A, B, 
		row_number() over(partition by A, B order by C desc) tmp
	from table1
	where ...
)t where tmp = '1'