我有一個表“空缺”，它由一個 id、vacancy_status_id、published_date、創建和更新組成。
created 表示該行在資料庫中創建的時間，updated 表示該行中的任何值上次更改的時間，published_date 表示空缺進入狀態 2（活動）的時間。
除此之外，我創建了一個日歷表，其中包含從 2010-01-01 到 2050-12-31 的所有日期（如下所述：https ://ubiq.co/database-blog/fill-missing-dates-in- mysql/ )
我想獲取從我的資料庫中創建第一個空缺之日起每天的活動空缺數量和非活動空缺數量。
棘手的部分是，活躍職位空缺是根據“published_date”欄位計算的，而非活躍職位空缺則基于“更新”欄位。
我可以將此查詢撰寫為 2 個單獨的查詢，但我無法將它們合并為一個查詢：
SELECT c.datefield AS created,
SUM(case when v.vacancy_status_id=2 then 1 else 0 end) as amount_active_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.published_date)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield
SELECT c.datefield AS created,
SUM(case when v.vacancy_status_id=3 OR v.vacancy_status_id=4 then 1 else 0 end) as amount_inactive_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.updated)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield
我如何將這兩者結合在一個查詢中？
我的結果應該顯示：
創建 | amount_active_vacancies | amount_inactive_vacancies
uj5u.com熱心網友回復：
如果您的兩個查詢都正確，您可以將它們連接在一起
SELECT a.created, a.amount_active_vacancies, b.amount_inactive_vacancies
FROM (
SELECT c.datefield AS created,
SUM(case when v.vacancy_status_id=2 then 1 else 0 end) as amount_active_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.published_date)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield
) a
JOIN (
SELECT c.datefield AS created,
SUM(case when v.vacancy_status_id=3 OR v.vacancy_status_id=4 then 1 else 0 end) as amount_inactive_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.updated)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield
) b
WHERE a.created = b.created
或結合條件
SELECT c.datefield AS created,
SUM(case when v.vacancy_status_id=2 then 1 else 0 end) as amount_active_vacancies,
SUM(case when v.vacancy_status_id=3 OR v.vacancy_status_id=4 then 1 else 0 end) as amount_inactive_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.published_date) OR c.datefield = DATE(v.updated)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/324619.html