如何使用MySQL分别对列进行计数和分组? [英] How can I count and group columns separately with MySQL?

问题描述

我有一个调查应用程序，并且正在结果页面上.对于每个问题，页面应显示提供的不同答案以及提供的答案次数.

I have a survey application and I'm working on the results page. For each question, the page should display the different answers provided and how many times each was provided.

某些问题可以接受多个答案.例如，假设以下是一个这样的问题:

Some questions can accept multiple answers. For example, let's assume the following is one such question:

1. 你感觉如何?

• 好
• 坏
• 好的

这表示允许将 Good ， Bad 和/或 Okay 的任意组合作为答案.

This means that any combination of Good, Bad and/or Okay are allowed as an answer.

说调查得到的答复如下:

Say the responses from the survey are the following:

• 调查1:好，差和好
• 调查2:好，差和好
• 调查3: Good 和 Bad
• 调查4:好和差
• 调查5:好
• 调查6:好
• 调查7:好
• Survey 1: Good, Bad and Okay
• Survey 2: Good, Bad and Okay
• Survey 3: Good and Bad
• Survey 4: Good and Bad
• Survey 5: Good
• Survey 6: Good
• Survey 7: Good

以下是预期结果:

1. 你感觉如何?
• 好= 7
• 坏= 4
• 好的= 2

我的代码可以提供

1. 你感觉如何?
• 好= 3
• 坏= 2
• 好= 4
• 好的= 1
• 坏= 2
• 好的= 1

此示例的数据库条目如下:

The database entries for this example look like this:

<身体>

questionNum	问题	answer1	answer2	answer3	...
1	您感觉如何?	好	错误	好的
1	您感觉如何?	好	错误	好的
1	您感觉如何?	好	错误
1	您感觉如何?	好	错误
1	您感觉如何?	好
1	您感觉如何?	好
1	您感觉如何?	好

这是我的代码:

my $queryQuery = "SELECT questionNum, question, answer1, answer2, answer3, answer4, answer5, answer6, answer7, answer8, answer9, answer10, COUNT(*) FROM results WHERE title = ? GROUP BY answer1, answer2, answer3, answer4, answer5, answer6, answer7, answer8, answer9, answer10 ORDER BY questionNum";
my $sthm = $dbh->prepare($queryQuery);
$sthm->execute($marathon);

my $prev_question;
while(my($questNumber, $quest, $ans1, $ans2, $ans3, $ans4, $ans5, $ans6, $ans7, $ans8, $ans9, $ans10, $count) = $sthm->fetchrow_array){
print qq{<tr><td> $questNumber. $quest \n </td></tr>} unless $quest eq $prev_question; # the trailing conditional is to get rid of the duplicate questions that print out.
print qq{<tr><td> $ans1 = $count </td></tr>} unless $ans1 eq "";
print qq{<tr><td> $ans2 = $count </td></tr>} unless $ans2 eq "";
print qq{<tr><td> $ans3 = $count </td></tr>} unless $ans3 eq "";
print qq{<tr><td> $ans4 = $count </td></tr>} unless $ans4 eq "";
print qq{<tr><td> $ans5 = $count </td></tr>} unless $ans5 eq "";
print qq{<tr><td> $ans6 = $count </td></tr>} unless $ans6 eq "";
print qq{<tr><td> $ans7 = $count </td></tr>} unless $ans7 eq "";
print qq{<tr><td> $ans8 = $count </td></tr>} unless $ans8 eq "";
print qq{<tr><td> $ans9 = $count </td></tr>} unless $ans9 eq "";
print qq{<tr><td> $ans10 = $count </td></tr>} unless $ans10 eq "";
$prev_question = $quest;
}

在这里，我要遍历查询以打印出所有没有重复的问题，并打印包含问题的答案(只要它们不为空).在这里，我正在搜索上面描述为1的输出.您感觉如何?好= 7，差= 4，好=2.
相反，我将答案分为几组，因为用户已使用复选框而不是单选按钮为单个问题选择了多个答案.因此，在数据库内部看起来，即使答案已经放在自己的列中，它们仍是连接的，因为用户选择问题的选项是在同一时间输入答案的.

Here I am looping through the query to print out all of the questions with no duplicates and print the answers with the questions as long as they aren't empty. Here is where I'm searching for the output described above as 1. How are you feeling? Good = 7, Bad = 4, Okay = 2.
Instead I'm getting the answers divided into groups because the user has selected multiple answers for a single question with check boxes instead of radiobuttons. So inside of the database it appears that even though the answers have been put into their own columns they are connected because they were entered at the same time by the user selecting to options for the question.

推荐答案

您需要将(糟糕的)数据模型转换为更有用的模型.这是您为了获得想要的结果而需要进行的摆弄.

You need to transform your (awful) data model into something that is more useful. This is the sort of fiddling you will need to perform to get the result you are looking for.

select questionNum ,question ,answer, count(*) as num_of
from (
  select questionNum ,question ,answer1 as answer FROM results where answer1 IS NOT NULL union all
  select questionNum ,question ,answer2 as answer FROM results where answer2 IS NOT NULL  union all
  select questionNum ,question ,answer3 as answer FROM results where answer3 IS NOT NULL  union all
  select questionNum ,question ,answer4 as answer FROM results where answer4 IS NOT NULL  union all
  select questionNum ,question ,answer5 as answer FROM results where answer5 IS NOT NULL  union all
  select questionNum ,question ,answer6 as answer FROM results where answer6 IS NOT NULL  union all
  select questionNum ,question ,answer7 as answer FROM results where answer7 IS NOT NULL  union all
  select questionNum ,question ,answer8 as answer FROM results where answer8 IS NOT NULL  union all
  select questionNum ,question ,answer9 as answer FROM results where answer9 IS NOT NULL  union all
  select questionNum ,question ,answer10 as answer FROM results where answer10 IS NOT NULL
  ) as fiddle
group by  questionNum ,question ,answer

为避免日后出现这种麻烦，您确实应该考虑重新布置桌子.这种情况往往会随着时间的流逝，使小提琴变得难以指数级地制造.因此，请花一些时间重新考虑您的数据模型.

To avoid fiddles of this nature in future you really ought to consider re-arranging the table. What tends to happen with this is that the fiddles get exponentially harder to create over time. so please do take some time to reconsider your data model.

这篇关于如何使用MySQL分别对列进行计数和分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！