cfoutput结果按年和月使用组？ [英] cfoutput results by year and month using a group?

问题描述

我想从具有此类结果的查询中输出结果，query = gettotalstars

I want to output results from a query that has results like this ,query=gettotalstars

我使用< cfoutput query =GetTotalStarsgroup =month>
但是重复所有的输出。
有没有另一种方法来做这个不使用'group'或者更好的方法来做呢？

I using <cfoutput query="GetTotalStars" group="month"> but this repeats the output for all. Is there another way to do this not using 'group' or maybe a better way to do it?

最后，我想要显示输出像这样:(显然从我有表的设置，现在它不会给我的结果，我想要的方式）

In the end i would like like it to show the output like this:(obviously from the way i have the table set up right now it wont give me the results the way i want it)

这里是现在使用的代码im： / p>

Here is the code im using right now:

<cfoutput query="GetTotalStars" group="month">   
<table cellpadding="0" cellspacing="0" class="tablesorter">  
  <thead><th></th><th>January</th><th>Frebruary</th>......</thead>  
  <tbody>  
  <cfloop query="GetTotalStars" >  
  <tr>  
  <td>#csedept_name#</td>  
  <td><div align="right">#TOTALSTARS#</div></td>  
  </tr>  
  </cfloop>  
  </tbody>  
  </cfoutput>  

  </table> 

推荐答案

以前的回应中有很多争议。我将尝试提出最简单的解决方案，基于你所展示的。如果您有多年的时间或者任何给定月份的空数据，您都必须自己解决一些问题。虽然试图带领你走向理解而不实际做所有的工作，我实际上给了你这个事实的一个确切的解决方案。您将需要调整和回退一些这些来处理数据中的漏洞或其他可能的边缘情况。总之，它应该引导你采取一种方法，你可以建立在处理这些其他的可能性。它使用查询的查询来挑逗标题行。我认为这是导致更复杂解决方案的部分。

Lots of controversy in the previous responses. I will try to present the simplest solution based on what you have shown. There are a number of issues that you will have to address for yourself if you have multiple years or if you have null data for any given month. While trying to lead you towards understanding without actually doing all the work, I have in fact given you an exact solution to this set of facts. You will need to adjust and back off some of this to handle holes in your data, or other possible edge cases. All in all, it should lead you to an approach that you can build on to handle these other possibilities. It does use a query of query to tease out the header row. I think that is the part that is leading to more complex solutions.

<cfscript>
    GetTotalStars = querynew(
        "dept_id,csedept_name,totalstars,year,month",
        "integer, varChar, integer, integer, integer",
        [
            {
            dept_id:1
            ,csedept_name:'department 1'
            ,totalstars:5
            ,year:2014
            ,month:5
            }
            ,{
            dept_id:1
            ,csedept_name:'department 1'
            ,totalstars:4
            ,year:2014
            ,month:6
            }
            ,{
            dept_id:2
            ,csedept_name:'department 2'
            ,totalstars:3
            ,year:2014
            ,month:5
            }
            ,{
            dept_id:2
            ,csedept_name:'department 2'
            ,totalstars:6
            ,year:2014
            ,month:6
            }
        ]
    );

writedump(GetTotalStars);
</cfscript>


<!---
    I agree with previous arguments about exessive use of query of queries.
    But for your header row (and to get a full list of possible months), you
    really need to pull that out of the original query in a single list or array or struct
--->

<cfquery dbtype="query" name="qryMonths">
    select distinct [month] from GetTotalStars order by [month]
</cfquery>


<cfoutput>

    <table border="1">
    <thead>
        <tr>
            <th>Department</th>

            <!---
                If you have gaps in the data, it might make sense to build an
                array here to keep track of what month is in what column
             --->
            <cfloop query="qryMonths">
                <th>#MonthAsString(qryMonths.month)#</th>
            </cfloop>
        </tr>
    </thead>
        <tbody>
            <!---
                This is just a straightforward nested query and group loop.
                If you have nulls in the data, you would check against the array
                that you built in the top section to figure out which column you
                are in
             --->
            <cfloop query="GetTotalStars" group="csedept_name">
                <tr>
                    <td>#GetTotalStars.csedept_name#</td>
                    <cfloop group="month">
                        <td>#GetTotalStars.totalstars#</td>
                    </cfloop>
                </tr>
            </cfloop>
        </tbody>
    </table>
</cfoutput>

这篇关于cfoutput结果按年和月使用组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！