性能改善后复杂SQL
时间：2022-03-10 17:46
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"">
<mapper namespace="jp.co.asahi_np.biz.bbs.pcs.dao.mik.ShukoMikomiDao">
<!-- グループ別掲載社別(ＮＥＴ売上) 202 -->
<select id="selectMikomiFee" parameterType="jp.co.asahi_np.biz.bbs.pcs.dto.mik.ShukoMikomiSearchDto"
resultType="jp.co.asahi_np.biz.bbs.pcs.dto.mik.ShukoMikomiDto">
<![CDATA[
SELECT shukeiGroupCode,
shukeiGroupName,
CASE
WHEN (mikomiFeeT IS NOT NULL OR mikomiFeeO IS NOT NULL OR
mikomiFeeS IS NOT NULL OR mikomiFeeN IS NOT NULL OR
mikomiFeeH IS NOT NULL) THEN
round((nvl(mikomiFeeT,0)+nvl(mikomiFeeO,0)+nvl(mikomiFeeS,0)+nvl(mikomiFeeN,0)+nvl(mikomiFeeH,0))/1000,0)
ELSE
round((mikomiFeeT+mikomiFeeO+mikomiFeeS+mikomiFeeN+mikomiFeeH)/1000,0)
END AS mikomiFeeSum,
round(mikomiFeeT/1000,0) as mikomiFeeT,
round(mikomiFeeO/1000,0) as mikomiFeeO,
round(mikomiFeeS/1000,0) as mikomiFeeS,
round(mikomiFeeN/1000,0) as mikomiFeeN,
round(mikomiFeeH/1000,0) as mikomiFeeH,
numT,
numO,
numS,
numN,
numH,
dspJyun
FROM (
SELECT shukeiGroupCode AS shukeiGroupCode,
shukeiGroupName AS shukeiGroupName,
sum(T) AS mikomiFeeT,
sum(O) AS mikomiFeeO,
sum(S) AS mikomiFeeS,
sum(N) AS mikomiFeeN,
sum(H) AS mikomiFeeH,
atom_concat(numT) AS numT,
atom_concat(numO) AS numO,
atom_concat(numS) AS numS,
atom_concat(numN) AS numN,
atom_concat(numH) AS numH,
dspJyun
FROM (SELECT tsg.shukei_group_code AS shukeiGroupCode,
tsg.shukei_group_name AS shukeiGroupName,
tsg.dsp_jyun as dspJyun,
CASE
WHEN tsm.ksai_sha_code = ‘1‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numT,
CASE
WHEN tsm.ksai_sha_code = ‘2‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numO,
CASE
WHEN tsm.ksai_sha_code = ‘3‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numS,
CASE
WHEN tsm.ksai_sha_code = ‘4‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numN,
CASE
WHEN tsm.ksai_sha_code = ‘5‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numH,
CASE
WHEN tsm.ksai_sha_code = ‘1‘ THEN
tsm.mikomi_fee
END AS T,
CASE
WHEN tsm.ksai_sha_code = ‘2‘ THEN
tsm.mikomi_fee
END AS O,
CASE
WHEN tsm.ksai_sha_code = ‘3‘ THEN
tsm.mikomi_fee
END AS S,
CASE
WHEN tsm.ksai_sha_code = ‘4‘ THEN
tsm.mikomi_fee
END AS N,
CASE
WHEN tsm.ksai_sha_code = ‘5‘ THEN
tsm.mikomi_fee
END AS H
FROM tb_shuko_mikomi tsm, --出稿見込情報
tb_shukei_group tsg, --集計グループ
tb_shukei_adv_relation tsar --集計グループ広告媒体関連
WHERE tsm.del_flg(+) = ‘0‘
AND tsg.del_flg = ‘0‘
AND tsar.del_flg = ‘0‘
AND tsm.uriage_nengetsu(+) = replace(‘2014/06‘,‘/‘,‘‘) --売上年月
AND tsm.uriage_sha_code(+) = ‘1‘ --売上社コード
AND tsm.tanto_bu_ka_code(+) = ‘110‘ --担当部課コード
AND tsm.gyosyu_mcode(+) = ‘10‘ --業種中分類コード
AND tsm.shukei_group_code(+) = tsg.shukei_group_code
AND tsg.shukei_group_kubun IN (‘1‘, ‘2‘)
AND tsg.shukei_group_code = tsar.shukei_group_code
AND tsar.input_flg = ‘1‘ --入力可否フラグ
GROUP BY tsg.shukei_group_code,
tsg.shukei_group_name,
tsm.ksai_sha_code,
tsm.mikomi_fee,
tsm.shuko_mikomi_mgr_num_temp,
tsg.dsp_jyun
ORDER BY tsg.dsp_jyun)
GROUP BY shukeiGroupCode,
shukeiGroupName,
dspJyun)
]]>
</select>
<!-- グループ別掲載社別(段数) 203 -->
<select id="selectMikomiDansu" parameterType="jp.co.asahi_np.biz.bbs.pcs.dto.mik.ShukoMikomiSearchDto"
resultType="jp.co.asahi_np.biz.bbs.pcs.dto.mik.ShukoMikomiDto">
<![CDATA[
SELECT shukeiGroupCode,
shukeiGroupName,
CASE
WHEN (mikomiDansuT IS NOT NULL OR mikomiDansuO IS NOT NULL OR
mikomiDansuS IS NOT NULL OR mikomiDansuN IS NOT NULL OR
mikomiDansuH IS NOT NULL) THEN
nvl(mikomiDansuT, 0) + nvl(mikomiDansuO, 0) +
nvl(mikomiDansuS, 0) + nvl(mikomiDansuN, 0) +
nvl(mikomiDansuH, 0)
ELSE
mikomiDansuT + mikomiDansuO + mikomiDansuS + mikomiDansuN +
mikomiDansuH
END AS mikomiFeeSum,
mikomiDansuT,
mikomiDansuO,
mikomiDansuS,
mikomiDansuN,
mikomiDansuH,
numT,
numO,
numS,
numN,
numH,
dspJyun
FROM (
SELECT shukeiGroupCode AS shukeiGroupCode,
shukeiGroupName AS shukeiGroupName,
sum(T) AS mikomiDansuT,
sum(O) AS mikomiDansuO,
sum(S) AS mikomiDansuS,
sum(N) AS mikomiDansuN,
sum(H) AS mikomiDansuH,
atom_concat(numT) AS numT,
atom_concat(numO) AS numO,
atom_concat(numS) AS numS,
atom_concat(numN) AS numN,
atom_concat(numH) AS numH,
dspJyun
FROM (SELECT tsg.shukei_group_code AS shukeiGroupCode,
tsg.shukei_group_name AS shukeiGroupName,
tsg.dsp_jyun as dspJyun,
CASE
WHEN tsm.ksai_sha_code = ‘1‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numT,
CASE
WHEN tsm.ksai_sha_code = ‘2‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numO,
CASE
WHEN tsm.ksai_sha_code = ‘3‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numS,
CASE
WHEN tsm.ksai_sha_code = ‘4‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numN,
CASE
WHEN tsm.ksai_sha_code = ‘5‘ THEN
tsm.shuko_mikomi_mgr_num_temp
END AS numH,
CASE
WHEN tsm.ksai_sha_code = ‘1‘ THEN
--tsm.mikomi_dansu
ROUND(tsm.mikomi_dansu/100, 2)
END AS T,
CASE
WHEN tsm.ksai_sha_code = ‘2‘ THEN
--tsm.mikomi_dansu
ROUND(tsm.mikomi_dansu/100, 2)
END AS O,
CASE
WHEN tsm.ksai_sha_code = ‘3‘ THEN
--tsm.mikomi_dansu
ROUND(tsm.mikomi_dansu/100, 2)
END AS S,
CASE
WHEN tsm.ksai_sha_code = ‘4‘ THEN
--tsm.mikomi_dansu
ROUND(tsm.mikomi_dansu/100, 2)
END AS N,
CASE
WHEN tsm.ksai_sha_code = ‘5‘ THEN
-- tsm.mikomi_dansu
ROUND(tsm.mikomi_dansu/100, 2)
END AS H
FROM tb_shuko_mikomi tsm, --出稿見込情報
tb_shukei_group tsg, --集計グループ
tb_shukei_adv_relation tsar --集計グループ広告媒体関連
WHERE tsm.del_flg(+) = ‘0‘
AND tsg.del_flg = ‘0‘
AND tsar.del_flg = ‘0‘
AND tsm.uriage_nengetsu(+) = replace(‘2014/06‘,‘/‘,‘‘) --売上年月
AND tsm.uriage_sha_code(+) = ‘1‘ --売上社コード
AND tsm.tanto_bu_ka_code(+) = ‘110‘ --担当部課コード
AND tsm.gyosyu_mcode(+) = ‘10‘ --業種中分類コード
AND tsm.shukei_group_code(+) = tsg.shukei_group_code
AND tsg.shukei_group_kubun IN (‘1‘, ‘2‘)
AND tsg.shukei_group_code = tsar.shukei_group_code
AND tsar.dansu_input_flg = ‘1‘ --段数入力可否フラグ
GROUP BY tsg.shukei_group_code,
tsg.shukei_group_name,
tsm.ksai_sha_code,
tsm.mikomi_dansu,
tsm.shuko_mikomi_mgr_num_temp,
tsg.dsp_jyun
ORDER BY tsg.dsp_jyun)
GROUP BY shukeiGroupCode,
shukeiGroupName,
dspJyun)
]]>
</select>
<!-- 405 -->
<!-- 当月売上計 (ＮＥＴ売上)-->
<select id="selectFee405" parameterType ="jp.co.asahi_np.biz.bbs.pcs.dto.mik.ShukoMikomiSearchDto"
resultType="jp.co.asahi_np.biz.bbs.pcs.dto.mik.ShukoMikomiDto">
<![CDATA[
with innerView as (
SELECT/*+materialize*/kumi_item_mgmt_num,
shukei_group_code
FROM (
SELECT tmkii.kumi_item_mgmt_num, --申込組込時項目情報．申込組込時項目管理番号
tsar.shukei_group_code, --集計グループ広告媒体関連．集計グループコード
ROW_NUMBER() OVER(PARTITION BY tmkii.receipt_num, tmkii.ksai_sha_code ORDER BY tmkii.dsp_jyun ASC, tmkii.hansu_code DESC) r
FROM tb_moko_kumi_item_info tmkii,
tb_shukei_adv_relation tsar
WHERE 1=1
]]>
AND to_char(tmkii.ksai_date, ‘YYYY/MM‘) IN
<if test="nengetsuList != null and nengetsuList.size() > 0">
<foreach collection="nengetsuList" item="item"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<![CDATA[
AND EXISTS
(SELECT 1
FROM
tb_moko_info_ksai_sha_betsu tmiksb,
tb_moko_info_matching tmim,
tb_moko_info_basic tmib
WHERE
tmib.receipt_num = tmiksb.receipt_num
AND tmib.his = tmiksb.his
AND ((tmib.yoyaku_waku_kubun_code IS NOT NULL AND
tmkii.receipt_num = tmib.yoyaku_waku_receipt_num) OR
(tmib.yoyaku_waku_kubun_code IS NULL AND
tmkii.receipt_num = tmib.receipt_num) OR
(tmkii.receipt_num = tmim.genko_umeawase_id AND
tmib.receipt_num = tmim.receipt_num AND
tmim.his= tmib.his ))
AND tmiksb.ksai_sha_code = tmkii.ksai_sha_code
AND tmiksb.jisseki_sign = tmkii.jisseki_sign
AND tmiksb.del_flg = ‘0‘
AND tmib.moko_kubun_code <> ‘1‘
AND tmib.moko_kind NOT IN (‘12‘,‘19‘, ‘30‘)
AND tmib.yuko_sign = ‘2‘
AND tmib.moko_state_code = ‘4‘
AND tmib.karihan_sign = ‘0‘
AND tmib.uriage_sha_code = ‘1‘ --遷移元画面．売上社コード
AND tmib.uriage_bu_ka_code = ‘110‘ --遷移元画面．担当部課コード
AND tmib.gyosyu_mcode = ‘10‘ --遷移元画面．業種中分類コード
AND tmib.del_flg = ‘0‘
AND tmib.yuko_sign = ‘2‘)
AND (tmkii.han_keiretsu_code NOT IN
]]>
<foreach collection="hanKeiretsuCodeList" item="item" open="(" separator="," close=")">
#{item.codeValue}
</foreach>
<![CDATA[
OR tmkii.han_keiretsu_code IS NULL)
AND tmkii.adv_baitai_bunrui_code = tsar.adv_baitai_bunrui_code
AND (CASE
WHEN EXISTS (SELECT 1
FROM tb_shukei_adv_relation tsar
WHERE tmkii.adv_kind_lcode = tsar.adv_kind_lcode
AND tmkii.adv_baitai_bunrui_code =
tsar.adv_baitai_bunrui_code
AND tsar.count_flg = ‘1‘
AND tsar.input_flg = ‘1‘) THEN
tmkii.adv_kind_lcode
ELSE
‘*‘
END) = tsar.adv_kind_lcode
AND tsar.count_flg = ‘1‘
AND tsar.input_flg = ‘1‘
AND tmkii.del_flg = ‘0‘)
WHERE r = 1),
innerView2 as (
SELECT/*+materialize*/kumi_item_mgmt_num,
shukei_group_code
FROM (
SELECT tmkii.kumi_item_mgmt_num, --申込組込時項目情報．申込組込時項目管理番号
tsar.shukei_group_code, --集計グループ広告媒体関連．集計グループコード
ROW_NUMBER() OVER(PARTITION BY tmkii.receipt_num, tmkii.ksai_sha_code ORDER BY tmkii.ksai_date ASC) r
FROM tb_moko_kumi_item_info tmkii,
tb_shukei_adv_relation tsar
WHERE 1=1
]]>
AND to_char(tmkii.ksai_date, ‘YYYY/MM‘) IN
<if test="nengetsuList != null and nengetsuList.size() > 0">
<foreach collection="nengetsuList" item="item"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<![CDATA[
AND EXISTS
(SELECT 1
FROM
tb_moko_info_ksai_sha_betsu tmiksb,
tb_moko_info_matching tmim,
tb_moko_info_basic tmib
WHERE
tmib.receipt_num = tmiksb.receipt_num
AND tmib.his = tmiksb.his
AND ((tmib.yoyaku_waku_kubun_code IS NOT NULL AND
tmkii.receipt_num = tmib.yoyaku_waku_receipt_num) OR
(tmib.yoyaku_waku_kubun_code IS NULL AND
tmkii.receipt_num = tmib.receipt_num) OR
(tmkii.receipt_num = tmim.genko_umeawase_id AND
tmim.receipt_num = tmib.receipt_num AND
tmim.his= tmib.his))
AND tmiksb.ksai_sha_code = tmkii.ksai_sha_code
AND tmiksb.jisseki_sign = tmkii.jisseki_sign
AND tmiksb.del_flg = ‘0‘
AND tmib.moko_kubun_code <> ‘1‘
AND tmib.yuko_sign = ‘2‘
AND tmib.moko_state_code = ‘4‘
AND tmib.moko_kind = ‘30‘
AND tmib.karihan_sign = ‘0‘
AND tmib.uriage_sha_code = ‘1‘ --遷移元画面．売上社コード
AND tmib.uriage_bu_ka_code = ‘110‘ --遷移元画面．担当部課コード
AND tmib.gyosyu_mcode = ‘10‘ --遷移元画面．業種中分類コード
AND tmib.del_flg = ‘0‘
AND tmib.yuko_sign = ‘2‘
)
AND (tmkii.han_keiretsu_code NOT IN
]]>
<foreach collection="hanKeiretsuCodeList" item="item" open="(" separator="," close=")">
#{item.codeValue}
</foreach>
<
热门排行
今日推荐
热门手游
-
我的水晶花园官方版
版本：v2.7.7
大小：88.83MB
日期：2024-09-25
-
平民小富商正版
版本：v1.0.1
大小：53.92MB
日期：2024-09-25
-
闲置农场生活安卓版
版本：v1.0.0
大小：39.36MB
日期：2024-09-25
-
凯蒂猫超市小镇手机版
版本：v1.0
大小：264.07MB
日期：2024-09-25
-
小偷模拟器2正版
版本：v1.0.0
大小：94.96MB
日期：2024-09-25
-
2024巴黎奥运会手机版
版本：v1.5.0
大小：1MB
日期：2024-09-25