SELECT *
FROM (
SELECT tmp_page.*, rownum AS row_id
FROM (
SELECT a.group_id, a.hos_id, a.copy_code, a.inv_id, a.inv_no
, a.inv_code, a.inv_name, a.mat_type_id, b.mat_type_name, a.inv_model
, a.is_com, a.unit_code, c.unit_name, aa.sup_id, d.sup_no
, d.sup_name, a.fac_id, e.fac_no, e.fac_name, a.plan_price
, a.sell_price, f.pack_code, g.pack_name, f.map_amount AS num_exchange, a.alias
, aa.is_default
FROM mat_inv_dict a
LEFT JOIN mat_type_dict b ON a.group_id = b.group_id
AND a.hos_id = b.hos_id
AND a.copy_code = b.copy_code
AND a.mat_type_id = b.mat_type_id
AND a.mat_type_no = b.mat_type_no
AND b.is_stop = 0
LEFT JOIN hos_unit c ON a.group_id = c.group_id
AND a.hos_id = c.hos_id
AND a.unit_code = c.unit_code
LEFT JOIN (
SELECT sup_id, inv_id, group_id, hos_id, copy_code
, is_default
FROM mat_inv_sup
WHERE group_id = '101'
AND hos_id = '105'
AND copy_code = 'wuliu'
AND is_default = 1
) aa ON a.group_id = aa.group_id
AND a.hos_id = aa.hos_id
AND a.copy_code = aa.copy_code
AND a.inv_id = aa.inv_id
LEFT JOIN hos_sup_dict d ON aa.group_id = d.group_id
AND aa.hos_id = d.hos_id
AND aa.sup_id = d.sup_id
AND d.is_stop = 0
LEFT JOIN hos_fac_dict e ON a.group_id = e.group_id
AND a.hos_id = e.hos_id
AND a.fac_id = e.fac_id
AND e.is_stop = 0
LEFT JOIN mat_inv_unit_map f ON f.inv_id = a.inv_id
AND f.hos_id = a.hos_id
AND f.group_id = a.group_id
AND f.copy_code = a.copy_code
LEFT JOIN hos_package g ON f.group_id = g.group_id
AND f.hos_id = g.hos_id
AND f.pack_code = g.pack_code
WHERE a.group_id = '101'
AND a.hos_id = '105'
AND a.copy_code = 'wuliu'
AND a.is_stop = 0
AND a.use_state = 1
ORDER BY a.inv_code
) tmp_page
WHERE rownum <= 20
)
WHERE row_id > 0
uj5u.com熱心網友回復：
忒長啊，看不過來，誰也不知道你業務上的含義uj5u.com熱心網友回復：
建議從最里層開始執行，太長。。。。。uj5u.com熱心網友回復：
SELECT *
FROM (SELECT TMP_PAGE.*, ROWNUM AS ROW_ID --ROWNUM ORACLE默認列
FROM (
SELECT A.GROUP_ID,
A.HOS_ID,
A.COPY_CODE,
A.INV_ID,
A.INV_NO,
A.INV_CODE,
A.INV_NAME,
A.MAT_TYPE_ID,
B.MAT_TYPE_NAME,
A.INV_MODEL,
A.IS_COM,
A.UNIT_CODE,
C.UNIT_NAME,
AA.SUP_ID,
D.SUP_NO,
D.SUP_NAME,
A.FAC_ID,
E.FAC_NO,
E.FAC_NAME,
A.PLAN_PRICE,
A.SELL_PRICE,
F.PACK_CODE,
G.PACK_NAME,
F.MAP_AMOUNT AS NUM_EXCHANGE,
A.ALIAS,
AA.IS_DEFAULT
FROM MAT_INV_DICT A
LEFT JOIN MAT_TYPE_DICT B
ON A.GROUP_ID = B.GROUP_ID --根據關聯條件 A 左聯接 B
AND A.HOS_ID = B.HOS_ID
AND A.COPY_CODE = B.COPY_CODE
AND A.MAT_TYPE_ID = B.MAT_TYPE_ID
AND A.MAT_TYPE_NO = B.MAT_TYPE_NO
AND B.IS_STOP = 0
LEFT JOIN HOS_UNIT C --根據關聯條件 A 左聯接 C
ON A.GROUP_ID = C.GROUP_ID
AND A.HOS_ID = C.HOS_ID
AND A.UNIT_CODE = C.UNIT_CODE
LEFT JOIN (SELECT SUP_ID,
INV_ID,
GROUP_ID,
HOS_ID,
COPY_CODE,
IS_DEFAULT
FROM MAT_INV_SUP
WHERE GROUP_ID = '101'
AND HOS_ID = '105'
AND COPY_CODE = 'wuliu'
AND IS_DEFAULT = 1) AA
ON A.GROUP_ID = AA.GROUP_ID --根據關聯條件 A 左聯接 AA(子查詢)
AND A.HOS_ID = AA.HOS_ID
AND A.COPY_CODE = AA.COPY_CODE
AND A.INV_ID = AA.INV_ID
LEFT JOIN HOS_SUP_DICT D
ON AA.GROUP_ID = D.GROUP_ID --根據關聯條件 AA 左聯接 D
AND AA.HOS_ID = D.HOS_ID
AND AA.SUP_ID = D.SUP_ID
AND D.IS_STOP = 0
LEFT JOIN HOS_FAC_DICT E
ON A.GROUP_ID = E.GROUP_ID --根據關聯條件 A 左聯接 E
AND A.HOS_ID = E.HOS_ID
AND A.FAC_ID = E.FAC_ID
AND E.IS_STOP = 0
LEFT JOIN MAT_INV_UNIT_MAP F
ON F.INV_ID = A.INV_ID --根據關聯條件 A 左聯接 F
AND F.HOS_ID = A.HOS_ID
AND F.GROUP_ID = A.GROUP_ID
AND F.COPY_CODE = A.COPY_CODE
LEFT JOIN HOS_PACKAGE G
ON F.GROUP_ID = G.GROUP_ID --根據關聯條件 F 左聯接 G
AND F.HOS_ID = G.HOS_ID
AND F.PACK_CODE = G.PACK_CODE
WHERE A.GROUP_ID = '101' --A表 篩選條件
AND A.HOS_ID = '105'
AND A.COPY_CODE = 'wuliu'
AND A.IS_STOP = 0
AND A.USE_STATE = 1
ORDER BY A.INV_CODE) TMP_PAGE
WHERE ROWNUM <= 20)
WHERE ROW_ID > 0
/*依次以A表為“主表”做左關聯
A LEFT JOIN B
A LEFT JOIN C
A LEFT JOIN (AA LEFT JOIN D)
A LEFT JOIN E
A LEFT JOIN (F LEFT JOIN G)
把這些個關聯合為一個整體
取前20條資料*/
uj5u.com熱心網友回復：
從里面查詢結果做外查詢的表，依次向外。很基礎的查詢。這么復雜就建立視圖，理解起來方便點。uj5u.com熱心網友回復：
比較長。。看起來比較麻煩，其實就是從最里層開始看就好了轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/97807.html
標籤：基礎和管理