select
case when t1.status = 'finish' then 'green_light'
when t1.status = 'ready' then 'yellow_light'
else 'white_light'
end as light,
(其他欄位),
isnull(t3.flow,'') as flow
from process t1
inner join customer t2 on t1.id = t2.id and t1.name = t2.name
left join on process_flow t3 on t1.status = 'ready' and t1.no = t3.no
where t1.status != 'Y'
※ 引述《yasang (咩)》之銘言：
: 資料庫名稱：SQL2008
: 資料庫版本：不清楚...
: 內容/問題描述：
: 大家好
: 我目前的程式大概是這樣
: select * from (
: SELECT DISTINCT
: CASE
: WHEN process_status='finish' AND d_date>convert(varchar(8),getdate(),112)
: then green_light
: WHEN process_status='ready' AND d_date>convert(varchar(8),getdate(),112)
: then yellow_light ELSE white_light
: END AS LIGHT,
: customer.id,
: customer.name,
: process.status,
: d_date,(這些是欄位...)
: FROM process
: JOIN customer ON customer.id=process.id AND customer.name=process.name
: WHERE process.status<>'Y'
: ) as a
: where 1=1
: 我希望用process.status再做判斷
: 如果process是ready, 則多串另一個資料的欄位狀態
: ex. 加這欄
: ↓↓↓↓↓↓
: process.no ∣ process status ∣ process flow
: －－－－－ ＋－－－－－－－－＋－－－－－－－－－
: 1 ∣ Y ∣
: 2 ∣ ready ∣ process 2
: 3 ∣ finish ∣
: CASE
: WHEN process.status='ready' (select*from process
: JOIN process_flow ON process.no=process_flow.no
: THEN
: ...然後我就卡住了...
: 請問要怎麼寫比較好呢？
: 謝謝！
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.223.36.250
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1512713430.A.187.html