只有两个字段用一个sql语句查询出某个学生的姓名、成绩以及在表中的排名
<p>昨天去面试时遇到一个这样的问题：</p>
<p>
有一张成绩表，只有两个字段，姓名和成绩。怎样用一个sql语句查询出某个学生的姓名，成绩以及在表中的排名？<br>
一时间我也想不出具体实现，我就提了两种思路：一种是通过join关联一个查询出他排名的sql语句；一种是通过group by来实现。<br>
回答得连自己都觉得有点心虚。请问大家如何实现呢？<br>
假设：表名字为Course，两个字段分别为name和score</p>
<p>
实现语句：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_57121">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">学生,成绩, </code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql color2">COUNT</code><code class="sql plain">(*) </code><code class="sql keyword">FROM</code> <code class="sql plain">表 </code><code class="sql keyword">WHERE</code> <code class="sql plain">a.成绩<=成绩) </code><code class="sql keyword">AS</code> <code class="sql plain">排名 </code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">FROM</code> <code class="sql plain">表 </code><code class="sql keyword">AS</code> <code class="sql plain">a</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
方法二：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_56712">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">declare</code> <code class="sql plain">@Course </code><code class="sql keyword">table</code><code class="sql plain">(</code><code class="sql keyword">name</code> <code class="sql keyword">varchar</code><code class="sql plain">(100),cj </code><code class="sql keyword">int</code><code class="sql plain">) </code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">@Course </code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">select</code> <code class="sql string">'a'</code><code class="sql plain">,99 </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">select</code> <code class="sql string">'b'</code><code class="sql plain">,66 </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">select</code> <code class="sql string">'c'</code><code class="sql plain">,88 </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">( </code>
</div>
<div class="line number9 index8 alt2">
<code class="sql keyword">select</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">1+</code><code class="sql color2">count</code><code class="sql plain">(</code><code class="sql keyword">name</code><code class="sql plain">) </code><code class="sql keyword">from</code> <code class="sql plain">@Course </code><code class="sql keyword">where</code> <code class="sql plain">cj>t.cj) mc,</code><code class="sql keyword">name</code><code class="sql plain">,cj </code>
</div>
<div class="line number10 index9 alt1">
<code class="sql keyword">from</code> <code class="sql plain">@Course t </code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">) tem </code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">where</code> <code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">'b'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
如果有成绩相同的记录的时候，goodspeed的语句确实有一些瑕疵，但是我根据他的语句稍微修改了一下就很完美了。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_790129">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">name</code><code class="sql plain">,score, </code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql color2">COUNT</code><code class="sql plain">(*)+1 </code><code class="sql keyword">FROM</code> <code class="sql plain">course </code><code class="sql keyword">WHERE</code> <code class="sql plain">a.score<score) </code><code class="sql keyword">AS</code> <code class="sql plain">sort </code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">FROM</code> <code class="sql plain">course </code><code class="sql keyword">AS</code> <code class="sql plain">a </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">sort</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
更多请到这里查看评论</p>
頁:
[1]