問題描述
如何組合表和視圖？ (How do combine tables and views?)
How do you combine tables and views into one query? I have two Select statements one is inner join already I would like to combine the table and views in the second select and make one query.
SELECT PROJECT.PROJ_ID,
PROJECT.HULL_NUM_ID,
PROJECT.SHIP_TYPE_CD,
PROJECT.PROJ_NM,
ICP_SUMMARY.MOD_STOP_DT
FROM BAIM.ICP_SUMMARY ICP_SUMMARY
INNER JOIN BAIM.PROJECT PROJECT
ON ICP_SUMMARY.PROJ_ID = PROJECT.PROJ_ID
SELECT ICP_SUMMARY_JS_VW.PROJ_ID,
ICP_SUMMARY_JS_VW.LBR_EST_MANDAYS,
ICP_SUMMARY_JS_VW.LBR_ICP_MANDAYS,
ICP_SUMMARY_JS_VW.MATL_EST_COST,
ICP_SUMMARY_JS_VW.MATL_ICP_COST,
ICP_SUMMARY_JS_VW.TOTAL_EST_COST,
ICP_SUMMARY_JS_VW.TOTAL_ICP
FROM BAIM.ICP_SUMMARY_JS_VW ICP_SUMMARY_JS_VW
參考解法
方法 1:
You treat views just like you would a table. You can perform joins on views, and you can have as many views as you need in the select statement.
方法 2:
You just need to JOIN on the table and the view, similar to this:
SELECT PROJECT.PROJ_ID,
PROJECT.HULL_NUM_ID,
PROJECT.SHIP_TYPE_CD,
PROJECT.PROJ_NM,
ICP_SUMMARY.MOD_STOP_DT,
ICP_SUMMARY_JS_VW.PROJ_ID,
ICP_SUMMARY_JS_VW.LBR_EST_MANDAYS,
ICP_SUMMARY_JS_VW.LBR_ICP_MANDAYS,
ICP_SUMMARY_JS_VW.MATL_EST_COST,
ICP_SUMMARY_JS_VW.MATL_ICP_COST,
ICP_SUMMARY_JS_VW.TOTAL_EST_COST,
ICP_SUMMARY_JS_VW.TOTAL_ICP
FROM BAIM.ICP_SUMMARY ICP_SUMMARY
INNER JOIN BAIM.PROJECT PROJECT
ON ICP_SUMMARY.PROJ_ID = PROJECT.PROJ_ID
INNER JOIN BAIM.ICP_SUMMARY_JS_VW ICP_SUMMARY_JS_VW -- do JOIN Here
ON ICP_SUMMARY.PROJ_ID = ICP_SUMMARY_JS_VW.PROJ_ID -- select the field to JOIN on
(by user1555520、David、Taryn)