从查询中选择行范围 [英] Selecting A row range from a query of queries

问题描述

如何使用查询查询选择特定范围的行？

How would I select a specific range of rows using a query of queries?

例如

<cfquery name="myQuery" maxrows ="20" startrow="12">
 SELECT *
 FROM   previous_query
 WHERE  row_numer >= 12
</cfquery>

那种东西...

推荐答案

这是一个棘手的问题，但是您的问题引起了我的兴趣。我认为我可能有解决方案

This was a tricky one but your problem intrigued me. I think I may have a solution

我编写了一个函数，该函数删除所需行之前的所有内容，然后删除行之后的所有内容

I wrote a function that delete everything prior to the rows you want and then deletes everything after the rows you want.

函数rowrange（）具有3个参数。
1.您正在使用的查询名。
2.您想要的起始行
3.您想要的行数。

the function rowrange() takes 3 parameters. 1. the queryname you are working with. 2. the starting row you want 3. the number of rows you want.

更新：我的朋友John Whish指出我实际上无需执行循环即可使其正常工作。

UPDATED: My friend John Whish pointed out that I actually do not need to do the looping to get this to work. Removing the loops makes this much more scalable.

<cfquery name="myQuery">
 SELECT *
 FROM   previous_query
 WHERE  row_numer >= 12
</cfquery>


<cfset  rowRange(myQuery,7,4)>
<cfdump var="#myQuery#">

<cffunction name="rowRange" hint="return a range of rows from a given query">
    <cfargument name="qObj" type="query" required="true">
    <cfargument name="start" type="numeric" required="true" default="1" hint="The number of the first row to include">
    <cfargument name="range" type="numeric" required="true" default="1" hint="The number of rows">


    <cfset var i = arguments.start+arguments.range-1>
    <cfset arguments.qObj.removeRows(i,arguments.qObj.recordcount-i)>
    <cfset arguments.qObj.removeRows(0,arguments.start-1)>

    <cfreturn arguments.qObj>
</cffunction>

这篇关于从查询中选择行范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！