多表分组统计指定集合内物种编码的目击记录数

需求说明 #

需要对多表关联后的数据，按reporter_num（报告人编号）和report_date（报告日期）分组，统计species_code属于{10,20}集合的目击记录数量；即使分组内没有符合条件的记录，也要显示计数为0。

正确SQL查询语句 #

SELECT
    r.reporter_num,
    rp.report_date,
    SUM(CASE WHEN s.species_code IN (10, 20) THEN 1 ELSE 0 END) AS my_count
FROM report rp
INNER JOIN reporter r ON rp.reporter_id = r.reporter_id
INNER JOIN location l ON rp.report_id = l.report_id
INNER JOIN method m ON l.location_id = m.location_id
LEFT JOIN sighting s ON m.method_id = s.method_id
GROUP BY r.reporter_num, rp.report_date;

或者使用COUNT实现等价逻辑：

SELECT
    r.reporter_num,
    rp.report_date,
    COUNT(CASE WHEN s.species_code IN (10, 20) THEN s.species_code END) AS my_count
FROM report rp
INNER JOIN reporter r ON rp.reporter_id = r.reporter_id
INNER JOIN location l ON rp.report_id = l.report_id
INNER JOIN method m ON l.location_id = m.location_id
LEFT JOIN sighting s ON m.method_id = s.method_id
GROUP BY r.reporter_num, rp.report_date;

原查询错误原因 #

1. 第一个查询的问题：
   count(sighting.species_code in (10, 20))中，sighting.species_code in (10,20)返回布尔值（TRUE/FALSE），而COUNT函数会将所有非NULL值计入统计，无论布尔值是真还是假，因此最终统计的是分组内的总记录数，而非符合条件的记录数。

2. 第二个子查询的问题：
   使用INNER JOIN关联子查询结果会过滤掉没有符合条件记录的分组（如1111的2022-09-16），导致这些分组无法出现在结果中；同时关联逻辑错误，使得计数结果不符合预期。

预期结果 #

执行正确SQL后将得到：

reporter_num | report_date | my_count
----------------------------------------
1111         | 2022-09-05  | 2
1111         | 2022-09-16  | 0
2222         | 2022-09-22  | 1

内容的提问来源于stack exchange，提问作者cheesypoofs