postgres中几个复杂的sql语句

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中的查询函数非常神奇,只有你想不到的,没有写不了的。

时间: 2024-10-27 16:53:22

postgres中几个复杂的sql语句的相关文章

thinkphp中多表查询中防止数据重复的sql语句(必看)_php技巧

下面先来看看例子: table id name 1 a 2 b 3 c 4 c 5 b 库结构大概这样,这只是一个简单的例子,实际情况会复杂得多. select *, count(distinct name) from table group by name 结果: id name count(distinct name) 1 a 1 2 b 1 3 c 1 最后一 项是多余的,不用管就行了 tp2.0手册   搜索连贯操作 可看到相关的资料 SELECT cat_id, COUNT(*) AS

在java类中写带中文的sql语句,查询结果为空

问题描述 在java类中写带中文的sql语句,查询结果为空 String query="select * from Restaurant where Restaurant_name='紅樹林';"; rs=stm.executeQuery(query); 这是我在封装好的数据库类中写的sql语句,查询结果为空(rs.next() 为false ) 如果改用id查询,即: String query="select * from Restaurant where Restaura

查询mysql中执行效率低的sql语句的方法_Mysql

一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下:  # Set long query time to 8 seconds    long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如

oracle-Oracle中如何只用一条sql语句查询下面的例子

问题描述 Oracle中如何只用一条sql语句查询下面的例子 解决方案 select_statement UNION [ALL] selectstatement [UNION [ALL] selectstatement][-n]其中selectstatement为待联合的SELECT查询语句.用联合查询足以 解决方案二: 这个只能在后台代码判断并组装语句,然后联表查询 解决方案三: Oracle SQL语句查询例子用一条SQL语句查询分组前三名数据常用的sql语句查询例子

SQL Server中存储过程比直接运行SQL语句慢的原因

原文:SQL Server中存储过程比直接运行SQL语句慢的原因     在很多的资料中都描述说SQLSERVER的存储过程较普通的SQL语句有以下优点: 1.       存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度. 2.       经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作.当对数据库进行复杂操作时(如对多个表进行 Upd

一些工作和学习中经常用到的SQL语句

一些常用的SQL语句: 新建表: create table [表名] ( [自动编号字段] int IDENTITY (1,1) PRIMARY KEY , [字段1] nVarChar(50) default '默认值' null , [字段2] ntext null , [字段3] datetime, [字段4] money null , [字段5] int default 0, [字段6] Decimal (12,4) default 0, [字段7] image null , ) 新建表

Oracle中存储过程执行大的Sql语句时如何查看sql是否正确

  在Oracle中编写一个存储过程中,有时会用到较大的sql语句,但是通过PL/sql工具Test查看时又不能看到sql的样子,通过查看发现可以通过Oracle的物理临时表来处理, 在存储过程中添加DBMS_OUTPUT.put_line('要输出的Sql语句'); 然后在PL/sql工具中选中存储过程,右键选择Test执行,执行完成后可以通过DBMS_OUTPUT窗口查看sql结果了,很实用的

详解Java的Hibernate框架中的缓存与原生SQL语句的使用_java

Hibernate缓存缓存是所有关于应用程序的性能优化和它位于应用程序和数据库之间,以避免数据库访问多次,让性能关键型应用程序有更好的表现. 缓存对Hibernate很重要,它采用了多级缓存方案下文所述: 第一级缓存: 第一级缓存是Session的缓存,是一个强制性的缓存,通过它所有的请求都必须通过. Session对象不断自身的动力的对象,提交到数据库之前. 如果发出多个更新一个对象,Hibernate试图拖延尽可能长的时间做了更新,以减少发出的更新SQL语句的数量.如果您关闭会话,所有被缓存

显示同一分组中的其他元素的sql语句_MsSql

1.原始查询表结果  2.理想查询表结果   一很牛的朋友写的sql语句大笑,学习啦偷笑: select userpwd,username=stuff((select ','+username from tuser t1 where t1.userpwd=t.userpwd for xml path('')),1,1,'') from tuser t group by userpwd 说明: a.stuff方法(举例说明:select stuff('abcdef', 2, 3, 'klmn');