我有兩個表，我需要根據 CASE 陳述句回傳匹配結果。如果查詢在第一種情況下回傳資料。我們需要回傳資料。我們不需要檢查以下 case 陳述句。
這是我試過的
Select
CASE
WHEN foo.Marks ='Two' AND t1.Subject = 3 THEN foo.UniqueValue
END as 'Result',
CASE
WHEN foo.Marks ='0' AND t1.Subject = 3 THEN foo.UniqueValue
END as 'Result1',
CASE
WHEN foo.Marks IS NULL AND t1.Subject IS NULL THEN foo.UniqueValue
END as 'Result2'
from (
select t1.Marks, t1.Subject, t1.Student, foo.UniqueValue from `table1` t1
inner join `table2` t2 on t1.Student = t2.GroupName
where t2.GroupID = 2
)foo;
| ID | 分數 | 主題 | 學生 | 唯一值 |
|---|---|---|---|---|
| 1 | 二 | 3 | 食品級 | 空氣1 |
| 2 | 0 | 3 | 食品級 | 空氣2 |
| 3 | 空值 | 空值 | 食品級 | AIR3 |
| ID | 組ID | 團隊名字 |
|---|---|---|
| 1 | 2 | 食品級 |
| 2 | 3 | 酒吧 |
| 3 | 7 | FOO123 |
1級- >如果Marks和Subject匹配它應該回傳AIR1。
級別 2 -> If Marksis0和Subjectis3應該回傳AIR2。
如果以上兩種情況都沒有找到任何結果，則應檢查以下情況。
級別 3 -> 如果Marks是NULL和Subject是NULL應該回傳AIR3
如果在 Level1 中找到匹配的行，則應回傳不應檢查 Level2,3。
在決賽中，我應該只獲得與上述級別匹配的單行優先級。
uj5u.com熱心網友回復：
如果GroupID已知，
SELECT t1.UniqueValue
FROM table1 t1
JOIN table2 t2 ON t1.Student = t2.GroupName
WHERE t2.GroupID = 2
ORDER BY
CASE WHEN t1.Marks = 'Two' AND t1.Subject = 3 THEN 1
WHEN t1.Marks = '0' AND t1.Subject = 3 THEN 2
WHEN t1.Marks IS NULL AND t1.Subject IS NULL THEN 3
ELSE 4 END ASC
LIMIT 1
為每個得到一個結果 GroupID
WITH cte AS (
SELECT t1.UniqueValue, ROW_NUMBER() OVER(PARTITION BY t2.GroupID ORDER BY CASE WHEN t1.Marks = 'Two' AND t1.Subject = 3 THEN 1
WHEN t1.Marks = '0' AND t1.Subject = 3 THEN 2
WHEN t1.Marks IS NULL AND t1.Subject IS NULL THEN 3
ELSE 4 END ASC) rn
FROM table1 t1
JOIN table2 t2 ON t1.Student = t2.GroupName
)
SELECT UniqueValue
FROM cte
WHERE rn = 1
uj5u.com熱心網友回復：
為什么不直接做 ELSE 而不是 END 和 new CASE？
Select
(CASE
WHEN foo.Marks ='Two' AND t1.Subject = 3 THEN foo.UniqueValue
ELSE
WHEN foo.Marks ='0' AND t1.Subject = 3 THEN foo.UniqueValue
ELSE
WHEN foo.Marks IS NULL AND t1.Subject IS NULL THEN foo.UniqueValue
END) Result
from (
select t1.Marks, t1.Subject, t1.Student, foo.UniqueValue from `table1` t1
inner join `table2` t2 on t1.Student = t2.GroupName
where t2.GroupID = 2
)foo;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/385477.html
下一篇：對陣列欄位的int值求和