如何在MySQL中按日期范围统计两州会员加入数（分列展示）

需求 #

我用MySQL数据库，有一张membership表，每行记录会员的加入日期、地区（district）、州（state）及加入人数（members_joined）。需要在指定日期范围内，把两个不同州（比如KA和MH）的会员加入数总和分别放在不同列展示，且如果某日期两州均无数据则跳过该行。

表结构 #

CREATE TABLE membership
(
id int not null AUTO_INCREMENT,
state VARCHAR(20),
district VARCHAR(20),
date date,
members_joined int, 
PRIMARY KEY (id)
);

期望结果 #

DATE       | FIRST_STATE| FIRST_STATE_COUNT | SEC_STATE | SEC_STATE_COUNT   |
------------------------------------------------------------------------------
2021-01-10 | KA         |   1000            |   MH      | 1200               |
2021-01-09 | KA         |   500             |   MH      | 550                |
2021-01-08 | KA         |   0               |   MH      | 100                |
2021-01-07 | KA         |   50              |   MH      | 0                  |

我的错误尝试（返回空结果集） #

select A.state as FIRST_STATE , sum(A.members_joined) as FIRST_STATE_COUNT, B.state as SEC_STATE , sum(B.members_joined) as SEC_STATE_COUNT
FROM membership A, membership B
WHERE A.state<>B.state and A.state='KA' and B.state='MH'
and A.date BETWEEN '2021-01-10' and '2020-10-07'
and B.date BETWEEN '2021-01-10' and '2020-10-07'

问题原因 #

你的查询返回空结果主要有两个问题：

1. 日期范围写反了：BETWEEN要求第一个参数是更早的起始日期，第二个是更晚的结束日期，你写的'2021-01-10' AND '2020-10-07'没有任何数据能满足，自然返回空。
2. 自连接逻辑不对：直接交叉连接两张表会产生大量冗余数据，且没有按日期分组，根本无法得到每日的统计结果。

解决方案 #

推荐两种实现方式，第一种更简洁高效，完全匹配你的需求：

方法一：条件聚合（优先使用） #

先筛选指定日期范围和目标州的数据，通过CASE WHEN分别统计每日两州的加入数总和，最后过滤掉两州都无数据的日期：

SELECT
    date AS DATE,
    'KA' AS FIRST_STATE,
    SUM(CASE WHEN state = 'KA' THEN members_joined ELSE 0 END) AS FIRST_STATE_COUNT,
    'MH' AS SEC_STATE,
    SUM(CASE WHEN state = 'MH' THEN members_joined ELSE 0 END) AS SEC_STATE_COUNT
FROM membership
WHERE date BETWEEN '2020-10-07' AND '2021-01-10'
  AND state IN ('KA', 'MH')
GROUP BY date
HAVING FIRST_STATE_COUNT > 0 OR SEC_STATE_COUNT > 0
ORDER BY date DESC;

方法二：日期表左连接（适合需覆盖全日期范围的场景） #

如果需要确保日期范围内的所有日期都展示（即使某日期两州都没数据，但你需求是跳过这种情况，所以保留HAVING条件），可以用递归CTE生成日期范围，再左连接会员表统计：

WITH date_range AS (
    SELECT '2020-10-07' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < '2021-01-10'
)
SELECT
    dr.dt AS DATE,
    'KA' AS FIRST_STATE,
    COALESCE(SUM(m1.members_joined), 0) AS FIRST_STATE_COUNT,
    'MH' AS SEC_STATE,
    COALESCE(SUM(m2.members_joined), 0) AS SEC_STATE_COUNT
FROM date_range dr
LEFT JOIN membership m1 ON dr.dt = m1.date AND m1.state = 'KA'
LEFT JOIN membership m2 ON dr.dt = m2.date AND m2.state = 'MH'
GROUP BY dr.dt
HAVING FIRST_STATE_COUNT > 0 OR SEC_STATE_COUNT > 0
ORDER BY dr.dt DESC;

关键点说明 #

• 修正了BETWEEN的日期顺序，确保逻辑正确。
• GROUP BY date按日期分组，得到每日的统计结果。
• HAVING条件过滤掉两州均无数据的日期，符合需求。
• COALESCE函数把NULL转换为0，保证即使某州当日无数据也显示0，和你的期望结果一致。

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