CF SQL创建具有不同结果的表 [英] CF SQL Creating a Table with different results

问题描述

我正在尝试使用Coldfusion和sql创建一个表。我尝试创建的表如下所示：

I am trying to create a table using coldfusion and sql. The table I am trying to create looks like this:

<cfquery datasource="#application.dsn#" name="someprocessTable">
    SELECT *
    FROM checklists
</cfquery>

<table id="Checklist_Stats">
    <thead>
        <th><b>Associate Name</b></th>
        <th><b>Location</b></th>
        <th><b>Checklists Generated by Associate</b></th>
        <th><b>Checklists Generated by Selected Location(s)</b></th>
        <th><b>Associate Percentage of Location Total</b></th>   
    </thead>
    <tbody>
        <cfoutput query="someprocessTable">                     
            <tr>
                <td>#associate#</td>
                <td>#location_code#</td>
                <td>#associate.recordcount#</td>
                <!---<td>##</td>
                <td>##</td>--->
            </tr>                      
        </cfoutput>
    </tbody>
</table>

我不确定的部分是如何将所有这些信息循环放在一个表下？因为您不希望在表上重复出现相同的人名，然后又显示出他们产生了多少人，因为我无法执行＃associate.recordcount＃

The part I am unsure about is how do I loop all of this information under one table? Because you would not want to have the same persons name keep reoccurring on the table and then how do you show how many was generated by them since I could not do something like #associate.recordcount#

推荐答案

似乎有不止一种方法可以完成您想要实现的目标，例如对联接和组进行查询转储在桌子上;使用单个CFoutput管理您的输出，或使用嵌套的CFOutput和/或CFloop。
以下显示了第三种方法：

There seems more than one way to do what you want to achieve like doing a query with joins and groups then dump in table; manage your output with a single CFoutput or use nested CFOutput and/or CFloop. Following show third approach:

<table border="1" id="Checklist_Stats">
    <thead>
        <th><b>Associate Name</b></th>
        <th><b>Location</b></th>
        <th><b>Checklists Generated by Associate</b></th>
        <th><b>Checklists Generated by Selected Location(s)</b></th>
        <th><b>Associate Percentage of Location Total</b></th>   
    </thead>
    <tbody>
    <cfquery name="allAssociatesQry" dbtype="query">
        SELECT DISTINCT associate, COUNT(*) AS associateCount FROM someprocessTable GROUP BY associate ORDER BY associate 
    </cfquery>
    <cfloop query="allAssociatesQry">
        <cfquery name="allLocCodeForAssociateQry" dbtype="query">
            SELECT * FROM someprocessTable WHERE associate='#associate#' ORDER BY location_code
        </cfquery>
        <tr><td><cfoutput>#allLocCodeForAssociateQry.associate#</cfoutput></td>
        <cfoutput query="allLocCodeForAssociateQry" group="location_code">
            <cfset locCntr = 0 />
            <cfoutput>
                <cfset locCntr = locCntr + 1 />
            </cfoutput>
            <cfif allLocCodeForAssociateQry.currentRow NEQ 1>
                <tr><td>&nbsp;</td>
            </cfif>
                <td>#allLocCodeForAssociateQry.location_code#</td>
                <td>#allAssociatesQry.associateCount#</td>
                <td>#locCntr#</td>
                <td>#Round((locCntr/allAssociatesQry.associateCount) * 100)#%</td>
            </tr>                      
        </cfoutput>
    </cfloop>
    </tbody>
</table>

请注意，CF QoQ区分大小写，因此如果需要，请转换关联名称和位置

Please note that CF QoQ is case sensitive so if need be then convert associate name and location to lower/upper/title case before hand

稍微修改一下CFloop的代码可能如下：

A slightly modified code for the CFloop may be like below:

<cfloop query="allAssociatesQry">
    <cfset thisAssociateName = trim(allAssociatesQry.associate) />
    <cfquery name="allLocCodeForAssociateQry" dbtype="query">
        SELECT location_code,count(location_code) AS locCntr FROM someprocessTable WHERE associate='#thisAssociateName#' GROUP BY location_code ORDER BY location_code
    </cfquery>
    <cfoutput query="allLocCodeForAssociateQry">
        <tr>
            <td>#thisAssociateName#</td>
            <td>#allLocCodeForAssociateQry.location_code#</td>
            <td>#allAssociatesQry.associateCount#</td>
            <td>#allLocCodeForAssociateQry.locCntr#</td>
            <td>#Round((allLocCodeForAssociateQry.locCntr/allAssociatesQry.associateCount) * 100)#%</td>
        </tr>
        <cfset thisAssociateName = "" />
    </cfoutput>
</cfloop>

这篇关于CF SQL创建具有不同结果的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！