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
: ...然后我就卡住了...
: 请问要怎么写比较好呢？
: 谢谢！