比較和匹配鍵系結，同時比較2個表并回傳計數

目前我有 2 個表，一個串列表和一個日志表。通過以下查詢，我試圖獲取特定日期的產品串列，并回傳正確的輸出。

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;

我已經在這個 dbfiddle( https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b0e433b59b5c4a4a0be3f0bc25d20124 ) 中嘗試了以下串列：

 (3, 'Publish','2021-05-02','2021-10-02','LP01'), (4, 'Action','2021-05-01','2021-05-01','LP02'),
 (5, 'Sold','2020-10-01','2020-10-01','LP03'), (6, 'Let','2021-05-01','2021-10-06','LP06'), 
 (10, 'Draft','2021-10-06','2021-10-06','LP05'), (11, 'Draft','2021-01-01','2021-01-01','LP04');

但是現在在我的實際資料庫中，狀態由特定的鍵系結表示。例如，('D' => 'Draft', 'A' => 'Action', 'Y' => 'Publish', 'S' => 'Sold', 'N' => 'Let')：

所以基本上這使我的實際串列表包含以下資料：

 (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');

現在我想要一種方法來獲得與上面的查詢相同的輸出，但是將它與新資料一起顯示，而不是顯示它現在以文字顯示的輸出，它應該將類似的狀態分組并回傳該資料的計數。例如：

基本上我想將這樣的東西添加到我認為的宣告中：

SELECT case status
when 'D' THEN 'Draft'
when 'A' THEN 'Action'
when 'Y' THEN 'Publish'
when 'S' THEN 'Sold'
when 'N' THEN 'Let'
END status_l ,COUNT(*) c from listings
group by status

uj5u.com熱心網友回復：

我建議您在資料庫中創建一個狀態表。使用此表，外連接您的查詢并計數：

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'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT 
  status.text,
  COUNT(Y.id) AS c 
from status
left join Y on Y.stat20211001 = status.code
group by status.code;

演示：https : //dbfiddle.uk/?rdbms=mysql_8.0&fiddle=673125506bb29b9d10719010aa2e00ae

uj5u.com熱心網友回復：

看起來你需要類似的東西

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'),
statustable AS (SELECT 'Draft' status UNION ALL
                SELECT 'Action' UNION ALL
                SELECT 'Publish' UNION ALL
                SELECT 'Sold' UNION ALL
                SELECT 'Let')
select X.*,
       COALESCE(X.logstat, statustable.status) stat20211001
from X
LEFT JOIN statustable ON X.status = LEFT(statustable.status, 1);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6b65365be8c3403b329a1b4b56212a03

嗯，不，我試過這個，但它沒有給出正確的輸出。我想要的輸出類似于我上面提供的表格 – JJM50

我的查詢為最終分組準備了一切 - 我認為這已經足夠了......

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'),
statustable AS (SELECT 'Draft' status UNION ALL
                SELECT 'Action' UNION ALL
                SELECT 'Publish' UNION ALL
                SELECT 'Sold' UNION ALL
                SELECT 'Let'),
preparedata AS ( select COALESCE(X.logstat, statustable.status) stat20211001
                 from X
                 LEFT JOIN statustable ON X.status = LEFT(statustable.status, 1) )
SELECT t1.status, COUNT(t2.stat20211001)
FROM statustable t1
LEFT JOIN preparedata t2 ON t1.status = t2.stat20211001
GROUP BY t1.status

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0edec9fbd3f07892e70f7a549117af8d

標籤：mysql sql

上一篇：兩個資料庫之間三列的MySQL更新條件

下一篇：錯誤1452MySQL-無法添加或更新子行