mysql - CFQuery where 子句与查询结果的比较

Question

我有一个返回 7 条记录的查询:

<cfquery name="equip" datasource="#datasource#">
select equipment_id from service_ticket
where equipment_id > 0
</cfquery>

然后我获取这些结果并再次运行另一个查询:

<cfquery name="get_equipment_tally" datasource="#datasource#">
select *
from equipment_maintenance
where active = "yes" AND id <> #equip.equipment_id#
</cfquery>

但是查询“get_equipment_tally”仅将“id”与where子句中的查询结果“equip”中的第一条记录进行比较，即使返回了七个记录。

有办法解决这个问题吗？

Answer 1

AND id NOT IN <cfqueryparam cfsqltype="cf_sql_integer" 
    value="#valueList(equip.equipment_id)#" list="true">

理想情况下，您可以将其转换为一个查询

<cfquery name="get_equipment_tally" datasource="#datasource#">
select *
from equipment_maintenance
where active = "yes" AND id NOT IN (select equipment_id  from service_ticket where equipment_id > 0)
</cfquery>