select a.FLOW_ID,
max(a.FLOW_VER) as FLOW_VER,
a.FLOW_NAME,
f.STEP_ID,
f.STEP_NAME,
e.NODE_KEY,
STU.TRACK_RULE_ID,
STU.TRACK_RULE_NAME
from SBRM_FLOW a
left join SBRM_FLOW_NODE_PATH c
on c.DIA_KEY = a.DIA_KEY
left join SBRM_FLOW_NODES e
on e.NODE_KEY = c.FROM_NODE_KEY
left join SBRM_STEP f
on f.STEP_KEY = e.STEP_KEY
left JOIN SBRM_STEP_TRACK_RULE SSTR
ON f.STEP_KEY = SSTR.STEP_KEY
left JOIN SBRM_TRACK_RULE STU
ON STU.TRACK_RULE_KEY = SSTR.TRACK_RULE_KEY
where a.FLOW_ID = 'F-3P4O1U'
group by a.FLOW_ID,
a.FLOW_NAME,
f.STEP_ID,
f.STEP_NAME,
e.NODE_KEY,
STU.TRACK_RULE_ID,
STU.TRACK_RULE_NAME
order by flow_id, NODE_KEY desc
这种写法执行得到的结果是所有FLOW_VER(这是流程的一个版本号)的结果,但其实我只是想要得到一个最大版本号的流程的信息而已,比如,这里有4个版本号,那上面的写法会得出4个版本号的所有结果,但我只想要版本4的结果,请高人指点。
max(a.FLOW_VER) as FLOW_VER,
a.FLOW_NAME,
f.STEP_ID,
f.STEP_NAME,
e.NODE_KEY,
STU.TRACK_RULE_ID,
STU.TRACK_RULE_NAME
from SBRM_FLOW a
left join SBRM_FLOW_NODE_PATH c
on c.DIA_KEY = a.DIA_KEY
left join SBRM_FLOW_NODES e
on e.NODE_KEY = c.FROM_NODE_KEY
left join SBRM_STEP f
on f.STEP_KEY = e.STEP_KEY
left JOIN SBRM_STEP_TRACK_RULE SSTR
ON f.STEP_KEY = SSTR.STEP_KEY
left JOIN SBRM_TRACK_RULE STU
ON STU.TRACK_RULE_KEY = SSTR.TRACK_RULE_KEY
where a.FLOW_ID = 'F-3P4O1U'
group by a.FLOW_ID,
a.FLOW_NAME,
f.STEP_ID,
f.STEP_NAME,
e.NODE_KEY,
STU.TRACK_RULE_ID,
STU.TRACK_RULE_NAME
order by flow_id, NODE_KEY desc
这种写法执行得到的结果是所有FLOW_VER(这是流程的一个版本号)的结果,但其实我只是想要得到一个最大版本号的流程的信息而已,比如,这里有4个版本号,那上面的写法会得出4个版本号的所有结果,但我只想要版本4的结果,请高人指点。
去掉group by
測測看結果是否正確
select a.FLOW_ID,
a.FLOW_VER ,
a.FLOW_NAME,
f.STEP_ID,
f.STEP_NAME,
e.NODE_KEY,
STU.TRACK_RULE_ID,
STU.TRACK_RULE_NAME
from SBRM_FLOW a
left join SBRM_FLOW_NODE_PATH c
on c.DIA_KEY = a.DIA_KEY
left join SBRM_FLOW_NODES e
on e.NODE_KEY = c.FROM_NODE_KEY
left join SBRM_STEP f
on f.STEP_KEY = e.STEP_KEY
left JOIN SBRM_STEP_TRACK_RULE SSTR
ON f.STEP_KEY = SSTR.STEP_KEY
left JOIN SBRM_TRACK_RULE STU
ON STU.TRACK_RULE_KEY = SSTR.TRACK_RULE_KEY
where a.FLOW_ID = 'F-3P4O1U'
AND NOT EXISTS(SELECT 1 FROM SBRM_FLOW WHERE FLOW_ID=a.FLOW_ID AND FLOW_VER>a.FLOW_VER)
order by flow_id, NODE_KEY DESC
select a.FLOW_ID,
a.FLOW_VER as FLOW_VER,
a.FLOW_NAME,
f.STEP_ID,
f.STEP_NAME,
e.NODE_KEY,
STU.TRACK_RULE_ID,
STU.TRACK_RULE_NAME
from SBRM_FLOW a
left join SBRM_FLOW_NODE_PATH c
on c.DIA_KEY = a.DIA_KEY
left join SBRM_FLOW_NODES e
on e.NODE_KEY = c.FROM_NODE_KEY
left join SBRM_STEP f
on f.STEP_KEY = e.STEP_KEY
left JOIN SBRM_STEP_TRACK_RULE SSTR
ON f.STEP_KEY = SSTR.STEP_KEY
left JOIN SBRM_TRACK_RULE STU
ON STU.TRACK_RULE_KEY = SSTR.TRACK_RULE_KEY
where a.FLOW_ID = 'F-3P4O1U' and
a.FLOW_VER=
(select max(FLOW_VER) lastFLOW_VER
from SBRM_FLOW a2 where a.FLOW_ID=a2.FLOW_ID)
order by flow_id, NODE_KEY desc