【经验积累】PGSQL中用到的一些语句

–多条记录中找出最新的那一条更新

UPDATE t_e_question t
   SET status = '4'
 WHERE t.questionid IN
       (SELECT c.questionid
          FROM (SELECT b.questionid,
                       row_number() over(PARTITION BY b.questioncode ORDER BY updatetime DESC) top
                  FROM t_e_question b
                 WHERE b.questioncode IN (SELECT a.questioncode
                                            FROM t_e_question a
                                           WHERE a.status = '1'
										                           GROUP BY a.questioncode
                                          HAVING COUNT(*) > 1)
                   AND b.status = '1') c
         WHERE top > 1);

–根据父id递归查子id记录

 WITH RECURSIVE S (themename,themeid,parentthemeid) AS (
        SELECT tmp.themename,tmp.themeid,tmp.parentthemeid
        FROM t_e_theme_information tmp
        where tmp.themeid = #{themeId}
        UNION ALL
        SELECT  x.themename,x.themeid,x.parentthemeid
        FROM t_e_theme_information  x JOIN s
        ON x.themeid = s.parentthemeid)
        SELECT string_agg(cc.themename,' > ') from  (SELECT  themename  FROM S ORDER BY themeid asc ) cc;

–子id查全路径

      WITH RECURSIVE S ( themeid,parentthemeid ) AS (
        SELECT
        tmp.themeid,tmp.parentthemeid
        FROM
        t_e_theme_information tmp
        WHERE
        tmp.themeid = #{themeIds} UNION ALL
        SELECT
        x.themeid,x.parentthemeid
        FROM
        t_e_theme_information x
        JOIN s ON x.themeid = S.parentthemeid
        ) select  string_agg( ff.themeid, ',') from  (SELECT s.themeid from s  ORDER BY themeid asc)  ff;


--排序
        WITH RECURSIVE S (themeid,parentthemeid,leavel) AS (
        SELECT tmp.themeid,tmp.parentthemeid,0 as  leavel
        FROM t_e_theme_information tmp
        where tmp.themeid =#{themeId}
        UNION ALL
        SELECT x.themeid,x.parentthemeid,S.leavel+1
        FROM t_e_theme_information x JOIN S
        ON x.themeid = S.parentthemeid)

       SELECT string_agg( themeid, ',') from (SELECT themeid FROM S ORDER BY leavel DESC ) aaa

–array_to_string ,ARRAY_AGG的聚合操作

SELECT
	TEMP_TABLE.courseName,
	TEMP_TABLE.contentno,
	AVG(TEMP_TABLE.scores) AS scores,
	count(TEMP_TABLE.userid),
	array_to_string(ARRAY(SELECT UNNEST (ARRAY_AGG((SELECT DISTINCT uif.realname from t_e_user_logininfo  uif where uif.userid =TEMP_TABLE.userid)))), ',') AS username
FROM	
  (SELECT
		C.NAME AS courseName,
		A.contentno AS contentno,
		AVG (CAST(A.RANK AS INT)) AS scores,
	
		A.createuserid AS userid
	FROM
		t_e_content_comment A,
		t_e_coursehour c
	WHERE
		A .replyuserid = ''
	AND A .commenttype = '12'
	AND A .contentno = C.coursehourid
	GROUP BY
	contentno,
	courseName,
	A.createuserid) AS TEMP_TABLE
GROUP BY 
TEMP_TABLE.courseName,
TEMP_TABLE.contentno;

SELECT 
 b.pkid as 序号,
 a.theme as 主题,
 (SELECT a3.realname from t_e_user_logininfo a3 where a3.userid =b.userid) as 参与人,
 (SELECT a4.orgname from t_e_org_edu a4 where a4.orgid =b.orgid) as  参与人学校,
 array_to_string(array(SELECT a2.name from t_e_coursehour_ext a1 ,t_e_coursehour a2 where a1.coursehourid =a2.coursehourid and a1.activityid =a.activityid and a2.creatorid=b.userid),',') as 课题名称
 
from t_e_research_activity a 
INNER JOIN   t_e_research_member b  on a.activityid =b.objectid
 where a.activityid ='200000000003';
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL ,可以使用多个子查询语句来构建更复杂的查询逻辑。子查询可以嵌套在其他查询,并且可以与其他查询语句组合使用,以实现更高级的数据操作和筛选。 下面是一个示例,展示了如何在 PostgreSQL 使用多个子查询语句: 假设我们有一个名为 "employees" 的表,其包含员工的信息,包括姓名、部门和工资。我们想要查询所有工资高于部门平均工资的员工,并按照工资从高到低排序。 ```sql SELECT * FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = ( SELECT department FROM employees WHERE name = 'John Doe' ) ); ``` 在上面的示例,我们使用了三个子查询语句。第一个子查询用于获取部门的平均工资,第二个子查询用于根据给定的名字查找部门,第三个子查询用于筛选出工资高于平均工资的员工。最终的结果集将包含满足条件的所有员工记录,按照工资从高到低排序。 请注意,这只是一个简单的示例,实际应用的子查询可能更加复杂。您可以使用不同的运算符(如比较运算符、逻辑运算符)和聚合函数来构建更高级的查询逻辑。此外,还可以使用连接(JOIN)和其他聚合操作来合并多个表的数据。 希望这个示例能帮助您理解如何在 PostgreSQL 使用多个子查询语句。如有需要,请提供更具体的问题或场景,我将尽力为您提供帮助。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值