<p><del>我在sql server上实现了这一点,但在postgresql上实现了同样的功能。</del>我现在包括了这两种功能。你知道吗</p>
<ul>
<li>检查<a href="https://www.postgresql.org/docs/9.5/static/tutorial-window.html" rel="nofollow noreferrer"><strong>Rank()</strong></a>函数,了解如何处理关系或使用<code>row_number()</code></li>
<li>使用变量而不是<code>'user3'</code></li>
</ul>
<p><strong><a href="http://sqlfiddle.com/#!15/77fcb/3" rel="nofollow noreferrer">Postgres Demo</a></strong><br/>
<strong><a href="http://rextester.com/JCUH42932" rel="nofollow noreferrer">SQL Fiddle Demo</a></strong></p>
<pre><code>WITH playerPoint as (
SELECT "points"
FROM Table1
WHERE "user" = 'user3'
)
SELECT *
FROM (
SELECT *
FROM (
SELECT T."user",
T."points",
CASE WHEN T."points" = P."points"
THEN '*****'
ELSE ''
END as Selected,
RANK() OVER (ORDER BY T."points" DESC) ranking
FROM Table1 T
CROSS JOIN playerPoint P
WHERE T."points" <= P."points"
UNION
SELECT T."user",
T."points",
CASE WHEN T."points" = P."points"
THEN '*****'
ELSE ''
END as Selected,
RANK() OVER (ORDER BY T."points" ASC) ranking
FROM Table1 T
CROSS JOIN playerPoint P
WHERE T."points" >= P."points"
) R1
ORDER BY ranking
LIMIT 5
) R2
ORDER BY POINTS
</code></pre>
<p><strong>输出</strong></p>
<p><a href="https://i.stack.imgur.com/m8GVW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/m8GVW.png" alt="enter image description here"/></a></p>
<p>在用<code>user8</code>将<code>DESC</code>加到最后<code>ORDER BY</code>之后</p>
<p><a href="https://i.stack.imgur.com/2gseP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2gseP.png" alt="enter image description here"/></a></p>