postgresql 在postgresql中加入+每行计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3944954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join + Count per row in postgresql
提问by Maurice Kroon
Current "Attempts" table:
当前的“尝试”表：
ID QUESTION_ID CORRECT
1 1 FALSE
2 2 TRUE
3 4 FALSE
4 3 FALSE
5 1 TRUE
6 1 TRUE
7 4 TRUE
8 3 TRUE
9 4 FALSE
10 1 TRUE
11 2 TRUE
11 1 FALSE
11 3 FALSE
Current "Question" table:
当前的“问题”表：
ID ANSWER
1 A
2 A
3 B
4 C
5 C
6 C
7 C
Now I want to order Questions based on their amount of times solved. As you can see, Question 1 has been solved 3 times while Question 5, 6 and 7 have been solved 0 times. After I make this order, I want to pick a random top 5 of questions with lowest amount of solved.
现在我想根据问题解决的次数来排序问题。如您所见，问题 1 已解决 3 次，而问题 5、6 和 7 已解决 0 次。在我下完这个订单后，我想随机选择解决数量最少的前 5 个问题。
Therefore, my questions is: How to do this? My ultimate goal is to do this in rails, but first I want to experiment with postgresql a bit. So do you know how to do this? Code examples highly appreciated.
因此，我的问题是：如何做到这一点？我的最终目标是在 Rails 中做到这一点，但首先我想尝试一下 postgresql。那么你知道如何做到这一点吗？代码示例高度赞赏。
Regards,
问候，
Maurice
莫里斯
// UPDATE
// 更新
Ok, I tried to put your advice into practise, but im running into a problem. As you can see, i'm just getting 1 row, i think because of the where clause. could it be that im counting it wrong?
好的，我尝试将您的建议付诸实践，但我遇到了问题。正如你所看到的，我只是得到 1 行，我想是因为 where 子句。会不会是我数错了？
=# select q.id, count(q.id)
-# from questions as q
-# left join attempts as a on a.question_id = q.id
-# where a.correct = true and a.user_id = 4
-# group by q.id
-# order by count(q.id) desc
-# limit 20
-# \g
id | count
-----+-------
112 | 2
(1 row)
// UPDATE 2
// 更新 2
ok, i did a nested select to solve this problem (inspired by How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN):
好的，我做了一个嵌套选择来解决这个问题（灵感来自如何从表中加入一个 COUNT，然后用另一个 JOIN 影响该 COUNT）：
select q.id, temp.Count
from questions as q
left join
(select q.id, count(a.id) as count
from questions as q
left join attempts as a
on a.question_id = q.id
where a.correct = true and a.user_id = 4
group by q.id)
temp on temp.id = q.id
Gave me something: an important list.
给了我一些东西：一个重要的清单。
Now, I still need to create the random 5 questions that have the least amount of counts.
现在，我仍然需要创建计数最少的随机 5 个问题。
I'm trying to do something now with min(count), but that doesnt seem to work out. Any ideas on how to do this?
我现在正在尝试使用 min(count) 做一些事情，但这似乎不起作用。关于如何做到这一点的任何想法？
Thanks again
再次感谢
回答by Marcus Adams
SELECT Q.ID, COUNT(A.ID) AS SolveCount
FROM Question Q
LEFT JOIN Attempts A
ON A.QUESTION_ID = Q.ID
WHERE A.CORRECT = TRUE
GROUP BY Q.ID
ORDER BY COUNT(A.ID)
回答by Jay
How about somthing like the following?
像下面这样的东西怎么样？
SELECT Q.ID, COUNT()
FROM Questions AS Q
LEFT JOIN Attempts AS A ON A.QUESTION_ID = Q.ID
WHERE A.CORRECT = TRUE
GROUP BY Q.ID
ORDER BY COUNT() DESC
LIMIT 5
Summary: It joins the questions table to the attempts table, only considers rows where correct is true, groups by the question id, sorts by the count of true attempts per question, and then limits to the top five results.
总结：它将问题表连接到尝试表，只考虑正确的行，按问题 id 分组，按每个问题的真实尝试次数排序，然后限制为前五个结果。
I haven't tested it, but I think it should at least be close to what you are looking for.
我还没有测试过它，但我认为它至少应该接近你正在寻找的东西。
