sql踩坑：sql里子查询里有用到主表字段时，执行的过程类似for循环时，是一条一条对比的

WITH 
PreFilteredMessages as (SELECT msg.*
    FROM im_message msg
    WHERE create_time >= '2024-03-01 00:00:00' 
      AND create_time <= '2024-06-11 23:59:59'  
      AND from_uid_id IN (SELECT member_id FROM im_team)
			and g_ignore2 = 0
			and team_id = 17554
)

SELECT 
  t1.*
FROM 
    PreFilteredMessages t1
LEFT JOIN im_team t2 ON t1.team_id = t2.id
LEFT JOIN sr_merchant t3 ON t2.owner_id = t3.im_user_id
WHERE 
1
    AND t1.team_id not in(
		
        SELECT distinct t4_sub.team_id 
        FROM im_message t4_sub
        WHERE 
            t4_sub.team_id = t2.id 
            AND t4_sub.from_uid_id = t2.owner_id 
            AND t4_sub.timestamp >= t1.timestamp 
            AND t4_sub.g_ignore2 = 0
						)

sql里子查询里有用到主表字段

工作中碰到的坑，where里面的子查询，在我想象中，就是得到结果集就是包含（17554），但是在not in之后，主查询的结果集里仍然包含了team_id=17754的记录，这是因为我的子查询里面的条件用上了主表的字段进行判断，这就导致了not in时，是用主表每一条数据的team_id去对比子查询内的结果，其实对比的维度还是message（我自己的理解：这里的子查询其实和left join差不多），所以当子查询内部有不符合条件的就会返回null，然后此时主表用来对比的team_id依然是17754，符合not in条件，就会被筛选出来；

解决办法

将子查询内符合条件的team也作为应该临时表，然后not in这个表就行啦~

WITH 
PreFilteredMessages as (SELECT team_id, from_uid_id,create_time,`timestamp`
    FROM im_message
    WHERE create_time >= '2024-03-01 00:00:00' 
      AND create_time <= '2024-06-11 23:59:59'  
      AND from_uid_id IN (SELECT member_id FROM im_team)
			and g_ignore2 = 0
),

Receive_team_b AS(
SELECT 
DISTINCT t2.id
FROM 
    PreFilteredMessages t1
LEFT JOIN im_team t2 ON t1.team_id = t2.id
LEFT JOIN sr_merchant t3 ON t2.owner_id = t3.im_user_id
LEFT outer JOIN im_message t4 ON t4.team_id = t2.id AND t4.from_uid_id = t2.owner_id 
                        AND t4.`timestamp` >= t1.`timestamp` AND t4.g_ignore2 = 0
LEFT outer JOIN merchant_event_log t5 ON t3.id = t5.merchant_id AND t2.id = t5.g_im_team_id 
                        AND t5.event_type IN('click_phone', 'click_wechat')
                        AND t5.create_time >= t1.create_time 
WHERE 
    1
    AND (t4.id IS not NULL
    or t5.id IS not NULL)
)


SELECT 
  t3.name,count(distinct t2.id)
FROM 
    PreFilteredMessages t1
LEFT JOIN im_team t2 ON t1.team_id = t2.id
LEFT JOIN sr_merchant t3 ON t2.owner_id = t3.im_user_id
WHERE 
1
and t2.id not in (select id from Receive_team_b)
group by t3.id		
having t3.name is not null