【经验积累】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';