首页 新闻 赞助 找找看

sql语句 临时表访问其 计数字段

0
悬赏园豆:20 [已关闭问题] 关闭于 2009-09-01 14:39
<pre><div class="cnblogs_code"><pre><img id="Code_Closed_Image_142208" onclick="this.style.display='none'; document.getElementById('Code_Closed_Text_142208').style.display='none'; document.getElementById('Code_Open_Image_142208').style.display='inline'; document.getElementById('Code_Open_Text_142208').style.display='inline';" src="/Images/OutliningIndicators/ContractedBlock.gif" alt="" width="11" height="16" align="top" /><img id="Code_Open_Image_142208" style="display: none" onclick="this.style.display='none'; document.getElementById('Code_Open_Text_142208').style.display='none'; getElementById('Code_Closed_Image_142208').style.display='inline'; getElementById('Code_Closed_Text_142208').style.display='inline';" src="/Images/OutliningIndicators/ExpandedBlockStart.gif" alt="" width="11" height="16" align="top" /><span id="Code_Closed_Text_142208" class="cnblogs_code_Collapse">Code</span><span id="Code_Open_Text_142208" style="display: none"><br /><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">into</span><span style="color: #000000;"> #a </span><span style="color: #0000FF;">from</span><span style="color: #000000;"> ServiceOrder so <br /></span><span style="color: #0000FF;">where</span><span style="color: #000000;"> so.comTime </span><span style="color: #808080;">&gt;=</span><span style="color: #FF00FF;">datediff</span><span style="color: #000000;">(</span><span style="color: #FF00FF;">day</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">,</span><span style="color: #FF00FF;">getdate</span><span style="color: #000000;">())</span><span style="color: #808080;">and</span><span style="color: #000000;"> so.OrderState </span><span style="color: #808080;">in</span><span style="color: #000000;"> (</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">6</span><span style="color: #000000;">) <br /></span><span style="color: #0000FF;">order</span><span style="color: #000000;"> </span><span style="color: #0000FF;">by</span><span style="color: #000000;"> so.comTime </span><span style="color: #0000FF;">asc</span><span style="color: #000000;"> <br /><br /></span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"> <br /></span><span style="color: #0000FF;">into</span><span style="color: #000000;"> #b </span><span style="color: #0000FF;">from</span><span style="color: #000000;"> ServiceOrder so <br /></span><span style="color: #0000FF;">where</span><span style="color: #000000;"> so.comTime</span><span style="color: #808080;">&lt;</span><span style="color: #FF00FF;">datediff</span><span style="color: #000000;">(</span><span style="color: #FF00FF;">day</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">,</span><span style="color: #FF00FF;">getdate</span><span style="color: #000000;">())</span><span style="color: #808080;">and</span><span style="color: #000000;"> so.OrderState </span><span style="color: #808080;">in</span><span style="color: #000000;"> (</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">,</span><span style="color: #800000; font-weight: bold;">6</span><span style="color: #000000;">) <br /></span><span style="color: #0000FF;">order</span><span style="color: #000000;"> </span><span style="color: #0000FF;">by</span><span style="color: #000000;"> so.comTime </span><span style="color: #0000FF;">desc</span><span style="color: #000000;"> <br /><br /> </span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;">,ROW_NUMBER() </span><span style="color: #0000FF;">over</span><span style="color: #000000;">(</span><span style="color: #0000FF;">order</span><span style="color: #000000;"> </span><span style="color: #0000FF;">by</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">) ) </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> rn <br /> </span><span style="color: #0000FF;">from</span><span style="color: #000000;"><br /> ( </span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"> </span><span style="color: #0000FF;">from</span><span style="color: #000000;"> #a </span><span style="color: #0000FF;">union</span><span style="color: #000000;"> </span><span style="color: #808080;">all</span><span style="color: #000000;"> </span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"> </span><span style="color: #0000FF;">from</span><span style="color: #000000;"> #b )</span><span style="color: #0000FF;">as</span><span style="color: #000000;"> yy <br /> </span><span style="color: #008080;">--</span><span style="color: #008080;"> where rn between 1 and 11</span><span style="color: #008080;"><br /></span><span style="color: #000000;"> </span><span style="color: #0000FF;">drop</span><span style="color: #000000;"> </span><span style="color: #0000FF;">table</span><span style="color: #000000;"> #a <br /> </span><span style="color: #0000FF;">drop</span><span style="color: #000000;"> </span><span style="color: #0000FF;">table</span><span style="color: #000000;"> #b </span></span></pre> </div> </pre> <p>注释起来的条件是&nbsp; 分页条件。但是这样访问是错误的。&nbsp; 前面加with&nbsp; ()也是不对。。。</p> <p>请问各位:那个计数的rn 这里怎么访问啊</p>
越~、。的主页 越~、。 | 初学一级 | 园豆:0
提问于:2009-09-01 14:26
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册