標題: {發問}sql 兩段語法組成一個 [打印本頁]

---

作者: 暗黑    時間: 2011-5-25 21:18     標題: {發問}sql 兩段語法組成一個

各位先進..可否幫個忙
這兩段 sql  要組成一段，要如何連結起來呢，小弟試了很久，組出來都不對
可否幫組一下，並優化一下...



select *
from WorkItem W,ProcessInstance P ,
(SELECT MAX(P1.createdTime  ) AS m  FROM WorkItem W1 ,ProcessInstance P1
  Where  P1.contextOID= W1.contextOID
  And P1.processDefinitionId='PerformanceAssessment_003'   
  And  W1.workItemName='自評填寫'
  And  W1.performerOID in ('c7324cb4cd6a1004885f3408ccd4a6ff','ea0d8b04cd7e1004889adc3e5719cbd7','d9e70f44cd7d1004837699664bd98c42')
  GROUP BY W1.performerOID) AS B  
where
P.contextOID=W.contextOID
And P.processDefinitionId='PerformanceAssessment_003'   
And workItemName='自評填寫'
And performerOID in ('c7324cb4cd6a1004885f3408ccd4a6ff','ea0d8b04cd7e1004889adc3e5719cbd7','d9e70f44cd7d1004837699664bd98c42')
And P.createdTime =B.m
And P.currentState in ('3','1')




select  Us.OID,uid,ddate,depId,umasterId,cdepId
from
Users Us,
PerformanceAssessment_007_Grid0 G0,
(SELECT MAX(formSerialNumber) AS m  FROM PerformanceAssessment_007_Grid0 PG0
   Where  PG0.depId ='J010'
    And PG0.ddate='2011-上半年'
    And PG0.umasterId='MH0433') AS B  
where
G0.formSerialNumber = B.m
And G0.depId= 'J010'
And ddate='2011-上半年'
And umasterId='MH0433'
AND Us.id = uid

---

作者: 暗黑    時間: 2011-5-28 10:43

解出來了

1. select *
   
2. from WorkItem W,ProcessInstance P ,
   
3. (SELECT MAX(P1.createdTime  ) AS m  FROM WorkItem W1 ,ProcessInstance P1
   
4.   Where  P1.contextOID= W1.contextOID
   
5.   And P1.processDefinitionId='PerformanceAssessment_003'   
   
6.   And  W1.workItemName='自評填寫'
   
7.   And  W1.performerOID in (
   
8. select  Us.OID,uid,ddate,depId,umasterId,cdepId
   
9. from
   
10. Users Us,
    
11. PerformanceAssessment_007_Grid0 G0,
    
12. (SELECT MAX(formSerialNumber) AS m  ,performerOID FROM PerformanceAssessment_007_Grid0 PG0
    
13.    Where  PG0.depId ='J010'
    
14.     And PG0.ddate='2011-上半年'
    
15.     And PG0.umasterId='MH0433') AS C  
    
16. where
    
17. G0.formSerialNumber = C.m
    
18. And G0.depId= 'J010'
    
19. And ddate='2011-上半年'
    
20. And umasterId='MH0433'
    
21. AND Us.id = uid
    
22. )
    
23.   GROUP BY W1.performerOID) AS B  
    
24. where
    
25. P.contextOID=W.contextOID
    
26. And P.processDefinitionId='PerformanceAssessment_003'   
    
27. And workItemName='自評填寫'
    
28. And performerOID in (B.performerOID)
    
29. And P.createdTime =B.m
    
30. And P.currentState in ('3','1')

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)