Queryappend的任何自定义函数

Question

Queryappend在Coldfusion - 18中，而不是Coldfusion - 16中。

有人能为"Queryappend“推荐一些自定义的coldfusoin函数吗？

假设我有两个查询：

Query-1
select * from user where userid > 10 order by userid asc

Query-2
select * from user where userid < 10 order by userid desc

Query append should return folowing:

userid username 
11  AA
12  BB
13  CC
9   MM
8   NN
7   OO

提前感谢

Answer 1

要在ColdFusion的早期版本中模拟queryAppend()，最简单、最具可读性的解决方案是使用查询查询(qoq)，然后使用union all特性附加生成的qoq，不提供ORDER BY子句。

<cfquery name="query1" datasource="mydatasource">
    select * from user where userid > 10 order by userid asc
</cfquery>

<cfquery name="query2" datasource="mydatasource">
    select * from user where userid < 10 order by userid desc
</cfquery>

<!--- "Union all" the 2 result sets together in a qoq and don't supply an order by clause  --->
<cfquery name="queryAppend" dbtype="query">
    select * from query1
    union all
    select * from query2
</cfquery>

下面是gist https://trycf.com/gist/484d3ab19f52d81867dacdced47fad09/lucee5?theme=monokai的工作示例

Answer 2

这里有一种方法可以做到这一点。

<cfscript>
IDList1 = '1,2,3';
IDList2 = '6,5,4';
NameList1 = 'Fred,Wilma,Pebbles';
NameList2 = 'Barney,Betty,Bam,Bam';
Flinstones = QueryNew("UserID,UserName", 'integer,varchar');
Rubbles = QueryNew("UserID,UserName", 'integer,varchar');

for (i = 1; i <=3; i++) {
QueryAddRow(Flinstones);
QuerySetCell(Flinstones, 'UserID', i);

QuerySetCell(Flinstones, 'UserName'
    , ListGetAt(NameList1, i));

QueryAddRow(Rubbles);
QuerySetCell(Rubbles, 'UserID'
    , ListGetAt(IDList2, i));

QuerySetCell(Rubbles, 'UserName'
    , ListGetAt(NameList2, i));
    
}
</cfscript>

<cfquery name="All" dbtype="query">
    select UserID, UserName
    from Flinstones
    order by UserID
</cfquery>

<cfloop query = "Rubbles">
    <cfset QueryAddRow(All)>
    <cfset QuerySetCell(All, 'UserId', UserId,
        All.recordcount)>
        <cfset QuerySetCell(All, 'UserName', UserName,
        All.recordcount)>
    
    
</cfloop>

<cfdump var="#all#">

你可以玩玩它，here。

将此逻辑放入一个接受两个查询并返回第三个查询的函数中并不难。