從相同結構的兩個表中檢索數據

我有一個 SQLite 數據庫，我使用以下查詢從兩個表中獲取數據。

select ie.* 
from (select * 
     from History where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from 
     union all 
     select * 
     from Pending where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from
    ) ie 
order by TimeStampCome desc LIMIT 100 OFFSET 1

這是最好和最有效的方法嗎？我已將警報對象儲存在數據庫中。所以很容易有超過一百萬個條目。

外層 ORDER BY 可能會導致對整個組合行集進行排序——至少在使伺服器能夠找到TimeStampCome從倒數第二個開始的最後 100 個條目的範圍內。當然，對一百萬個條目進行排序並不便宜。

我會嘗試通過對 UNION ALL 查詢的每個分支應用 LIMIT 來減少組合集。請注意，ORDER BY 不能直接應用於作為 UNION 分支的單個 SELECT。您將不得不使用更多嵌套（派生表或 CTE）來應用 ORDER BY，然後從該派生表/CTE 中進行選擇。這是一個派生表解決方案：

SELECT
 ie.*
FROM
 (
   SELECT
     *
   FROM
     (
       SELECT
         *
       FROM
         History
       WHERE
         Station = @station
         AND TimeStampCome <= @till
         AND TimeStampCome >= @from
       ORDER BY
         TimeStampCome DESC
         LIMIT 101
     ) AS s
   UNION ALL
   SELECT
     *
   FROM
     (
       SELECT
         *
       FROM
         Pending
       WHERE
         Station = @station
         AND TimeStampCome <= @till
         AND TimeStampCome >= @from
       ORDER BY
         TimeStampCome DESC
         LIMIT 101
     ) AS s
 ) AS ie
ORDER BY
 TimeStampCome DESC
 LIMIT 100
 OFFSET 1
;

這是一個使用 CTE 的變體，如果您使用的是支持Common Table Expressions的 SQLite 版本：

WITH
 HistoryFiltered AS
 (
   SELECT
     *
   FROM
     History
   WHERE
     Station = @station
     AND TimeStampCome <= @till
     AND TimeStampCome >= @from
   ORDER BY
     TimeStampCome DESC
     LIMIT 101
 ),
 PendingFiltered AS
 (
   SELECT
     *
   FROM
     Pending
   WHERE
     Station = @station
     AND TimeStampCome <= @till
     AND TimeStampCome >= @from
   ORDER BY
     TimeStampCome DESC
     LIMIT 101
 )
SELECT
 ie.*
FROM
 (
   SELECT * FROM HistoryFiltered
   UNION ALL
   SELECT * FROM PendingFiltered
 ) AS ie
ORDER BY
 TimeStampCome DESC
 LIMIT 100
 OFFSET 1
;

每個單獨的 SELECT 都使用LIMIT 101而不是LIMIT 100，因為起初不知道它們中的哪一個最終將具有TimeStampCome將被外部忽略的最新條目OFFSET 1。因此，每個源行集必須為最終的 SELECT 提供盡可能多的數據。

引用自：https://dba.stackexchange.com/questions/193519