基于特定条件关联三张表的查询筛选需求及表结构说明

嘿，我来帮你搞定这三张表的关联查询！先理清楚表之间的关联逻辑，再给你几个实用的查询示例，你可以根据自己的实际需求调整：

先明确三张表的结构 #

t1（已上传文件表）

t2（已替换文件备份表）

t3（文件访问日志表）

表关联核心逻辑 #

• t2的fileid和t1的id关联，代表这条备份记录对应的原文件
• t3的fileid需要结合is_backup字段判断关联表：is_backup=0时关联t1（访问的是当前生效的原文件），is_backup=1时关联t2（访问的是被替换后的备份文件）

实用查询示例 #

场景1：查询所有文件（含原文件和备份）的完整访问日志

这个查询会把日志和对应的文件信息关联起来，清晰区分原文件和备份的访问记录：

SELECT
    -- 日志核心信息
    t3.id AS log_id,
    t3.userid,
    t3.action,
    t3.access_time,
    t3.ip_Address,
    t3.is_backup,
    -- 关联文件信息
    CASE
        WHEN t3.is_backup = 0 THEN t1.filename
        ELSE t2.filename
    END AS filename,
    CASE
        WHEN t3.is_backup = 0 THEN t1.display_filename
        ELSE t2.display_filename
    END AS display_filename,
    CASE
        WHEN t3.is_backup = 0 THEN t1.uploaded_path
        ELSE t2.uploaded_path
    END AS uploaded_path
FROM t3
LEFT JOIN t1 ON t3.fileid = t1.id AND t3.is_backup = 0
LEFT JOIN t2 ON t3.fileid = t2.id AND t3.is_backup = 1
-- 可添加自定义筛选条件，比如按用户、时间范围
-- WHERE t3.userid = 'U1001' AND t3.access_time BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY t3.access_time DESC;

场景2：查询某一个原文件的所有访问记录（含替换后的备份访问）

假设要查询原文件ID为123的所有访问痕迹，包括替换前访问原文件、替换后访问备份文件的记录：

SELECT
    t3.id AS log_id,
    t3.userid,
    t3.action,
    t3.access_time,
    t3.ip_Address,
    t3.is_backup,
    COALESCE(t1.display_filename, t2.display_filename) AS display_filename
FROM t3
LEFT JOIN t1 ON t3.fileid = t1.id AND t3.is_backup = 0 AND t1.id = 123
LEFT JOIN t2 ON t3.fileid = t2.id AND t3.is_backup = 1 AND t2.fileid = 123
WHERE t1.id IS NOT NULL OR t2.fileid IS NOT NULL
ORDER BY t3.access_time DESC;

场景3：统计每个文件（含备份）的访问次数

用来统计文件的热度，区分原文件和备份的访问量：

SELECT
    CASE
        WHEN t3.is_backup = 0 THEN CONCAT('原文件[ID:', t1.id, ']')
        ELSE CONCAT('备份文件[对应原文件ID:', t2.fileid, ']')
    END AS file_label,
    COALESCE(t1.display_filename, t2.display_filename) AS display_filename,
    COUNT(t3.id) AS access_count
FROM t3
LEFT JOIN t1 ON t3.fileid = t1.id AND t3.is_backup = 0
LEFT JOIN t2 ON t3.fileid = t2.id AND t3.is_backup = 1
GROUP BY file_label, display_filename
ORDER BY access_count DESC;

如果有更具体的筛选需求（比如按访问动作、IP段过滤），直接在对应的查询中添加WHERE条件即可。

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