mysql - 使用同一表上的左联接的成员资格续订报告

我有两个表：

Member
'id'
'name'

Membership
'id'
'site_id'
'member_id'
'year'

我想建立一个报告来跟踪多年来的订阅情况，如下所示：


网站编号
2019年总订阅
2018年总订阅
新（首次订阅该站点）
续订（在同一站点中于2019年和2018年订阅）
丢失（在2018年完成但在2019年未完成该工作）


我在同一个Membership表上的两个左连接处苦苦挣扎，如下所示：

SELECT m.site_id,
count(m2019.id) as total2019,
count(m2018.id) as total2018,
SUM(IF(m2019.member_id IS NOT NULL AND m2018.member_id IS NULL, 1, 0)) new,
SUM(IF(m2019.member_id IS NOT NULL AND m2018.member_id IS NOT NULL AND m2018.site_id = m2019.site_id, 1, 0)) renewal,
SUM(IF(m2018.member_id IS NOT NULL AND m2019.member_id IS NULL, 1, 0)) lost
FROM membership m
LEFT JOIN membership m2019 ON m.id = m2019.member_id AND m.year = '2019' AND m2019.year = '2019' AND m.site_id = m2019.site_id
LEFT JOIN membership m2018 ON m.id= m2018.member_id AND m.year = '2018' AND m2018.year = '2018' AND m.site_id = m2018.site_id
GROUP BY m.site_id;

计数正确，但条件不正确，因此我很确定我的查询不正确。
很久以来我一直在努力，我开始感到有点失落。

有人可以帮我弄清楚我做错了什么吗？
谢谢

最佳答案

我认为您可以通过两个聚合级别来执行所需的操作：

SELECT m.site_id,
       SUM( m.max_year = 2019 ) as total2019,
       SUM( m.min_year = 2018 ) as total2018,
       SUM( m.min_year = 2019 AND m.max_year = 2019 ) as new2019,
       SUM( m.min_year = 2018 and m.max_year = 2018 ) as renewals,
       SUM( m.max_year = 2018 ) as lost
FROM (SELECT m.id, m.site_id, MIN(year) as min_year, MAX(year) as max_year
      FROM membership m
      WHERE year in (2018, 2019)
      GROUP BY m.id, m.site_id
     ) mm
     ON mm.id = m.id
GROUP BY m.site_id;

关于mysql - 使用同一表上的左联接的成员资格续订报告，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58593915/