主页 / 问题 / 682049
Accepted
Pantera
Asked:2020-06-21 21:23:50 +0000 UTC2020-06-21 21:23:50 +0000 UTC

对 PostgreSQL 数据进行排序

  • 772

我如何编写查询以获得 3sname的type=0另一个 3stype=1和 3stype=2总共 9 name。我只输出type=0. 发誓UNION ALL。

Select forum_thread.name, forum_thread.updatedat, forum_thread.threadtype
From forum_thread, business
Where business.id = 1 AND forum_thread.threadtype=0
Order by forum_thread.threadtype, forum_thread.updatedat desc limit 3
UNION ALL
Select forum_thread.name, forum_thread.updatedat, forum_thread.threadtype
From forum_thread, business
Where business.id = 1 AND forum_thread.threadtype=1
Order by forum_thread.threadtype, forum_thread.updatedat desc limit 3
UNION ALL
Select forum_thread.name, forum_thread.updatedat, forum_thread.threadtype
From forum_thread, business
Where business.id = 1 AND forum_thread.threadtype=2
Order by forum_thread.threadtype, forum_thread.updatedat desc limit 3
postgresql

1 个回答

  1. Best Answer
    select *
  from (
   Select forum_thread.name, forum_thread.updatedat, forum_thread.threadtype,
          row_number() over(patition by forum_thread.threadtype Order by forum_thread.threadtype, forum_thread.updatedat desc) RN
     From forum_thread, business
    Where business.id = 1 AND forum_thread.threadtype in(0,1,2)
  ) X
 where RN<=3
 Order by threadtype, updatedat desc

    如果由于某种原因这比你的 3-union 解决方案慢，只需将子查询括在括号中(select ... limit 3) UNION ALL (select ... limit 3)

    • 2

相关问题