比較 2 個表後返回特定日期的數據計數

這是 dbfiddle 以便更好地理解，閱讀問題時請參考：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b89a233c62983a23f94120eb3779a831

我有 2 個表，稱為列表和日誌表。列表表包含產品參考號及其目前狀態。因此，假設如果它的狀態是目前發布並且稍後出售，則狀態更新為已售。這裡是參考號。在此表中是唯一的，因為 1 個產品的狀態可以更改。

現在我有另一個名為 Logs 的表，該表記錄了特定產品（由 refno 引用）在特定時間範圍內發生的所有狀態更改。列表表中的初始條目沒有記錄在這裡，但是一旦它的狀態發生變化，該條目就會顯示在這裡。

假設我有以下列表表（‘D’ => ‘Draft’, ‘A’ => ‘Action’, ‘Y’ => ‘Publish’, ‘S’ => ‘Sold’, ‘N’ => ‘讓’）：

INSERT INTO listings VALUES
(3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'),
(5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), 
(10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04');

到目前為止，每個狀態下的總計數將給出：

但是，如果我只想要 2020-10-01 的條目計數，它將在所有狀態下顯示 0，除了已售出，它會顯示 1。

現在，在 2020 年 10 月 1 日到今天的這段時間裡，如上所示，在列表表中輸入了一些值，而且對於某些情況，狀態也發生了變化。狀態表：

INSERT INTO logs VALUES
(1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), 
(3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06');

現在在我的列表中顯示的是狀態更改後的值。所以現在要獲取特定日期的總計數，我讓我的語句引用日誌表中的日期並分別減去 status_to，並添加 status_from。對此的查詢在上面提供的 dbfiddle 中。在這裡，我讓它返回發生在 2021 年 10 月 1 日或之前的數據，但它沒有給出正確的輸出。

此查詢的另一個問題是我無法返回最初發生的條目的數據。例如，就像我上面提到的，2020-10-01 上的數據值應該在已售出 1 下顯示，而在其他所有內容下顯示 0（所需輸出），但它不會這樣做，因為日誌表中沒有記錄何時最初添加了一個新條目。

所以基本上我在這裡想要的是查詢檢查我的兩個表中發生在今天和查詢中指定的日期之間的事務，並減少在我在查詢中指定的日期之後發生的任何事務。如果您想更簡單地解釋我想要實現的目標，請參考：

with X as (
 select l.*,
        (select status_from from logs
          where logs.refno = l.refno
            and logs.logtime >= '2021-10-01'
          order by logs.logtime limit 1) logstat
   from listings l
   where l.added_date < '2021-10-01')
select X.*, ifnull(X.logstat,X.status) stat20211001 from X;

見分貝小提琴

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