如何在cfml / coldfusion中模拟sql语句逻辑？ [英] How to mimic sql statement logic in cfml / coldfusion?

问题描述

我遇到一个问题，我试图将我的sql语句转换为在cfml（cfscript>）中执行相同的逻辑，因此，我试图模仿此sql语句以在cfscript中显示结果。有人可以帮我解决这个问题吗？

I Am coming to a problem where I am trying to convert my sql statement to do the same logic in cfml (cfscript>) so, I am trying to mimic this sql statement to display the results inside my cfscript. can somebody help me solve this issue? thanks for the help.

SQL：

    select * from myapp.GGG_myphone where department_name 
        like (select distinct department_name from myapp.GGG_myphone
         where department_nbr like '#DEPT_FUND_NBR#'  ) 

推荐答案

@Scott，我假设：

@Scott, I am assuming that:

1. 您选择查询中的每一行都代表一个类似的JSON到您显示的一个
   。毕竟，列名称与
   JSON中的键匹配。

1. Each row from your select query represents a JSON similar to the one you have shown. After all, the column names match the keys in the JSON.

您已经或打算拥有许多这样的键存储的JSON，每个
作为文件。我从您的文件读取代码中推断出这一点。假设
文件存储在名为 jsons的目录中。

You already have or intend to have a number of such JSONs stored, each as a file. I infer this from your file-read code. Let's say the files are stored in a directory called 'jsons'.

您要查找的是ColdFusion代码，它将选择与所选查询中的条件匹配的
JSON文件。

What you are looking for is ColdFusion code that will select the JSON files that match the criteria in your select query.

<cfscript>
array function getJSONByDeptName(string departmentNbr)  {
var JsonFiles = arrayNew(1); 
var JsonFile = "";
var JsonData = {};
var collectionOfMatchingJsonData = arrayNew(1); 
var departmentNamesList = "";

/* Here, we assume the JSON files are stored in subdirectory 'jsons' within current directory*/
JsonFiles = directorylist(expandPath('jsons'));

if (arrayLen(JsonFiles) gt 0) {
    for (var fileNumber=1; fileNumber lte arrayLen(JsonFiles); fileNumber=fileNumber+1) {
        /* Get each file in turn*/
        JsonFile = fileRead(JsonFiles[fileNumber]);

        /*Read its JSON content. The result is an array containing one item of type struct*/
        jsonData = deserializeJSON(JsonFile);

        /* Reminder: jsonData[1] is a struct. Check whether the departmentNbr key in the struct 
           matches the input value of departmentNbr. 
           If it does, add the jsonData to the list, avoiding duplicate values of departmentName */     
        if (jsonData[1].departmentNbr eq arguments.departmentNbr and ListFindNoCase(departmentNamesList, jsonData[1].departmentName) eq 0) {
            arrayAppend(collectionOfMatchingJsonData,jsonData)

            /* Add department name to list.  */
            departmentNamesList = listAppend(departmentNamesList, jsonData[1].departmentName);

        }

    }

}

return collectionOfMatchingJsonData;    
}

 // Test it, using departmentNbr '1982' 
 writedump(getJSONByDeptName('1982'));
 </cfscript>

这篇关于如何在cfml / coldfusion中模拟sql语句逻辑？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！