SQL三列交叉连接后按规则过滤用户无对应投票记录空行问题

核心逻辑 #

• 先从votes表提取所有存在投票行为的(user, tick)唯一组合，过滤掉完全无投票的用户-tick配对
• 将上述有效组合与ticks表的全量日期做交叉连接，保证每个有效配对覆盖所有要求的日期
• 左连接votes表匹配对应日期的投票值，无投票记录的日期vote字段自动返回NULL

最终查询语句 #

SELECT 
    ut.user, 
    t.tick, 
    t.dait, 
    v.vote,
    ROW_NUMBER() OVER (ORDER BY ut.user, t.tick, t.dait) AS ilocat
FROM ticks t
CROSS JOIN (
    -- 仅保留有至少一次投票的用户-tick组合
    SELECT DISTINCT user, tick 
    FROM votes
) ut
LEFT JOIN votes v
    ON v.dayt = t.dait 
    AND v.tick = t.tick 
    AND v.user = ut.user
ORDER BY ut.user, t.tick, t.dait;

优化说明 #

原查询的CROSS JOIN子查询包含了dayt字段，会生成多余的维度组合，导致无投票的用户-tick配对也被纳入结果。本次修改移除了子查询中的冗余字段，同时删除了不必要的GROUP BY逻辑，查询效率更高，结果完全符合过滤规则：

• 无任何投票记录的用户-tick配对不会出现在结果中
• 存在至少一次投票的用户-tick配对会返回全日期的记录，未投票日期vote为NULL

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