一、并集、交集、差集
1.1并集,分union和union all两种情况
union 会去重
示例一:
select 1
union all
select 1
只输出一个1:
1
union all 不会去重
示例二:
select 1
union all
select 1
输出两个1:
1
1
1.2交集 intersect
示例:select 1
intersect
select 1
输出:1
1.3差集 except
示例:
select 2
except
select 2
输出 null ,由于两个查询结果一样,因此没有差异点
二、JSON和Array常用函数
2.1 unnset()函数,将输入的数组转换成一个表,每行一个元素
示例:select unnest(array['1', 'V'])
2.2 json_array_elements()和json_array_elements_text()
json_array_elements :展开JSON数组的每个JSON元素,展开之后每个元素值还是JSON
json_array_elements_text:展开JSON数组的每个JSON元素,展开之后每个元素值是text
示例: select json_array_elements('[{"name":"zs"}, {"name":"lisi"}]'::json)
由于展开之后每个元素是json,因此还可以获取name的值
select
field1 ->> 'name' as name
from
(
select json_array_elements('[{"name":"zs"}, {"name":"lisi"}]'::json) as field1
) as t1
json_array_elements_text()使用示例
select
field1::json ->> 'name' as name -- 可以将text转为json之后获取name
from
(
select json_array_elements_text('[{"name":"zs"}, {"name":"lisi"}]'::json) as field1 -- 注意:json_array_elements_text平铺的每个元素是text
) as t1
2.2 PG中构建数组,可以使用array[元素1,元素2]来构建
select array[1,2,3]
2.3 json中使用->和->>取值的区别
->> 取值结果类型是文本
-> 取值结果类型是json
2.4 数组取并集、交集、差集
方案一:可以使用unnest()将数组转为多行,然后使用union/intersect/except取并集/交集/差集
示例:数组交集
SELECT UNNEST(ARRAY[1, 2, 3])
INTERSECT -- 可以修改为intersect/except取交集/差集
SELECT UNNEST(ARRAY[1, 2])
方案二:使用数组的函数来实现
数组并集示例
-- 链接函数,依然不会去重
select array_cat(ARRAY[1, 2], ARRAY[2, 3])
-- && 重叠,是否有共同元素,结果返回true/false
select ARRAY[1, 2, 3] && ARRAY[1, 2]