INSERT
INTO w_display_order_send(
store_code
, item_code
, gondola_furniture_type
, display_gondola
, display_shelf
, display_row
, display_face
, display_order_input_datetime
, extraction_date
, update_datetime
, update_function_id
, update_person_id
, update_count
)
SELECT
tmp.store_code
, tmp.item_code
, CASE WHEN tmp.gondola_furniture_type IS NULL THEN NULL
ELSE LPAD(TRIM(tmp.gondola_furniture_type), 3, '0')
END AS gondola_furniture_type
, tmp.display_gondola
, tmp.display_shelf
, tmp.display_row
, tmp.display_face
, tmp.display_order_input_datetime
, #{systemDate} :: date
, CURRENT_TIMESTAMP
, #{taskId}
, #{taskId}
, 0
FROM
(
SELECT
te1.store_code
, tn.item_code
, gl1.gondola_furniture_type
, tn.display_gondola
, tn.display_shelf
, tn.display_row
, tn.display_face
, tn.display_order_input_datetime
, ROW_NUMBER() OVER (
PARTITION BY
te1.store_code
, tn.item_code
, tn.label_code
ORDER BY
tn.display_order_input_datetime DESC
) AS registrationOrder
FROM
m_store_number AS te1
INNER JOIN m_display AS tn
ON te1.original_store_code = tn.original_store_code
LEFT JOIN m_gondola_layout AS gl1
ON gl1.version = #{gondolaLayoutVersion}
AND tn.original_store_code = gl1.original_store_code
AND tn.display_gondola = gl1.gondola_number
WHERE
te1.version = #{storeVersion}
AND #{systemDate} :: date BETWEEN te1.apply_start_date AND te1.apply_end_date
) AS tmp
WHERE
tmp.registrationOrder <= 2
UNION ALL
SELECT
DISTINCT
te2.store_code
, sh1.item_code
, CASE WHEN gl2.gondola_furniture_type IS NULL THEN NULL
ELSE LPAD(TRIM(gl2.gondola_furniture_type), 3, '0')
END AS gondola_furniture_type
, tt1.display_gondola
, tt1.display_shelf
, NULL :: numeric AS display_row
, NULL :: numeric AS display_face
, tt1.special_display_input_datetime
, #{systemDate} :: date
, CURRENT_TIMESTAMP
, #{taskId}
, #{taskId}
, 0
FROM
m_store_number AS te2
INNER JOIN m_specific_display_order AS tt1
ON te2.original_store_code = tt1.original_store_code
LEFT JOIN m_gondola_layout AS gl2
ON gl2.version = #{gondolaLayoutVersion}
AND tt1.original_store_code = gl2.original_store_code
AND tt1.display_gondola = gl2.gondola_number
INNER JOIN m_pattern AS pt
ON pt.version = #{patternVersion}
AND tt1.original_store_code = pt.original_store_code
AND #{systemDate} :: date BETWEEN pt.apply_start_date AND pt.apply_end_date
INNER JOIN m_item AS sh1
ON sh1.version = #{itemVersion}
AND pt.pattern_type = sh1.pattern_type
AND pt.pattern_code = sh1.pattern_code
AND #{systemDate} :: date BETWEEN sh1.apply_start_date AND sh1.apply_end_date
AND tt1.information_category_code = sh1.information_category_code
LEFT JOIN m_license AS li1
ON li1.version = #{licenseVersion}
AND te2.original_store_code = li1.original_store_code
AND sh1.license_code = li1.license_code
LEFT JOIN m_item_by_specific_store_recommendation ts
ON ts.version = #{specificItemVersion}
AND tt1.original_store_code = ts.original_store_code
AND sh1.item_code = ts.item_code
AND #{systemDate} :: date BETWEEN ts.apply_start_date AND ts.apply_end_date
WHERE
te2.version = #{storeVersion}
AND #{systemDate} :: date BETWEEN te2.apply_start_date AND te2.apply_end_date
AND (
sh1.license_code = '00'
OR (
sh1.license_code != '00'
AND li1.licenseditem_adopt_flag != '2'
)
)
AND (
sh1.specific_item_type = ' '
OR (
sh1.specific_item_type IN ('1', '2', 'G')
AND ts.original_store_code IS NOT NULL
)
)
UNION ALL
SELECT
DISTINCT
te3.store_code
, '999999'
, CASE WHEN gl3.gondola_furniture_type IS NULL THEN NULL
ELSE LPAD(TRIM(gl3.gondola_furniture_type), 3, '0')
END AS gondola_furniture_type
, tt2.display_gondola
, tt2.display_shelf
, NULL :: numeric AS display_row
, NULL :: numeric AS display_face
, tt2.special_display_input_datetime
, #{systemDate} :: date
, CURRENT_TIMESTAMP
, #{taskId}
, #{taskId}
, 0
FROM
m_store_number AS te3
INNER JOIN m_specific_display_order_by_specific_category AS tt2
ON te3.original_store_code = tt2.original_store_code
LEFT JOIN m_gondola_layout AS gl3
ON gl3.version = #{gondolaLayoutVersion}
AND tt2.original_store_code = gl3.original_store_code
AND tt2.display_gondola = gl3.gondola_number
INNER JOIN m_recommendation_group_by_specific_category AS rg
ON rg.version = #{recommendationGroupVersion}
AND tt2.original_store_code = rg.original_store_code
AND #{systemDate} :: date BETWEEN rg.apply_start_date AND rg.apply_end_date
INNER JOIN m_item_by_specific_category AS sh2
ON sh2.version = #{itemSpecificCategory}
AND rg.recommendation_group_type = sh2.recommendation_group_type
AND rg.recommendation_group_code = sh2.recommendation_group_code
AND #{systemDate} :: date BETWEEN sh2.apply_start_date AND sh2.apply_end_date
AND tt2.information_category_code = sh2.information_category_code
LEFT JOIN m_license AS li2
ON li2.version = #{licenseVersion}
AND te3.original_store_code = li2.original_store_code
AND sh2.license_code = li2.license_code
WHERE
te3.version = #{storeVersion}
AND #{systemDate} :: date BETWEEN te3.apply_start_date AND te3.apply_end_date
AND (
sh2.license_code = '00'
OR (
sh2.license_code != '00'
AND li2.licenseditem_adopt_flag != '2'
)
)通过连接以下 TBL 检索了过多的记录，
并且在对大量记录执行 ROW_NUMBER() OVER 时，耗时过长。
m_store_number（门店编号主表）
m_display（显示主表）
m_gondola_layout（贡多拉布局）我想用临时表来处理
本文探讨了在使用face_code业务时遇到的信息不匹配问题，主要关注appid、mch_id、out_trade_no及sub_mch_id的一致性检查，确保支付流程的顺利进行。
扫一扫
444
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
