根据另一个表中的行数对mysql表进行排序

发布于 2024-08-21 00:45:12 字数 421 浏览 10 评论 0原文

我试图根据用户在辅助评论表中链接到的评论数量对用户表进行排序，我认为子选择将是最好的工具，但我无法获得正确的语法。

用户表 testdata：

id | user_id
1  | 1000
2  | 1001
3  | 1002

评论表 testdata

id | link_id
1  | 1002
2  | 1000
3  | 1002
4  | 1000
5  | 1002
6  | 1001
7  | 1000
8  | 1002

第一个表中的预期排序结果将是：

id | user_id
3  | 1002
1  | 1000
2  | 1001

任何朝正确方向的推动都将非常有帮助，谢谢！ =)

原文

I'm trying to sort a user table based on how many comments they have linked to them in a secondary comment-table, I figured a sub-select will be the best tool but I can't get the syntax correct.

Users table testdata:

id | user_id
1  | 1000
2  | 1001
3  | 1002

Comment table testdata

id | link_id
1  | 1002
2  | 1000
3  | 1002
4  | 1000
5  | 1002
6  | 1001
7  | 1000
8  | 1002

Expected sorted result in the first table would be:

id | user_id
3  | 1002
1  | 1000
2  | 1001

Any push in the right direction would be extremly helpful, thanks! =)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

再可℃爱ぅ一点好了 2024-08-28 00:45:12

事实上，没有必要使用子查询。您可以使用 JOIN 和 ORDER BY 计数：

SELECT 
    users.user_id, COUNT(comments.id) as total_messages
FROM 
    users
INNER JOIN 
    comments ON comments.link_id = users.id
GROUP BY 
    user_id
ORDER BY 
    COUNT(comments.id) DESC

In fact, there is no need to use a sub-query. You can use a JOIN and ORDER BY the count:

SELECT 
    users.user_id, COUNT(comments.id) as total_messages
FROM 
    users
INNER JOIN 
    comments ON comments.link_id = users.id
GROUP BY 
    user_id
ORDER BY 
    COUNT(comments.id) DESC

回复收藏 0 原文

心的位置 2024-08-28 00:45:12

只是一个带有计数（）的连接，然后按计数（）进行排序就可以了

select c.id, user_id, count(*) from user u, comments c where u.id = c.id group by id, user_id

-ace

just a join, with a count(), and then an order by the count() should do it

select c.id, user_id, count(*) from user u, comments c where u.id = c.id group by id, user_id

-ace

回复收藏 0 原文

雪落纷纷 2024-08-28 00:45:12

SELECT
    u.id,
    u.user_id
    COUNT(u.id) link_count
FROM
    Users u,
    Comment c
WHERE
    c.link_id = u.user_id
ORDER BY
    link_count
GROUP BY
    u.id,
    u.user_id

SELECT
    u.id,
    u.user_id
    COUNT(u.id) link_count
FROM
    Users u,
    Comment c
WHERE
    c.link_id = u.user_id
ORDER BY
    link_count
GROUP BY
    u.id,
    u.user_id

回复收藏 0 原文

丑疤怪 2024-08-28 00:45:12

SELECT u.id, u.user_id
FROM users u
JOIN ( SELECT link_id, COUNT(*) cnt FROM comment GROUP BY link_id ) c
  ON ( c.link_id = u.user_id )
ORDER BY c.cnt DESC

这将允许您添加来自用户的其他列，而无需对所有列进行分组。

SELECT u.id, u.user_id
FROM users u
JOIN ( SELECT link_id, COUNT(*) cnt FROM comment GROUP BY link_id ) c
  ON ( c.link_id = u.user_id )
ORDER BY c.cnt DESC

This would allow you to add other columns from users without having to group by them all.

回复收藏 0 原文

~没有更多了~