SET @MYCNT = 0;
SELECT *
,(@MYCNT := @MYCNT + 1) AS ROWNUM
FROM(
SELECT DISTINCT
A.ID AS ID
,A.FlowID AS FlowID
,A.InstanceName AS TITLE
,B.F_NO AS NO
,B.F_NAME AS NAME
,A.StartTime AS STARTTIME
,case A.EndFlg when '1' then E.F_NAME when '2' then "结束" end AS NODE_NAME
,A.EndFlg
FROM
T_WF_INSTANCE A
,T_WF_SYS_FLOW B
WHERE
A.FlowID = B.ID
AND A.StartUserID = 119
AND A.EndFlg = 2
LEFT JOIN
(SELECT *
from
T_WF_CURRENT C
,T_WF_SYS_ROUTE D
,T_WF_SYS_NODE E
where
A.EndFlg=1
AND A.ID = C.InstanceID
AND C.UserID <> 119
AND C.FlowID =D.FLOWID
AND C.RouteID =D.ID
AND D.F_TO =E.ID)
ORDER BY ID DESC)CC
SELECT *
,(@MYCNT := @MYCNT + 1) AS ROWNUM
FROM(
SELECT DISTINCT
A.ID AS ID
,A.FlowID AS FlowID
,A.InstanceName AS TITLE
,B.F_NO AS NO
,B.F_NAME AS NAME
,A.StartTime AS STARTTIME
,case A.EndFlg when '1' then E.F_NAME when '2' then "结束" end AS NODE_NAME
,A.EndFlg
FROM
T_WF_INSTANCE A
,T_WF_SYS_FLOW B
WHERE
A.FlowID = B.ID
AND A.StartUserID = 119
AND A.EndFlg = 2
LEFT JOIN
(SELECT *
from
T_WF_CURRENT C
,T_WF_SYS_ROUTE D
,T_WF_SYS_NODE E
where
A.EndFlg=1
AND A.ID = C.InstanceID
AND C.UserID <> 119
AND C.FlowID =D.FLOWID
AND C.RouteID =D.ID
AND D.F_TO =E.ID)
ORDER BY ID DESC)CC
(SELECT * 这个地方报错
AND A.EndFlg = 2 )
LEFT JOIN
(SELECT *
添加上面红色部分
谁能吧下面的2个SQL用1个SQL写出来
第1个SQL如下 :
sql.Append(" SET @MYCNT = 0; ");
sql.Append(" SELECT * ");
sql.Append(" ,(@MYCNT := @MYCNT + 1) AS ROWNUM ");
sql.Append(" FROM( ");
sql.Append(" SELECT ");
sql.Append(" A.ID AS ID");
sql.Append(" ,A.FlowID AS FlowID");
sql.Append(" ,A.InstanceName AS TITLE");
sql.Append(" ,B.F_NO AS NO");
sql.Append(" ,B.F_NAME AS NAME");
sql.Append(" ,A.StartTime AS STARTTIME");
sql.Append(" ,E.F_NAME AS NODE_NAME");
sql.Append(" FROM ");
sql.Append(" T_WF_INSTANCE A");
sql.Append(" ,T_WF_SYS_FLOW B ");
sql.Append(" ,T_WF_CURRENT C ");
sql.Append(" ,T_WF_SYS_ROUTE D ");
sql.Append(" ,T_WF_SYS_NODE E ");
sql.Append(" WHERE ");
sql.Append(" EndFlg=1");
sql.Append(" AND A.FlowID = B.ID ");
sql.Append(" AND A.ID = C.InstanceID ");
sql.Append(" AND C.UserID <> ?StartUserID ");
sql.Append(" AND A.StartUserID = ?StartUserID ");
sql.Append(" AND C.FlowID =D.FLOWID ");
sql.Append(" AND C.RouteID =D.ID ");
sql.Append(" AND D.F_TO =E.ID "); sql.Append(" ORDER BY ID DESC)CC ");
第2个SQL如下
sql.Append(" SET @MYCNT = 0; ");
sql.Append(" SELECT * ");
sql.Append(" ,(@MYCNT := @MYCNT + 1) AS ROWNUM ");
sql.Append(" FROM( ");
sql.Append(" SELECT ");
sql.Append(" A.ID AS ID");
sql.Append(" ,A.FlowID AS FlowID");
sql.Append(" ,A.InstanceName AS TITLE");
sql.Append(" ,B.F_NO AS NO");
sql.Append(" ,B.F_NAME AS NAME");
sql.Append(" ,A.StartTime AS STARTTIME");
sql.Append(" ,A.UpdTime AS ENDTIME");
sql.Append(" FROM ");
sql.Append(" T_WF_INSTANCE A");
sql.Append(" ,T_WF_SYS_FLOW B ");
sql.Append(" WHERE ");
sql.Append(" EndFlg=2");
sql.Append(" AND A.FlowID = B.ID ");
sql.Append(" AND A.StartUserID = ?StartUserID ");
sql.Append(" ORDER BY ID DESC)CC ");
SELECT *
,(@MYCNT := @MYCNT + 1) AS ROWNUM
FROM(
SELECT
A.ID AS ID
,A.FlowID AS FlowID
,A.InstanceName AS TITLE
,B.F_NO AS NO
,B.F_NAME AS NAME
,A.StartTime AS STARTTIME
,E.F_NAME AS NODE_NAME
FROM
T_WF_INSTANCE A
,T_WF_SYS_FLOW B
,T_WF_CURRENT C
,T_WF_SYS_ROUTE D
,T_WF_SYS_NODE E
WHERE
EndFlg=1
AND A.FlowID = B.ID
AND A.ID = C.InstanceID
AND C.UserID <> ?StartUserID
AND A.StartUserID = ?StartUserID
AND C.FlowID =D.FLOWID
AND C.RouteID =D.ID
AND D.F_TO =E.ID
union all
SELECT
A.ID AS ID
,A.FlowID AS FlowID
,A.InstanceName AS TITLE
,B.F_NO AS NO
,B.F_NAME AS NAME
,A.StartTime AS STARTTIME
,A.UpdTime AS ENDTIME
FROM
T_WF_INSTANCE A
,T_WF_SYS_FLOW B
WHERE
EndFlg=2
AND A.FlowID = B.ID
AND A.StartUserID = ?StartUserID
) CC ORDER BY ID DESC 上面红色那部分不知道你填什么变量,你自己改下吧