php - MYSQL/PHP 计算重复出现问题的答案

Question

我什至不知道该怎么做。但我会尽力解释。

我正在创建一个看起来像这样的报告:

但是还有很多问题。

所以很多人都回答了这个调查，所有记录都在数据库中，我需要使用它来制作这份报告。

我已经创建了从连接多个表中提取所有相关数据的查询，如下所示:

表格-民意调查-调查条目-调查问题-survey_meta-硬件服务

        SELECT
            `surveyEntries`.`ID` AS EntryID,
            `surveyEntries`.`created` AS EntryDate,
            `hw_services`.`name` AS Provider,
            `surveyQuestions`.`ID` AS QuestionID,
            `surveyQuestions`.`label` AS Question,
            `survey_meta`.`answer` AS Answer,
          `surveyQuestions`.`parentID` AS ParentQuestion
        FROM `survey`
        JOIN `surveyQuestions`
            ON `survey`.`ID` = `surveyQuestions`.`surveyID`
        JOIN `surveyEntries`
            ON `survey`.`ID` = `surveyEntries`.`surveyID`
        JOIN `survey_meta`
             ON (`surveyEntries`.`ID` = `survey_meta`.`entryID` AND `surveyQuestions`.`ID` = `survey_meta`.`questionID`)
        JOIN `hw_services`
            ON `surveyEntries`.`hw_serviceID` = `hw_services`.`ID`
        WHERE `hw_services`.`healthwatchID` = '1'
        AND `survey`.`ID` = '1'
        AND `surveyQuestions`.`type` IN ('radio', 'dropdown')
        AND `hw_services`.`ID` = '1697'

好吧，不只是为了证明这是我正在撤回的数据的图片。调查中共有 30 个问题，但这里我只显示每个条目的 4 行。

所以它们是相同的问题等，但条目不同。

现在我该怎么做呢？

并获取每个问题中回答"is"的数字和回答“否”的数字，以便我可以在 php 中生成它？

如果您需要任何进一步的信息，请告诉我。

Answer 1

由于您拥有所有数据，我们可以使用 group by 来查看每个问题的所有唯一答案的计数。为此，我们可以使用如下内容:

SELECT QuestionId, Question, Answer, count(*) 
FROM (PUT YOUR SELECT HERE) 
GROUP BY QuestionId, Answer

然后将为您提供每个问题(及其 ID)，以及该问题的唯一答案和该唯一答案的计数。

或者在您提供的一个选项中完成所有操作:

SELECT
            `surveyEntries`.`ID` AS EntryID,
            `surveyEntries`.`created` AS EntryDate,
            `hw_services`.`name` AS Provider,
            `surveyQuestions`.`ID` AS QuestionID,
            `surveyQuestions`.`label` AS Question,
            `survey_meta`.`answer` AS Answer,
             count(*) as Total,
            `surveyQuestions`.`parentID` AS ParentQuestion
        FROM `survey`
        JOIN `surveyQuestions`
            ON `survey`.`ID` = `surveyQuestions`.`surveyID`
        JOIN `surveyEntries`
            ON `survey`.`ID` = `surveyEntries`.`surveyID`
        JOIN `survey_meta`
             ON (`surveyEntries`.`ID` = `survey_meta`.`entryID` AND `surveyQuestions`.`ID` = `survey_meta`.`questionID`)
        JOIN `hw_services`
            ON `surveyEntries`.`hw_serviceID` = `hw_services`.`ID`
        WHERE `hw_services`.`healthwatchID` = '1'
        AND `survey`.`ID` = '1'
        AND `surveyQuestions`.`type` IN ('radio', 'dropdown')
        AND `hw_services`.`ID` = '1697'
        GROUP BY `surveyQuestions`.`ID`, `survey_meta`.`answer`

我在初始选择中添加了 count(*) 并在末尾添加了 GROUP BY surveyQuestions.ID, survey_meta.answer