我有三列名稱，ClientId 和 GroupID
╔══════╦══════════╦═════════╗
║ Name ║ ClientId ║ GroupId ║
╠══════╬══════════╬═════════╣
║ abc ║ 1 ║ 1 ║
║ xyz ║ 2 ║ 2 ║
║ lmn ║ 3 ║ 3 ║
║ opq ║ 50 ║ 1 ║
║ def ║ 543 ║ 2 ║
║ rst ║ 115 ║ 0 ║
║ uvw ║ 5 ║ 5 ║
╚══════╩══════════╩═════════╝
我想在哪里創建一個：
- 如果 ClientId 和 GroupId 相同，則正常顯示名稱
- 如果 ClientId 和 GroupId 不同，則顯示它所在行的名稱
- 如果 GroupId = 0 則正常顯示名稱
╔══════╦══════════╦═════════╦══════════╗
║ Name ║ ClientId ║ GroupId ║ Fix_Name ║
╠══════╬══════════╬═════════╣══════════╣
║ abc ║ 1 ║ 1 ║ abc ║
║ xyz ║ 2 ║ 2 ║ xyz ║
║ lmn ║ 3 ║ 3 ║ lmn ║
║ opq ║ 50 ║ 1 ║ abc ║
║ def ║ 543 ║ 2 ║ xyz ║
║ rst ║ 115 ║ 0 ║ rst ║
║ uvw ║ 5 ║ 5 ║ uvw ║
╚══════╩══════════╩═════════╝══════════╝
我已經嘗試過幾次使用左連接和聯合，但在所有情況下它都不會顯示正確的名稱并且總是回傳為 0，在下面的情況下，它甚至會復制行并回傳比客戶端表更大的數字
with
p as (
select
Name,
ClientId,
GroupId,
case
when GroupId = 0 then Name
when ClientId in (GroupId) and GroupId not in (0)
then Name
else 0
end as Fix_Name
from client),
f as (
select
Name,
GroupId,
ClientId,
case
when ClientId not in (GroupId)
then GroupId
else ClientId
end as ClientId1
from client)
select
p.Name,
p.ClientId,
p.GroupId,
f.ClientId1,
p.Fix_Name
from p left join f on (p.ClientId = f.ClientId)
uj5u.com熱心網友回復：
SELECT t1.Name ,
t1.ClientId,
t1.GroupId,
CASE WHEN t1.ClientId = t1.GroupId -- if ClientId and GroupId are the same
THEN t1.Name -- it displays Name as normal
WHEN t1.GroupId = 0 -- if GroupId = 0
THEN t1.Name -- then display the name as normal
ELSE t2.Name -- displays Name of the row in which it is
END AS Fix_Name
FROM client t1
JOIN client t2 ON t2.ClientId = t1.GroupId
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/442571.html
標籤：mysql
上一篇：SQLException:沒有為jdbc:mysql找到合適的驅動程式。SQLState：08001。供應商錯誤：0