我的查詢：
select unique f.documentname,f.projectname,f.documentdescription,
f.P0BUSINESSUNIT,f.P0ZONENAME,f.P0REGIONNAME,f.P0GROUPNAME,l.processname,
f.ORIGINALCREATIONDATE
from p0findoc f, lcstep@sdrcpadm l
where f.inlifecyclename IS Not Null and
f.INLIFECYCLESTEP in (select l.LIFECYCLESTEP from lcstep@sdrcpadm ) and ORIGINALCREATIONDATE > '2021/01/01'
order by f.P0BUSINESSUNIT,f.P0ZONENAME,f.P0REGIONNAME,f.P0GROUPNAME ;
輸出：
DocumentName, Project Name, Doc Description...so on
PD71017 311503 Change Order 1 CIS Americas CIS Brazil CIS Not Applicable Deliver Services 2021/05/04-14:50:07:998
PD71017 311503 Change Order 1 CIS Americas CIS Brazil CIS Not Applicable Notification 2021/05/04-14:50:07:998
PD71017 311503 Change Order 1 CIS Americas CIS Brazil CIS Not Applicable PRJ Notification 2021/05/04-14:50:07:998
PD71017 311503 Change Order 1 CIS Americas CIS Brazil CIS Not Applicable Request CSS Closure 2021/05/04-14:50:07:998
PD71017 311503 Change Order 1 CIS Americas CIS Brazil CIS Not Applicable Validation Failure 2021/05/04-14:50:07:998
PD73834 311503 Change Order 4 CIS Americas CIS Brazil CIS Not Applicable Deliver Services 2021/09/13-17:24:18:433
PD73834 311503 Change Order 4 CIS Americas CIS Brazil CIS Not Applicable Notification 2021/09/13-17:24:18:433
在上面的輸出中，我只想為 PDxxxx 列“檔案名稱”設定唯一值。列行程名稱有多個值，因此只想擁有一個值。
uj5u.com熱心網友回復：
如果您想要一個（任何）行程名稱，則采用例如“第一個”或“最后一個”（最小或最大），但您必須聚合所有其他列（這也意味著您不再需要unique） ：
SELECT f.documentname,
f.projectname,
f.documentdescription,
f.p0businessunit,
f.p0zonename,
f.p0regionname,
f.p0groupname,
MAX (l.processname) processname,
f.originalcreationdate
FROM p0findoc f
JOIN lcstep@sdrcpadm l ON f.inlifecyclestep = l.lifecyclestep
WHERE f.inlifecyclename IS NOT NULL
AND originalcreationdate > DATE '2021-01-01'
GROUP BY f.documentname,
f.projectname,
f.documentdescription,
f.p0businessunit,
f.p0zonename,
f.p0regionname,
f.p0groupname,
f.originalcreationdate
ORDER BY f.p0businessunit,
f.p0zonename,
f.p0regionname,
f.p0groupname;
此外，您不想將日期與字串進行比較；這就是我在where子句中使用日期文字的原因。最后，切換到JOIN.
uj5u.com熱心網友回復：
我認為您得到了錯誤的答案，因為您沒有使用任何型別的唯一鍵將兩個表連接在一起。
將您的 FROM 子句更改為 From p0findoc f Inner Join lcstep@sdrcpadm l on f.InLifecycleStep=l.LifecycleStep
并洗掉子查詢 f.INLIFECYCLESTEP in (select l.LIFECYCLESTEP from lcstep@sdrcpadm )
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/376674.html