题解 | #小破站9月新会员人数#

子查询去重出九月一号前的用户，使用a not in b的逻辑判断九月的新会员 解法1

重点1: count distinct统计 重点2: not in 子查询

SELECT 
    COUNT(DISTINCT user_id) AS new_cnt
FROM 
    detail_list_tb
WHERE 
    begin_date BETWEEN '2021-9-1' AND '2021-9-30'
	AND user_id NOT IN 
    (
		SELECT 
            DISTINCT user_id
		FROM 
            detail_list_tb
		WHERE 
            begin_date < '2021-9-1'
	);

解法2

重点1: 左连接 left join的使用 重点2: 子查询，中去重连接

SELECT
    COUNT(a.user_id)
FROM
(
    -- 九月的会员
    SELECT 
        DISTINCT user_id
    FROM 
        detail_list_tb
    WHERE 
        begin_date BETWEEN '2021-9-1' AND '2021-9-30'
) a
LEFT JOIN
(
    -- 旧会员
    SELECT 
        DISTINCT user_id
    FROM 
        detail_list_tb
    WHERE 
        begin_date < '2021-9-1'
) b
-- 九月的会员与旧会员相连，且九月的会员id不在旧会员记录中的即为新会员
ON a.user_id = b.user_id AND b.user_id IS NULL