查找关联记录最少的记录 [英] Find the record with the lest amount of associated records

问题描述

我刚刚问了这个问题在两个表之间的联接中找到最旧的记录，并很好地回答了我的问题.问题是那不是我要找的(我的错)

I just asked this question Find the oldest record in a join between two tables and got a great answer to my question. The problem is that is is not quite what I was looking for (my fault)

考虑以下MySQL表

Table: Questions
   ID

Table: Results
   ID 
   Created - When this record was added. 
   Q_ID - A FK to the Question table 

示例数据

Table: Questions
   ID 
   ----
    1
    8
   15
   55

Table: Results
   ID | Created | Q_ID 
   --------------------
    1 |   12:02 |    1 
    2 |   12:03 |   15 
    3 |   12:04 |    8  

使用以下查询，它将返回所有没有与之关联的结果的记录，如果所有记录都具有结果，则它将返回最早的结果的问题.

Using the following query, it will return all the records that do not have results associated with them, if all records have results then it will return the question with the oldest result.

SELECT *
FROM
    questions
    LEFT JOIN results
        ON results.q_id = questions.id
ORDER BY
    ISNULL(results.id) DESC, results.created ASC
LIMIT 1

我实际上正在寻找的是尚未回答的任何问题，然后将问题按我被回答多少次的计数进行排序.避免回答的问题应该放在顶部.

What I am actually looking for is for any question that has not been answered, then to sort the question my the count of how many times they have been answered. The lest answered questions should be at the top.

推荐答案

这将为您提供每个问题以及与之相关的结果数(即使不存在任何结果).他们将按最低的顺序在顶部进行订购:

This would give you each question, and the number of results associated it (even if no results exist). They will be ordered with the lowest count at the top:

SELECT Questions.ID, COUNT(Results.ID) Result_Count
FROM
    Questions
    LEFT JOIN Results ON Questions.ID = Results.Q_ID
GROUP BY Questions.ID
ORDER BY COUNT(Results.ID)

这是您的主意吗?

这篇关于查找关联记录最少的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！