SELECT (SELECT COUNT(1) TaskPendingCount
FROM Opt_TaskView
WHERE ActualStartTime IS NULL
AND Status IS NULL
AND ProjectID = :ProjectID1
AND TaskOwnerID = :TaskOwnerID1,
(SELECT COUNT(1) TaskDoingCount
FROM Opt_TaskView
WHERE Status = 'Processing'
AND ProjectID = :ProjectID2
AND TaskOwnerID = :TaskOwnerID2) TaskDoingCount,
(SELECT COUNT(1) TaskFinishedCount
FROM Opt_TaskHistoryView
WHERE 1 = 1
AND ProjectID = :ProjectID3
AND TaskOwnerID = :TaskOwnerID3) TaskFinishedCount
FROM DUAL如果 SQL 写死,查询正确,但是如果参数传入的话,除了中间那个字段正确外,其他2个都为 0
请指教
FROM Opt_TaskView
WHERE ActualStartTime IS NULL
AND Status IS NULL
AND ProjectID = :ProjectID1
AND TaskOwnerID = :TaskOwnerID1,
(SELECT COUNT(1) TaskDoingCount
FROM Opt_TaskView
WHERE Status = 'Processing'
AND ProjectID = :ProjectID2
AND TaskOwnerID = :TaskOwnerID2) TaskDoingCount,
(SELECT COUNT(1) TaskFinishedCount
FROM Opt_TaskHistoryView
WHERE 1 = 1
AND ProjectID = :ProjectID3
AND TaskOwnerID = :TaskOwnerID3) TaskFinishedCount
FROM DUAL如果 SQL 写死,查询正确,但是如果参数传入的话,除了中间那个字段正确外,其他2个都为 0
请指教
--注意大小写,如果是字符的话要用单引号
SQL> ED
已写入 file afiedt.buf 1 SELECT
2 (SELECT COUNT(1) FROM EMP WHERE JOB='&JOB1') JOB1,
3 (SELECT COUNT(1) FROM EMP WHERE JOB='&JOB2') JOB2
4* FROM DUAL
SQL> /
输入 job1 的值: SALESMAN
原值 2: (SELECT COUNT(1) FROM EMP WHERE JOB='&JOB1') JOB1,
新值 2: (SELECT COUNT(1) FROM EMP WHERE JOB='SALESMAN') JOB1,
输入 job2 的值: MANAGER
原值 3: (SELECT COUNT(1) FROM EMP WHERE JOB='&JOB2') JOB2
新值 3: (SELECT COUNT(1) FROM EMP WHERE JOB='MANAGER') JOB2 JOB1 JOB2
---------- ----------
4 3
paramList.Add(new OracleParameter(":PendingProjectID", "3cc15a4e-0017-482c-8f33-7cb3546e4b27"));
sqlTaskDoing.Append(" AND ProjectID = :DoingProjectID");
paramList.Add(new OracleParameter(":DoingProjectID", "3cc15a4e-0017-482c-8f33-7cb3546e4b27"));
sqlTaskFinished.Append(" AND ProjectID = :FinishedProjectID");
paramList.Add(new OracleParameter(":FinishedProjectID", "3cc15a4e-0017-482c-8f33-7cb3546e4b27"));sqlTaskPending.Append(" AND TaskOwnerID = :PendingTaskOwnerID");
paramList.Add(new OracleParameter(":PendingTaskOwnerID", "fddfbed2-f2ef-4a3a-8d54-6528d514878f"));
sqlTaskDoing.Append(" AND TaskOwnerID = :DoingTaskOwnerID");
paramList.Add(new OracleParameter(":DoingTaskOwnerID", "fddfbed2-f2ef-4a3a-8d54-6528d514878f"));
sqlTaskFinished.Append(" AND TaskOwnerID = :FinishedTaskOwnerID");
paramList.Add(new OracleParameter(":FinishedTaskOwnerID", "fddfbed2-f2ef-4a3a-8d54-6528d514878f"));
&参数名
(SELECT COUNT(1) TaskPendingCount
FROM Opt_TaskView
WHERE ActualStartTime IS NULL
AND Status IS NULL
AND ProjectID = :ProjectID1
AND TaskOwnerID = :TaskOwnerID1)
,(SELECT COUNT(1) TaskDoingCount
FROM Opt_TaskView
WHERE Status = 'Processing'
AND ProjectID = :ProjectID2
AND TaskOwnerID = :TaskOwnerID2)
,(SELECT COUNT(1) TaskFinishedCount
FROM Opt_TaskHistoryView
WHERE 1 = 1
AND ProjectID = :ProjectID3
AND TaskOwnerID = :TaskOwnerID3)
FROM DUAL