如何对查询进行计数和分组以获得正确的结果?

I have a problem, please see my database:

我有一个问题，请查看我的数据库:

-------------------
| id | article_id |
-------------------
| 1  |      1     |
| 2  |      1     |
| 3  |      1     |
| 4  |      2     |
| 5  |      2     |
| 6  |      3     |
| 7  |      3     |
| 8  |      3     |
| 9  |      3     |
| 10 |      3     |

And I want to receive something like this (order by votes, from max to min):

我想收到这样的东西(按投票顺序，从max到min):

---------------------------
| id | article_id | votes |
---------------------------
| 1  |      3     |    5  |
| 2  |      1     |    3  |
| 3  |      2     |    2  |

Could you please help me to write proper sql query?

你能帮我写一个合适的sql查询吗?

2 个解决方案

#1

SELECT article_id, COUNT(article_id) AS votes
FROM votes_table
GROUP BY article_id
ORDER BY votes DESC;

#2

SET @currentRow = 0;
SELECT @currentRow := @currentRow + 1 AS id, t.article_id, t.c AS `votes`
FROM (
    SELECT article_id, count(*) as `c`
    FROM table_votes
    GROUP BY article_id
    ) t
ORDER BY t.c DESC

please note that you can't select an id column like this in this context, and your "expected result" is incorrect. I tried to adapt it at a maximum.

请注意，在此上下文中不能选择这样的id列，并且您的“预期结果”是不正确的。我试图最大限度地适应它。

cheers

干杯