postgres中几个复杂的sql语句
需求一
需要获取一个问题列表，这个问题列表的排序方式是分为两个部分，第一部分是一个已有的数组[0,579489,579482,579453,561983,561990,562083] 第二个部分是按照id进行排序，但是需要过滤掉前面已有的数组。
最开始的时候我大概是想这么写的：
select * from question where id = any(
array_cat(
ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[],
(select array(
select id from question where id not in
(0,579489,579482,579453,561983,561990,562083)
and status in (1, -1)
and created_at > 1426131436 order by id desc offset 0 limit 10 )
)::integer[]
)
)
这里用了个字查询来查找问题，然后和array做一个链接。但是发现这个 = any 返回的结构并不是按照我想要的排序。有没有办法让 = any 按照指定顺序返回呢？后来经过别人帮忙，将sql语句改成：
select * from question q join (
select * from unnest(
array_cat( ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], (
select array(
select id from question where id not in (0,579489,579482,579453,561983,561990,562083) and status in (1, -1) and created_at > 1426131436 order by id desc offset 0 limit 10
)
)::integer[] )
) WITH ORDINALITY as ids(id, rn)
) as tmp on q.id = tmp.id order by tmp.rn
这里主要有几个函数：
一个是unnest函数，是将一个array变成一个多行的子查询结果。
一个是WITH ORDINALITY，这个函数是只在pg9.4中才增加的函数，和unnest一起使用能返回对应的数组和在数组中的排序。
需求二
现在有个表，有个字段是content，content 里面存储的是双层json，即
{"title": "testtest", "content": "{"id":23,"qid":580585,
"content":"\u8fd9\u4e2a\u662f\u8ffd\u95ee"}"}
现在我要获取按照解析后的qid进行排序分页的结构。
使用了json ->> 符号
语句实现如下：
select a.question_id, max(is_read) as is_read from (
select id, is_read, (content::json->>'content')::json->>'qid' as question_id
from inbox where receiver=1
) a group by a.question_id order by a.question_id desc offset 0 limit 10
这里的json->>直接使用了两层解析结构。
总结
pgsql中的查询函数非常神奇，只有你想不到的，没有写不了的。