时间戳

首页 > 解决方案 > MYSQL GROUP BY LEFT JOIN 和 COUNT

mysql - MYSQL GROUP BY LEFT JOIN 和 COUNT

问题描述

我在使用 JOIN 子句对 MYSQL 数据库中的项目进行分组和计数时遇到问题

我的两张表如下

用户表

id | surname | othernames
1  |  Doe    | John
2  |  Doe    | Jane
3  |  Doe    | Mary

订阅表

id | user_id | parent_subscription_id 
1  | 1       | Null
2  | 2       | 1
3  | 3       | 1
4  | 4       | 2                         
5  | 5       | 3
6  | 6       | 3

我需要能够生成如下列表

Name         |   Referrals
John Doe     | 2
Jane Doe     | 1
Mary Doe     | 2

换句话说，它是用户表中的用户，其 users.id 与 subscriptions.user_id 匹配，其订阅的订阅 id 是另一个订阅的父订阅。这意味着，如果您的订阅被另一个订阅引用为其自己的 parent_subscription_id，那么该新订阅将成为您的推荐。

我已经尝试了以下查询，但它没有给我预期的结果

SELECT users.surname, users.othernames,count('s.parent_subscription_id') as referrals 
FROM users 
    LEFT JOIN subscriptions s ON s.user_id=users.id 
group BY parent_subscription_id

我已经检查了关于 SO 的其他一些问题，但我找不到任何可以解决此类问题的问题 谢谢

标签： mysqlgroup-bycountleft-join

解决方案

我认为你想要的逻辑是：

select u.surname, u.othernames, count(s.parent_subscription_id) referrals
from subscriptions s
left join subscriptions p on p.id = s.parent_subscription_id
inner join users u on u.id = coalesce(p.user_id, s.user_id)
group by u.id, u.surname, u.othernames

DB Fiddle 上的演示：

姓氏 | 其他名字 | 转介
:-------- | :--------- | --------:
能源部 | 约翰 | 2
能源部 | 简 | 1
能源部 | 玛丽 | 2

推荐阅读