gpt4 book ai didi

mysql - 按一年中的月份获取特定行的排名

转载 作者:行者123 更新时间:2023-11-30 22:44:22 26 4
gpt4 key购买 nike

我如何查询某行的排名,与该行在同一月期间与其他行相比的投票数数相关那一年?

+----+---------+--------+---------------------+
| 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 |
+----+---------+--------+---------------------+

例如,如果我想查找每个月的 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 |
+----+------+---------+-------+---------------------+

这是因为 post_id #1 在当年的第 4 个月获得了最多的选票,但在当年的第 6 个月获得了第二多的选票。

我该如何查询?

最佳答案

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

DEMO

关于mysql - 按一年中的月份获取特定行的排名,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30081879/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com