我想弄清楚是否可以使用 MySQL 作業臺根據id、statement_one 和 latest_statement為我提供一些高級計算。我的基表如下所示：
基本上我希望測驗列如下所示：
- 對于 id=1，測驗列從 2019Q3 到 2021Q2 顯示“1”，其他所有顯示“0”。
- 對于 id=22，測驗列從 2019Q2 到 2021Q3 顯示“1”，其他所有顯示“0”。
任何人都知道我可以使用哪個函式根據每個 id 的 statement_one 和 last_statement 范圍創建計算？在此先感謝您的幫助！
uj5u.com熱心網友回復：
您可以嘗試這樣的查詢：
首先，我為每個獲取 first_statement ( MIN) 和 last_statement ( MAX) id，然后我JOIN cte使用原始表進行子查詢并使用CASE陳述句來計算test列。
MySql >= 8.0
WITH cte AS (SELECT id, MIN(statement_one) AS fs, MAX(latest_statement) AS ls
FROM t
GROUP BY id)
SELECT *,
CASE WHEN quarter >= cte.fs AND quarter <= cte.ls THEN 1 ELSE 0 END AS test
FROM t
INNER JOIN cte ON t.id = cte.id;
MySql < 8.0
SELECT *,
CASE WHEN quarter >= sq.fs AND quarter <= sq.ls THEN 1 ELSE 0 END AS test
FROM t
INNER JOIN (SELECT id, MIN(statement_one) AS fs, MAX(latest_statement) AS ls
FROM t
GROUP BY id) AS sq ON t.id = sq.id;
| ID | 四分之一 | statement_one | 最新宣告 | ID | FS | ls | 測驗 |
|---|---|---|---|---|---|---|---|
| 1 | 2019Q1 | 1 | 2019Q3 | 2021Q2 | 0 | ||
| 1 | 2019Q2 | 1 | 2019Q3 | 2021Q2 | 0 | ||
| 1 | 2019Q3 | 2019Q3 | 1 | 2019Q3 | 2021Q2 | 1 | |
| 1 | 2019Q4 | 1 | 2019Q3 | 2021Q2 | 1 | ||
| 1 | 2020Q1 | 1 | 2019Q3 | 2021Q2 | 1 | ||
| 1 | 2020Q2 | 1 | 2019Q3 | 2021Q2 | 1 | ||
| 1 | 2020Q3 | 1 | 2019Q3 | 2021Q2 | 1 | ||
| 1 | 2020Q4 | 1 | 2019Q3 | 2021Q2 | 1 | ||
| 1 | 2021Q1 | 1 | 2019Q3 | 2021Q2 | 1 | ||
| 1 | 2021Q2 | 2021Q2 | 1 | 2019Q3 | 2021Q2 | 1 | |
| 1 | 2021Q3 | 1 | 2019Q3 | 2021Q2 | 0 | ||
| 1 | 2021Q4 | 1 | 2019Q3 | 2021Q2 | 0 | ||
| 22 | 2019Q1 | 22 | 2019Q2 | 2021Q3 | 0 | ||
| 22 | 2019Q2 | 2019Q2 | 22 | 2019Q2 | 2021Q3 | 1 | |
| 22 | 2019Q3 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2019Q4 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2020Q1 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2020Q2 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2020Q3 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2020Q4 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2021Q1 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2021Q2 | 22 | 2019Q2 | 2021Q3 | 1 | ||
| 22 | 2021Q3 | 2021Q3 | 22 | 2019Q2 | 2021Q3 | 1 | |
| 22 | 2021Q4 | 22 | 2019Q2 | 2021Q3 | 0 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/324623.html