合并同一数据库3张表并筛选近7天数据的SQL查询需求

在名为Files的数据库中，存在RXInboundFileQueue、EligibilityInboundFileQueue、CostFileInboundFileQueue三张表，它们均包含OriginalName和FileCatalogedOn字段。需要编写SQL查询将这三张表合并为一个视图，展示近7天的文件记录。当前编写的代码仅能查询当日数据且未合并结果，需实现合并及7天时间范围筛选。

原代码如下：

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT
    OriginalName,  
    FileCatalogedOn
FROM RXInboundFileQueue
ORDER BY FileCatalogedOn DESC

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT
    OriginalName,
    FileCatalogedOn
FROM EligibilityInboundFileQueue
ORDER BY FileCatalogedOn DESC

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT
    OriginalName,
    FileCatalogedOn
FROM CostFileInboundFileQueue
ORDER BY FileCatalogedOn DESC

无需使用外连接，用UNION ALL合并三张表的记录是最直接高效的方式（若需去重可替换为UNION，但UNION ALL性能更优），同时调整时间范围为近7天，最终可创建视图持久化查询逻辑。

1. 近7天数据合并查询语句 #

SELECT
    OriginalName,
    FileCatalogedOn,
    'RX' AS FileSource -- 可选：添加来源标识，区分记录所属表
FROM RXInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
AND FileCatalogedOn < CAST(GETDATE() AS DATE) -- 若需包含当天数据，可改为 <= GETDATE()

UNION ALL

SELECT
    OriginalName,
    FileCatalogedOn,
    'Eligibility' AS FileSource
FROM EligibilityInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
AND FileCatalogedOn < CAST(GETDATE() AS DATE)

UNION ALL

SELECT
    OriginalName,
    FileCatalogedOn,
    'Cost' AS FileSource
FROM CostFileInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
AND FileCatalogedOn < CAST(GETDATE() AS DATE)

ORDER BY FileCatalogedOn DESC;

2. 创建视图 #

若需持久化该合并查询逻辑，执行以下语句：

USE Files; -- 切换至目标数据库
GO

CREATE VIEW Recent7DaysInboundFiles
AS
SELECT
    OriginalName,
    FileCatalogedOn,
    'RX' AS FileSource
FROM RXInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
AND FileCatalogedOn < CAST(GETDATE() AS DATE)

UNION ALL

SELECT
    OriginalName,
    FileCatalogedOn,
    'Eligibility' AS FileSource
FROM EligibilityInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
AND FileCatalogedOn < CAST(GETDATE() AS DATE)

UNION ALL

SELECT
    OriginalName,
    FileCatalogedOn,
    'Cost' AS FileSource
FROM CostFileInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
AND FileCatalogedOn < CAST(GETDATE() AS DATE);
GO

关键说明 #

• UNION ALL：直接合并三张表的符合条件记录，不做去重处理，性能优于UNION；若三张表存在完全重复的记录，可替换为UNION实现去重。
• 时间范围：DATEADD(DAY, -7, CAST(GETDATE() AS DATE))获取7天前的零点，CAST(GETDATE() AS DATE)获取当天零点，筛选的是过去7天的完整日期数据；若需包含当天实时数据，将第二个条件改为FileCatalogedOn <= GETDATE()。
• FileSource字段：可选添加，便于快速区分每条记录的来源表，利于后续问题排查。

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