使用Coldfusion循环查询

Question

我有一个表‘分数’，其中包含一个companyID加上一些相关的分数。companyID可以在此表中多次出现。我有另一个包含companyID和companyName的表('company')。我想要做的是查询'company‘表以获得所有的companyID，然后使用这个结果来查询’score‘表中的分数。我也需要做一些计算。我想我需要遍历第一个结果集，并将companyID值放入第二个查询中。只是不确定该怎么做。任何帮助都将不胜感激。

我知道这段代码不会工作，但它可能会让你对我想要完成的事情有一个感觉：

<cfquery name="companyIDs" datasource="myDB">
   Select companyID from company
</cfquery>

<cfloop>
 <cfquery name="companyScore" datasource="myDB">
 SELECT Round(sum(leadership)/Count(leadership)) as leadership,   
 Round(sum(communication)/Count(communication)) as communication, 
 Round(sum(fairness)/Count(fairness)) as fairness, 
 Round(sum(ethics)/Count(ethics)) as ethics, 
 Round(sum(competence)/Count(competence)) as competence 
 FROM scores 
 Where companyID = 'companyIDs.companyID'
 </cfquery>
</cfloop>

比

Answer 1

有更好的方法来完成你需要的东西，但我已经回答了你的问题。我对你的代码做了一些修改：

<cfloop query='companyIDs'> // add the name of your query to the cloop
 <cfquery name="companyScore" datasource="myDB">
 SELECT Round(sum(leadership)/Count(leadership)) as leadership,   
 Round(sum(communication)/Count(communication)) as communication, 
 Round(sum(fairness)/Count(fairness)) as fairness, 
 Round(sum(ethics)/Count(ethics)) as ethics, 
 Round(sum(competence)/Count(competence)) as competence 
 FROM scores 
 Where companyID = '#companyIDs.companyID#' // add ## around your output
 </cfquery>

<cfdump var='#companyScore#'>  // dump your results

</cfloop>

Answer 2

有几种方法可以做到这一点，但下面的方法将会起作用。

<cfquery name="companyIDs" datasource="myDB">
Select companyID from company
</cfquery>

<cfloop query="companyIDs">
 <cfquery name="companyScore" datasource="myDB">
 SELECT Round(sum(leadership)/Count(leadership)) as leadership,   
 Round(sum(communication)/Count(communication)) as communication, 
 Round(sum(fairness)/Count(fairness)) as fairness, 
 Round(sum(ethics)/Count(ethics)) as ethics, 
 Round(sum(competence)/Count(competence)) as competence 
 FROM scores 
 Where companyID = #companyIDs.companyID#
 </cfquery>
</cfloop>

您也可以将两个查询连接在一起，因此您只有一个整体查询，这将是首选的方式。

<cfquery name="companyScore" datasource="myDB">
 SELECT companyID,
 Round(sum(leadership)/Count(leadership)) as leadership,   
 Round(sum(communication)/Count(communication)) as communication, 
 Round(sum(fairness)/Count(fairness)) as fairness, 
 Round(sum(ethics)/Count(ethics)) as ethics, 
 Round(sum(competence)/Count(competence)) as competence 
 FROM scores INNER JOIN company ON scores.companyID = company.companyID
 GROUP BY companyID
 </cfquery>