搜索多个表并生成单个可分页的混合结果集

Question

我在 SQL Server 2008 数据库中有 4 个表，具有以下架构

Tutorials (TutorialID, Title, Approved, AddedDate)
Albums    (AlbumID, Title, Approved, AddedDate)
Profiles  (ProfileID, Title, Approved, AddedDate)
Polls     (PollID, Title, Approved, AddedDate)

我想提供搜索工具，它将搜索所有这些表，并将生成一个显示所有表的混合数据的可分页结果集。

Answer 1

您可能想要一个联合视图：

CREATE VIEW MixedData(Type, ID, Title, Approved, AddedDate) AS
    SELECT 'Tutorial' AS Type, TutorialID AS ID, Title, Approved, AddedDate
    UNION ALL
    SELECT 'Album'    AS Type, AlbumID    AS ID, Title, Approved, AddedDate
    UNION ALL
    SELECT 'Profile'  AS Type, ProfileID  AS ID, Title, Approved, AddedDate
    UNION ALL
    SELECT 'Poll'     AS Type, PollID     AS ID, Title, Approved, AddedDate

然后，您可以从应用所需条件MixedData中进行选择。 "AS 类型"和"AS ID"子句并非绝对必要。