select TaskId,[Date],[User],
(select top 1 ProUser from 流程表 where taskId=a.TaskId and step='Cashier') as Cashier步骤处理人,
(select top 1 ProcTime from 流程表 where taskId=a.TaskId order by ProcTime desc) as 最后处理时间,
(select top 1 ProUser from 流程表 where taskId=a.TaskId and ProUser is null order by id) as  当前处理人
from 任务表 a

------解决方案--------------------
SQL code


with tt as 
(
select TaskId,[Date],[User],
(select top 1 ProUser from 流程表 where taskId=a.TaskId and step='Cashier') as Cashier步骤处理人,
(select top 1 ProcTime from 流程表 where taskId=a.TaskId order by ProcTime desc) as 最后处理时间,
(select top 1 ProUser from 流程表 where taskId=a.TaskId and ProUser is null order by id) as  当前处理人
from 任务表 a
)
select * From tt 
where Cashier步骤处理人=@参数1
and 最后处理时间> @参数2 and .....

------解决方案--------------------
SQL code

select * from tasks
--结果：
taskid  date                    users   amount
----------------------------------------------
1717    2012-08-03 00:00:00.000    admin    1000
1718    2012-08-03 00:00:00.000    admin    12000
----------------------------------------------
select * from process
--结果：
id      taskid   step          prouser   proctime
-----------------------------------------------
9447    1717    submit            user01    20120804
9448    1717    updateserno    user02    20120805
9449    1717    gm            user03    20120806
9450    1717    cashier            user04    
-----------------------------------------------
--使用下面sql语句查询
select dd.taskid,dd.date,dd.users,[cashier步骤处理人],mm.proctime [最后处理时间], [当前处理人]from 
(
select a.taskid TaskID,Date,a.users Users,b.prouser 'cashier步骤处理人',b.prouser '当前处理人' from tasks a left join (select *from process where step='cashier' ) b
on a.taskid=b.taskid)dd left join(select taskid,max(proctime) proctime from process group by taskid)mm
on dd.taskid=mm.taskid
--结果：
id      date                    users   cashier步骤处理人  最后处理时间     当前处理人
-------------------------------------------------
1717    2012-08-03 00:00:00.000    admin    user04           20120806            user04
1718    2012-08-03 00:00:00.000    admin    NULL             NULL             NULL
-------------------------------------------------
--如果需要查询条件的话，可以在后面添加where子句；例如：
where [当前处理人]='user04' and proctime>'2012-08-01'