从SQL迁移到API时CFML代码逻辑不符问题求助

问题背景 #

我正在把原来的SQL查询逻辑迁移到API调用（现在用JSON文件模拟数据），目标是复刻原有SQL的行为，但现在的CFML代码会返回所有的job status、job class和pay grades组合。我的需求是：当某个job_class（比如0707）从数据源中被删除时，页面能显示这个缺失的job_class；如果没有任何缺失，就返回空结构体。

原有SQL逻辑 #

原来的SQL是获取所有Active状态的唯一job_class和PAY_GRADE组合：

select distinct job_class, PAY_GRADE 
from app.my_databaseFone 
where job_status like 'Active' 
order by job_class

现有CFML代码问题分析 #

先看你当前的代码：

<cffunction name="my_api" access="private" description="my api data"> 
    <cfset qFullmyApi = fileRead("C:\Users\Desktop\myjson.json")> 
    <cfset jsonData = deserializeJSON(qFullmyApi) /> 
    <cfscript> 
        myAPIOutput = {}; 
        for (item in jsonData) { 
            if (structKeyExists(myAPIOutput, item.jobStatus)) { 
                matchedValue = false; 
                for (i=1; i <= arrayLen(myAPIOutput[item.jobStatus]); i=i+1) { 
                    if (item.jobClass == myAPIOutput[item.jobStatus][i].job_class && item.payGrade == myAPIOutput[item.jobStatus][i].pay_grade) { 
                        matchedValue = true; 
                        break; 
                    } 
                } 
                if (matchedValue == false) { 
                    arrayAppend(myAPIOutput[item.jobStatus], {"JOB_CLASS":item.jobClass, "PAY_GRADE": item.payGrade}); 
                } 
            } else { 
                myAPIOutput[item.jobStatus] = [{"JOB_CLASS":item.jobClass, "PAY_GRADE": item.payGrade}]; 
            } 
        } 
        return myAPIOutput; 
    </cfscript> 
</cffunction>

这段代码的逻辑是把JSON里所有jobStatus对应的jobClass+payGrade组合去重后收集起来，和你要的「检测缺失」完全不搭边——你需要的是对比「基准数据（原来SQL返回的Active状态的组合）」和「当前JSON数据」，找出基准里有但JSON里没有的条目。

修正后的代码实现 #

要实现需求，我们需要分两步：

1. 先获取基准数据（也就是原来SQL查询的结果，这里如果是从数据库拿就执行SQL，如果你是用JSON作为基准，就需要定义这个基准列表）
2. 遍历基准列表，检查每个组合是否存在于当前JSON数据中
3. 收集缺失的条目，没有缺失则返回空结构体

下面是修正后的CFML代码：

<cffunction name="my_api" access="private" description="Detect missing job_class/pay_grade from Active status"> 
    <!-- 1. 获取基准数据：执行原有SQL获取Active状态的标准组合 -->
    <cfquery name="qBenchmark" datasource="your_datasource">
        select distinct job_class, PAY_GRADE 
        from app.my_databaseFone 
        where job_status like 'Active' 
        order by job_class
    </cfquery>
    
    <!-- 2. 读取当前JSON数据源 -->
    <cfset qFullmyApi = fileRead("C:\Users\Desktop\myjson.json")> 
    <cfset jsonData = deserializeJSON(qFullmyApi) /> 
    
    <cfscript> 
        // 把当前JSON里的Active状态组合存为结构体键，方便快速查找
        activeCurrentEntries = {};
        for (item in jsonData) {
            if (item.jobStatus == "Active") {
                // 用jobClass+payGrade拼接成唯一标识键
                key = "#item.jobClass#_#item.payGrade#";
                activeCurrentEntries[key] = true;
            }
        }
        
        // 3. 对比基准和当前数据，收集缺失条目
        missingEntries = [];
        for (row in qBenchmark) {
            benchmarkKey = "#row.job_class#_#row.PAY_GRADE#";
            if (!structKeyExists(activeCurrentEntries, benchmarkKey)) {
                arrayAppend(missingEntries, {
                    "JOB_CLASS": row.job_class,
                    "PAY_GRADE": row.PAY_GRADE,
                    "STATUS": "Missing"
                });
            }
        }
        
        // 4. 按需求返回：有缺失则返回缺失列表，无缺失返回空结构体
        return arrayLen(missingEntries) > 0 ? {"missing_jobs": missingEntries} : {};
    </cfscript> 
</cffunction>

代码细节说明 #

• 基准数据替换：如果你的基准不是来自数据库，而是固定的组合列表，可以把cfquery部分换成预定义数组，比如：

  qBenchmark = [
      {"job_class": "0707", "PAY_GRADE": "G1"},
      {"job_class": "0808", "PAY_GRADE": "G2"}
  ];
  

• 查找效率优化：用结构体存储当前存在的组合，比循环数组判断存在性的效率高很多，数据量大的时候优势更明显。
• 返回逻辑严格对齐需求：完全按照你要的逻辑，无缺失返回空结构体，有缺失则明确列出缺失的job_class和pay grade。

内容的提问来源于stack exchange，提问作者user10008857