现在我知道这个问题以前已经被问过好几次了,但很长一段时间以来,我一直试图将不同的现有解决方案应用于我的特定问题,但都没有成功。因此,我在这里希望得到一些指导。
我有一个名为tblanswers的表格,其中包含与另一个表格中不同问题相关的答案我想要的是获得特定问题ID的每个答案的计数,但将其限制为每个月的n个第一个答案
来自tblanswers的样本数据:
id qid answer timestamp
72 162 2 1366027324
71 161 4 1343599200
70 162 2 1366014201
69 161 4 1366011700
68 162 2 1366006729
67 161 3 1366010948
66 162 2 1365951084
这是我迄今为止的查询:
SELECT *, COUNT(*) c FROM(
SELECT answer, timestamp, YEAR(FROM_UNIXTIME(timestamp)) yr, MONTH(FROM_UNIXTIME(timestamp)) mo FROM tblanswers
WHERE qid = 161
ORDER BY timestamp ASC
) q GROUP BY YEAR(FROM_UNIXTIME(timestamp)), MONTH(FROM_UNIXTIME(timestamp)), answer
这会给我这样的信息:(样本数据中的日期和数字不准确)
answer yr mo c
1 2013 5 5
2 2013 5 3
3 2013 5 2
1 2013 6 5
2 2013 6 15
3 2013 6 7
假设我只想在一个月内看到前三个答案,那么计数永远不会超过3。如何限制每个月?
最终数据应该是每个答案的总和,如下所示:
answer num_answers
1 2
2 3
3 3
我认为其中一种解决方案可以发挥作用,但不能发挥作用:http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-grouphttp://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group-another-solution
感谢您的帮助。谢谢
此解决方案基于此处的top-N-per-group方法
SELECT answer, COUNT(*) num_answers
FROM (SELECT answer, yearmonth,
@rn := CASE WHEN @prevmonth = yearmonth
THEN @rn + 1
ELSE 1
END rn,
@prevmonth := yearmonth
FROM (SELECT @rn := NULL, @prevmonth := NULL) init,
(SELECT answer,
YEAR(FROM_UNIXTIME(timestamp))*100+MONTH(FROM_UNIXTIME(timestamp)) yearmonth
FROM tblanswers
WHERE qid = 220
ORDER BY timestamp) x) y
WHERE rn <= 3
GROUP BY answer
SQLFIDDLE
这个解决方案怎么样:
SELECT qid, answer, YEAR(FROM_UNIXTIME(timestamp)) yr, MONTH(FROM_UNIXTIME(timestamp)) mo, COUNT(*) no
FROM tblanswers
WHERE qid = 161
GROUP BY answer, yr, mo
HAVING COUNT(*) <= 2
ORDER BY timestamp ASC;
和小提琴:http://sqlfiddle.com/#!2/1541eb/126
没有理由重新发明一个轮子,并冒着出现错误、次优代码的风险。您的问题是常见的每个组限制问题的琐碎扩展(另请参阅每个组的标记限制)。已经有经过测试和优化的解决方案来解决这个问题。