How can I make a query to get the rank of a row, in relation to the number of votes the row has compared to other rows during the same month of the year?
我如何进行查询以获得一行的排名,与一年中同一行中该行与其他行的投票数相关?
+----+---------+--------+---------------------+
| id | post_id | votes | date |
+----+---------+--------+---------------------+
| 1 | 1 | 10342 | 2015-04-29 04:40:51 |
| 2 | 2 | 9432 | 2015-04-17 22:37:53 |
| 3 | 3 | 33064 | 2015-05-17 22:37:17 |
| 4 | 4 | 32741 | 2015-05-17 22:37:25 |
| 5 | 5 | 30362 | 2015-05-17 22:37:31 |
| 6 | 6 | 15321 | 2015-06-28 00:33:20 |
| 7 | 1 | 4356 | 2015-06-28 00:19:30 |
+----+---------+--------+---------------------+
For example, if I wanted to find the rank of post_id #1 for each month, it should return something like:
例如,如果我想在每个月找到post_id#1的等级,它应该返回如下内容:
+----+------+---------+-------+---------------------+
| id | rank | post_id | votes | date |
+----+------+---------+-------+---------------------+
| 1 | 1 | 1 | 10342 | 2015-04-29 04:40:51 |
| 7 | 2 | 1 | 4356 | 2015-06-28 00:19:30 |
+----+------+---------+-------+---------------------+
This is because post_id #1 had the most votes during the 4th month of the year, but the second highest number of votes in the 6th month of the year.
这是因为post_id#1在一年中的第四个月获得的票数最多,但是在今年第六个月中获得第二高票数。
How can I make this query?
我该如何进行此查询?
1 个解决方案
#1
SELECT YEAR(t1.date) AS year, MONTH(t1.date) AS month, SUM(IF(t1.votes >= t2.votes, 1, 0)) AS rank
FROM YourTable AS t1
JOIN YourTable AS t2 ON YEAR(t1.date) = YEAR(t2.date) AND MONTH(t1.date) = MONTH(t2.date)
WHERE t2.post_id = 1
GROUP BY year, month
#1
SELECT YEAR(t1.date) AS year, MONTH(t1.date) AS month, SUM(IF(t1.votes >= t2.votes, 1, 0)) AS rank
FROM YourTable AS t1
JOIN YourTable AS t2 ON YEAR(t1.date) = YEAR(t2.date) AND MONTH(t1.date) = MONTH(t2.date)
WHERE t2.post_id = 1
GROUP BY year, month