其實還有更簡單的方法
//查詢有效期內的計劃任務節點
string sql = "select g.grade_name, "
+ " '' as degree_name,"
+ " '' as reason, "
+ " 'project_node' as node_type, "
+ " '' as fact_node_name, "
+ " p.create_time, "
+ " p.id, p.project_id, p.node_name, p.deptids,"
+ " p.node_begin_time, p.node_end_time, p.worklevel"
+ " from hrp_project_node p "
+ " left join hrp_cycle_grade g on g.id = p.worklevel "
+ " where p.state = 0 and p.node_state <> 3 "
+ " and UNIX_TIMESTAMP(p.node_begin_time) < UNIX_TIMESTAMP('" + DateTime.Now.ToString() + "') "
//優先查出所有
//+ " and UNIX_TIMESTAMP(p.node_end_time) > UNIX_TIMESTAMP('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "') "
+ " and (p.exeids like '" + userId + "%' or p.exeids like '%," + userId + "%') ";
//合并還未執行的任務節點
sql += " union all "
+ " select g.grade_name, "
+ " '' as degree_name,"
+ " '' as reason, "
+ " 'fact_node' as node_type, "
+ " '' as fact_node_name, "
+ " f.create_time, "
+ " f.id, f.project_id, f.node_name, f.deptids,"
+ " f.fact_begin_time, f.fact_end_time, f.worklevel"
+ " from hrp_fact_node_execute f "
+ " left join hrp_cycle_grade g on g.id = f.worklevel "
+ " where f.state = 0 and f.fact_state_id = 0 "
+ " and (f.exeids like '" + userId + "%' or f.exeids like '%," + userId + "%') ";
sql = string.Format("select * from ({0}) as t1 order by create_time desc", sql);
ps: 我記得這樣的一篇帖子好像發表過, 奇怪為什么不找不到了??
當時還參考了別人的博客,
迷糊ing.... 唉, 被 google.com搞得心神不寧啊~~~~
posted on 2009-06-25 16:27
黃小二 閱讀(1555)
評論(0) 編輯 收藏 所屬分類:
[DB].Oracle 、
[DB].MySQL 、
[DB].SQL Server