如何使这个查询更容易编写

I have a query that pulls up questions from one table and answers from another.

我有一个查询,从一个表中提取问题,从另一个表中提取答案。

SELECT 
  questions.question, 
  questions.answers, 
  (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 1 
          GROUP BY answer) as ans1, 
  (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 2 
          GROUP BY answer) as ans2 
FROM questions 
WHERE questions.id = 1

While this works I don't like the idea of adding an extra subquery for each answer (questions.answers is a comma-seperated string of potential answers). It's do-able but I'm sure there must be a better way. The main thing is that different questions have different numbers of answers.

虽然这有效但我不喜欢为每个答案添加额外的子查询(questions.answers是一个逗号分隔的潜在答案字符串)。这是可行的,但我确信必须有更好的方法。主要的是,不同的问题有不同的答案数量。

Is there a better way to do this or is this an acceptable way of doing things? I'd imagine multiple subselects in a query could have a (small) performance hit in the future (not that I'm performance testing yet).

有没有更好的方法来做到这一点,或者这是一种可接受的做事方式?我想象一下,查询中的多个子选择将来可能会有(小)性能损失(而不是我的性能测试)。

If it's applicable I don't expect to have more than 5 answers per question.

如果它适用,我不希望每个问题有超过5个答案。

6 个解决方案

#1

SELECT q.question, q.answers,
  SUM(a.answer = 1) AS ans1,
  SUM(a.answer = 2) AS ans2
FROM questions q
 LEFT OUTER JOIN answers a ON (q.id = a.question_id)
WHERE q.id = 1
GROUP BY q.id;

#2

It looks pretty good to me. You could leave out the "group by" clauses on the subqueries since you're only selecting one value of "answer" anyway.

它看起来对我很好。您可以在子查询中省略“group by”子句,因为您只选择了一个“answer”值。

I suspect that there may be something screwy with your schema if you are saving a list of possible answers with every single answer. That should probably be in a separate table with each answer represented by a single row. Delimiter separated strings are a big code smell in database design, as are duplicates of data that you expect to be the same.

我怀疑如果你在每个答案中保存一份可能的答案列表,那么你的架构可能会有些麻烦。这可能应该在一个单独的表中,每个答案由一行表示。在数据库设计中,分隔符分隔的字符串是一个很大的代码气味,因为您期望相同的数据重复。

#3

maybe?

SELECT 
    questions.question, 
    questions.answers, 
    (case when answers.answer = 1 then COUNT(answer) end ) as ans1, 
    (case when answers.answer = 2 then COUNT(answer) end ) as ans2 
    FROM questions left join answers on answers.question_id = questions.id
    WHERE questionss.id = 1
    group by questions.id

#4

Could you not use?

你能用吗?

SELECT 
  questions.question, 
  questions.answers, 
  COUNT(answer)

FROM questions inner join answers
on questions.id = answer.question_id
WHERE questions.id = 1
GROUP BY
questions.question, 
questions.answers

You'd get rows instead of columns, but similar results.

你会得到行而不是列,但结果相似。

#5

The standard way for linking multiple rows in one table to one or more rows in another table is to use a link table. Such a table would store in each of its rows an ID from one table and the ID from the other table that is related to the first one.

将一个表中的多个行链接到另一个表中的一个或多个行的标准方法是使用链接表。这样的表将在其每个行中存储来自一个表的ID和来自另一个表的与第一个表相关的ID。

For your questions and answers, storing the possible answers to a question in a link table (that I might call question_answered) might looks like this:

对于您的问题和答案,在链接表中存储问题的可能答案(我可能称之为question_answered)可能如下所示:

question_id | answer_id

question_id | answer_id

1 1

1 2

2 3

4 4

4 5

4 6 `

4 6`

As you can see, question 1 has two possible answers, question 2 has 1 possible answer, question 3 has gone unanswered while question 4 received 3 different answers.

如你所见,问题1有两个可能的答案,问题2有1个可能的答案,问题3没有答案,而问题4有3个不同的答案。

While I'm not sure what "answer = 1" or "answer = 2" mean in your context, using a link table counting all the answers to a question is easy as:

虽然我不确定“answer = 1”或“answer = 2”在您的上下文中是什么意思,但使用链接表计算问题的所有答案很简单:

SELECT question, count(answer_id) FROM questions AS q INNER JOIN question_answered AS qa ON qa.question_id = q.id) GROUP BY q.id

SELECT question,count(answer_id)FROM questions AS q INNER JOIN question_answered AS qa ON qa.question_id = q.id)GROUP BY q.id

#6

      SELECT    questions.question,
                       questions.answers,
                       A.answer,
                       COUNT(A.answer)
        FROM      questions Q
LEFT JOIN      answers A
            ON      Q.id = A.question_id
     WHERE      questions.id = 1
          AND      A.answer IN (1,2)
GROUP BY     A.answer

#1

SELECT q.question, q.answers,
  SUM(a.answer = 1) AS ans1,
  SUM(a.answer = 2) AS ans2
FROM questions q
 LEFT OUTER JOIN answers a ON (q.id = a.question_id)
WHERE q.id = 1
GROUP BY q.id;

#2