SQL：按有多少行排序（使用 COUNT？）

发布于 2024-12-11 07:23:30 字数 551 浏览 1 评论 0原文

对于 SMF，我正在为我的氏族成员制作一个名册（请不要附带“你应该问 SMF”，因为那完全不相关；这只是上下文信息）。

我需要它来选择所有成员（来自 smf_members），并按照他们在 smf_permissions 中拥有的权限数量对其进行排序（以便脚本可以确定谁的级别更高）。您可以使用以下命令检索有多少权限：COUNT(permission) FROM smf_permissions。

我现在使用此 SQL：

SELECT DISTINCT(m.id_member), m.real_name, m.date_registered  
FROM smf_members AS m, smf_permissions AS p  
WHERE m.id_group=p.id_group  
ORDER BY COUNT(p.permission)

但是，这只返回一行！如何返回多行？

干杯，阿尔特

原文

For SMF, I'm making a roster for the members of my clan (please don't come with "You should ask SMF", because that is completely irrelevant; this is just contextual information).

I need it to select all members (from smf_members) and order it by how many permissions they have in smf_permissions (so the script can determine who is higher in rank).
You can retrieve how many permissions there are by using: COUNT(permission) FROM smf_permissions.

I am now using this SQL:

SELECT DISTINCT(m.id_member), m.real_name, m.date_registered  
FROM smf_members AS m, smf_permissions AS p  
WHERE m.id_group=p.id_group  
ORDER BY COUNT(p.permission)

However, this only returns one row! How to return several rows?

Cheers,
Aart

分享到QQ

分享到微博

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

发布评论

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

平安喜乐 2024-12-18 07:23:30

您需要一个GROUP BY。我还使用显式的 JOIN 语法进行了重写。如果您想要包含零权限的成员，您可能需要更改为 LEFT JOIN。

SELECT m.id_member,
       m.real_name,
       m.date_registered,
       COUNT(p.permission) AS N
FROM   smf_members AS m
       JOIN smf_permissions AS p
         ON m.id_group = p.id_group
GROUP  BY m.id_member,
          m.real_name,
          m.date_registered
ORDER  BY COUNT(p.permission)

You need a GROUP BY. I've also rewritten with explicit JOIN syntax. You might need to change to LEFT JOIN if you want to include members with zero permissions.

SELECT m.id_member,
       m.real_name,
       m.date_registered,
       COUNT(p.permission) AS N
FROM   smf_members AS m
       JOIN smf_permissions AS p
         ON m.id_group = p.id_group
GROUP  BY m.id_member,
          m.real_name,
          m.date_registered
ORDER  BY COUNT(p.permission)

回复收藏 0 原文

~没有更多了~