从多对多表中检索分组 [英] Retrieving groupings from many-to-many tables

问题描述

这很难解释，但我会尽力而为.

This is kind of hard to explain but I'll do what I can.

我有2个具有多对多关系的表；有一个定义该关系的链接表.这些表分别命名为Question，QuestionTopic和Topic.主题具有字段TopicID和TopicName.问题具有字段QuestionID和QuestionText.

I have 2 tables with a many-to-many relationship; there is a linking table defining the relationship. These tables are named Question, QuestionTopic and Topic. Topic has the fields TopicID and TopicName.Question has the fields QuestionID and QuestionText.

我想检索主题列表以及属于该主题的问题数量.但是，可以将主题归为一组，并且应该知道该组合所独有的问题数.例如:

I want to retrieve a listing of the topics and the number of questions belonging to that topic. However topics could be grouped together and the count of questions unique to that combination should be known. As an example:

主题|计数

主题1，主题2 | 10

Topic1,Topic2 | 10

主题1 | 3

主题2 | 2

以上暗示主题1和主题10独有3个问题，主题为主题1和主题2.剩下的2个问题都有topic2. 我正在使用MySQL和PHP.谢谢.

The above implies there are 3 questions unique to topic1 and 10 which have the topics Topic1 and Topic2. The remaining 2 questions have topic2. I'm using MySQL and PHP. Thanks.

推荐答案

使用GROUP_CONCAT()的作弊"解决方案.这不会显示与任何主题无关的问题的数量:

"Cheating" solution, using GROUP_CONCAT(). This will not show the count of questions that are not related to any topic:

SELECT
      TopicIds
    , COUNT(*) AS QuestionCount
FROM
      ( SELECT
              QuestionId
            , GROUP_CONCAT(TopicId ORDER BY TopicId) AS Topics
        FROM 
              QuestionTopic
        GROUP BY 
              QuestionId
      ) AS grp
GROUP BY
      Topics

这篇关于从多对多表中检索分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！