在超过 2100 个值上选择 IN [英] Select IN on more than 2100 values

问题描述

如何对超过 2100 个值进行选择?

How can you do a select in on more than 2100 values?

<cfquery name="result.qryData">
  SELECT    sub_acct_no,  ...
  FROM  dbo.Closed_ORDER
  WHERE ord_no IN <cfqueryparam cfsqltype="CF_SQL_varchar" value="#ValueList(qryOrd.ord_no)#" list="yes">
</cfquery>

由于表的设置方式，链接服务器和联接不是一个选项.

Because of the ways that the tables are setup, linked Servers and JOINS are not an option.

运行时会抛出一个错误，因为传入了许多新字段.

When this is ran an error this thrown because there are new many fields being passed in.

推荐答案

首先将值加载到 XML 中

First load the values into XML

<cfset var strResult = '<ul class="xoxo">'>
<cfloop query="qryOrd">
    <cfset strResult &= '<li>#xmlformat(ord_no)#</li>'>
</cfloop>
<cfset strResult &= '</ul>'>

然后在sql查询中使用xml

Then use the xml in the sql query

<cfquery name="result.qryData">
DECLARE @xmlOrd_no       xml = <cfqueryparam cfsqltype="CF_SQL_varchar" value="#strResult#">


DECLARE @tblOrd_no          TABLE (ID varchar(20))


INSERT INTO @tblOrd_no
SELECT tbl.Col.value('.', 'varchar(20)')
FROM    @xmlOrd_no.nodes('/ul/li') tbl(Col)


SELECT  sub_acct_no,  ...
FROM    dbo.Closed_ORDER
WHERE   ord_no IN (SELECT ID FROM @tblOrd_no)
</cfquery>

您还可以转储 XML，它在 HTML 中的格式正确

You can also do a dump of the XML and it is properly formatted in HTML

 <cfoutput>#strResult#</cfoutput>

这篇关于在超过 2100 个值上选择 IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！