Last created 10 records from all of the 3 tables

问题:

I have 3 tables

Table1

Primary_key  int
Forign_key  int
PostId
CreatedDate Datetime

Table2

Primary_key int
Forign_key int
LocationId
CreatedDate Datetime

Table3

Primary_key int
Forign_key int
UserId
OrganisationId
CreatedDate Datetime

How can I Select latest created 10 records from all of the 3 tables. Note that the 3 tables have different structure

sql server 2005

回答1:

If you want "last 10 per table"

SELECT
    *
FROM
    (
    SELECT
        Primary_key, Forign_key,
        UserId, OrganisationId, NULL AS LocationId, NULL AS PostID,
        CreatedDate, 'table3' AS Source,
        ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS rn
    FROM table3
    UNION ALL
    SELECT
        Primary_key, Forign_key,
        NULL, NULL, LocationId, NULL,
        CreatedDate, 'table2',
        ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS rn
    FROM table2
    UNION ALL
    SELECT
        Primary_key, Forign_key,
        NULL, NULL, NULL, PostID,
        CreatedDate, 'table1',
        ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS rn
    FROM table
    ) T
WHERE
   t.rn <= 10

For "last 10 over all tables"

SELECT TOP 10
    *
FROM
    (
    SELECT
        Primary_key, Forign_key,
        UserId, OrganisationId, NULL AS LocationId, NULL AS PostID,
        CreatedDate, 'table3' AS Source
    FROM table3
    UNION ALL
    SELECT
        Primary_key, Forign_key,
        NULL, NULL, LocationId, NULL,
        CreatedDate, 'table2'
    FROM table2
    UNION ALL
    SELECT
        Primary_key, Forign_key,
        NULL, NULL, NULL, PostID,
        CreatedDate, 'table1'
    FROM table
    ) T
ORDER BY
    CreatedDate DESC