mysql - 行由于分组依据而倍增

我正在查询几个表，以获取问题的每个答案的计数。在这里，我所做的是采取调查，我们可能会问一些问题，一些答案选项完成调查后，我们需要得到一个调查的统计数字，然后我们需要计数的每一个答案的问题。
这是我的问题。

SELECT s.NAME AS surveyname,
    COUNT(r.answer_id) AS totalAnswer,
    q.id AS questionid,
    q.question AS question,
    a.answer AS answer,
    COUNT(r.textbox) AS totalTextbox,
    COUNT(r.textboxmulti) AS totalTextboxmulti,
    qt.template AS template,
    s.NAME AS surveyname,
    COUNT(r.other) AS other
FROM surveys s
INNER JOIN survey_results AS sr
    ON s.id = sr.survey_id
INNER JOIN results AS r
    ON sr.id = r.surveyresults_id
INNER JOIN questions AS q
    ON r.question_id = q.id
INNER JOIN questiontypes AS qt
    ON q.questiontype_id = qt.id
LEFT JOIN answers AS a
    ON r.answer_id = a.id
WHERE s.id = < cfqueryparam cfsqltype = "cf_sql_integer" value = "#arguments.surveyid#" >
GROUP BY q.id,
    a.id
ORDER BY a.rank

这个问题完全符合我的要求。但问题是，在视图上显示结果时，它所做的是问题与答案的数量相乘，尽管我使用cfoutout属性组和列名questionid。有谁能帮我解决我怎样才能防止问题与答案的数量相乘？
这是我显示调查结果的方式

<cfoutput query="rc.data.questions" group="questionid">
   <cfswitch expression="#rc.data.questions.template#">
      <cfcase value="multiplechoice">
         <table class="table table-striped table-hover">
            <thead>
               <tr>
                  <th width="50%">#rc.data.questions.question#</th>
                  <th></th>
                  <th>
                     <div class="center">Response Count</div>
                  </th>
               </tr>
            </thead>
            <cfoutput>
               <tbody>
                  <tr>
                     <td width="60%">#rc.data.questions.answer#</td>
                     <td>
                        <div class="center">#rc.data.questions.totalanswer#</div>
                     </td>
                  </tr>
            </cfoutput>
            <cfif structKeyExists(rc.data.questions, "totalother") AND rc.data.questions.template EQ 'multiplechoiceother' OR rc.data.questions.template EQ 'multiplechoicemultiother'>
            <tr>
            <td><a href="#buildurl(action='survey.text_other',querystring='id=#questionid#')#" target="_blank">View other Text answers</a></td>
            <td><div class="center">#rc.data.questions.totalother#</div></td>
            </tr>
            </cfif>
            </tbody>
         </table>
         </table>
      </cfcase>
   </cfswitch>
</cfoutput>

最佳答案

ORDERBY子句中字段的顺序必须与要使用cfoutput的group属性的顺序匹配。如果要执行此操作：

<cfoutput query="SomeQuery" group="field1">
    #data for this grouping#
    <cfoutput group="field2">
        #data for this grouping#
        <cfoutput>
            #ungrouped data#
        </cfoutput>
    </cfoutput>
</cfoutput>

那么您的查询必须以：

order by field1, field2, other_fields_if_appropriate

关于mysql - 行由于分组依据而倍增，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19701666/