PostgreSQL关联表查询：按指定用户匹配对应日期筛选

方法一：直接关联+多条件匹配 #

适合匹配规则较少的场景，直接通过条件组合精准筛选：

SELECT 
    u.user_name AS "User id",
    v.visit_date
FROM 
    "user" u
JOIN 
    visit v ON u.user_id = v.user_id
WHERE 
    (u.user_name = 'user1' AND v.visit_date = '2023-01-16')
    OR (u.user_name = 'user2' AND v.visit_date = '2023-01-17')
    OR (u.user_name = 'user4' AND v.visit_date = '2023-01-23');

方法二：构造映射表关联查询 #

如果需要维护大量用户-日期匹配规则，这种方式扩展性更强：

SELECT 
    u.user_name AS "User id",
    v.visit_date
FROM 
    "user" u
JOIN 
    visit v ON u.user_id = v.user_id
JOIN 
    (VALUES 
        ('user1', '2023-01-16'::DATE),
        ('user2', '2023-01-17'::DATE),
        ('user4', '2023-01-23'::DATE)
    ) AS required (name, date) 
    ON u.user_name = required.name AND v.visit_date = required.date;

两种方法都能得到你期望的结果：

• 方法一逻辑直白，少量规则时写起来更快；
• 方法二把匹配规则集中在VALUES子句里，后续新增或修改规则只需调整这里，更易维护。

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