CREATE OR REPLACE VIEW VR_MC_MIN_DATE AS
SELECT
S.COP_NAME AS COP_NAME,
S.SERIES_NUMBER AS SERIES_NUMBER,
S.SERIES_STATE AS SERIES_STATE,
S.VENDOR_NAME AS VENDOR_NAME,
S.WAREHOUSE_NAME AS WAREHOUSE_NAME,
S.STORE_STATE AS STORE_STATE,
S.STOCKIN_ORDERID AS STOCKIN_ORDERID,
S.STOCKIN_DATE AS STOCKIN_DATE,
S.CONTROL_LOCK AS CONTROL_LOCK,
DECODE(IS_SHOW, 1, '是', '否') AS ppt_machine,
(CASE WHEN SERVICE_GODDS_STATE IS NULL THEN '否' ELSE '是'END) AS sale_after_machine,
G.GOODS_ID AS GOODS_ID,
G.GOODS_NAME AS GOODS_NAME,
G.GOODS_CLASS_NAME AS GOODS_CLASS_NAME,
G.CONFIG_INFO AS CONFIG_INFO,
G.GOODS_STANDARD AS GOODS_STANDARD,
G.MODEL_NAME AS MODEL_NAME,
G.BRAND_NAME AS BRAND_NAME,
G.GOODS_TYPE AS GOODS_TYPE,
A.AREA_NAME AS AREA_NAME,
T.MIN_SHOP_DATE AS IN_SHOP_DATE,
D.CAPTION AS CAPTION
FROM MC_STORE.MC_SERIES S
LEFT JOIN GOODS_MASTER G ON S.GOODS_ID = G.GOODS_ID
LEFT JOIN HR_COMPANY C ON S.COP_ID = C.COP_ID
LEFT JOIN HR_AREA A ON C.AREA_ID=A.AREA_ID
INNER JOIN (SELECT SHOP_ID,SERIES_NUMBER, MIN(TRANS_DATE) AS MIN_SHOP_DATE FROM MC_TRANS WHERE INOUT = 1 GROUP BY SHOP_ID,SERIES_NUMBER) T
ON T.SHOP_ID = S.SHOP_ID AND T.SERIES_NUMBER=S.SERIES_NUMBER
LEFT JOIN (SELECT KEY, CAPTION
FROM MC_DICTIONARY
WHERE PARENT_DICT_ID IS NOT NULL
START WITH PARENT_DICT_ID IS NULL AND DICT_ID = 'ZD0190'
CONNECT BY PRIOR DICT_ID = PARENT_DICT_ID
) D ON S.STORE_STATE = D.KEY
WITH READ ONLY;
select * from VR_MC_MIN_DATE where SERIES_NUMBER=:1
这样的 条件 无法推进到 视图中的子查询.
该视图中的子查询是获取该号的入店的时间.
MC_TRANS 是某种交易流水表, 很显然该表会越来越大.