根据"home"表和"away"表中的条件计算引用数的最佳方法是什么？

Question

我正在做一个项目，并使用Knex进行，(尽管如果你给我原始SQL，我很确定我也可以转换它。

所以，这是。

我有表格"组"和"user_group"。

table 'group' 
id: integer
name: string
is_public: boolean
table 'user_group'
group_id: ref(group.id)
user_id: ref(user.id) // don't need user table info for this
role: string 

我想做的是获取所有组的列表

• is_public为真的地方
• 返回 group.name 和 group.id
• 以及该组有多少成员的计数。
• 从大多数成员到最少成员排序

所以输出看起来像这样：

[
{name: "Clowns", memberCount: 20, id: 3},
{name: "Puppets", memberCount: 12, id: 36},
{name: "Jugglers", memberCount: 5, id: 12},
...
]

这是我到目前为止得到的：

SELECT COUNT('*') AS memberCount, group.name AS name, group.id AS id
FROM group
INNER_JOIN user_group on group.id = user_group.group_id
WHERE group.is_public = true
AND WHERE user_group.role = "MEMBER"
ORDER BY memberCount ASC`

我走在正确的轨道上吗？

Answer 1

我会这样处理：

SELECT g.name, g.id, COUNT(ug.role) AS memberCount
FROM group g LEFT JOIN
user_group ug
ON ug.group_id = g.id AND
ug.role = 'MEMBER'
WHERE g.is_public
GROUP BY g.name, g.id
ORDER BY memberCount DESC;

这是如何构建的？ 首先，这是使用LEFT JOIN因为您需要所有组，甚至可能是没有成员的组。 由于LEFT JOIN，角色的条件在ON子句中，而不是WHERE子句中。

其他一些注意事项：

• 对字符串和日期常量使用单引号。 这是 SQL 标准。
• 按SELECT中的所有键聚合。
• 要从最高到最低获取结果，请使用DESCORDER BY。
• 无需为布尔值指定= true，尽管也没有问题。

Answer 2

如果您使用的是MS-SQL，那么下面的查询将为您提供帮助。 聚合函数通常与 SELECT 语句的 GROUP BY 子句一起使用。所以你需要使用分组依据子句。

SELECT COUNT(*) AS memberCount, group.name AS name, group.id AS id
FROM group
INNER_JOIN user_group on group.id = user_group.group_id
WHERE group.is_public = 1

----如果列是位数据类型，则为 1

真。

AND WHERE user_group.role = 'MEMBER'
group by group.name,group.id
ORDER BY COUNT(*) ASC

Answer 3

表user_group必须按如下方式分组：

SELECT group_id, COUNT(user_id) AS memberCount
FROM user_group
WHERE role = 'MEMBER'
GROUP BY group_id

通过此分组，您可以获得属于每个组的用户数。
现在，必须将上述查询连接到group表：

SELECT
ug.memberCount, g.name, g.id
FROM group AS g
INNER JOIN (
SELECT group_id, COUNT(user_id) AS memberCount
FROM user_group
WHERE role = 'MEMBER' 
GROUP BY group_id
) AS ug
ON ug.group_id = g.id
WHERE g.is_public = TRUE
ORDER BY ug.memberCount DESC, g.name

上面的查询将仅返回至少具有 1 个成员的组。
如果您还想要所有其他具有 0 个成员的组，则需要一个LEFT JOIN：

SELECT
COALESCE(ug.memberCount,  0) AS memberCount, g.name, g.id
FROM group AS g
LEFT JOIN (
SELECT group_id, COUNT(user_id) AS memberCount
FROM user_group
WHERE role = 'MEMBER' 
GROUP BY group_id
) AS ug
ON ug.group_id = g.id
WHERE g.is_public = TRUE
ORDER BY ug.memberCount DESC, g.name