検索条件を各項目の一時テーブルに入れて、ジョインするかしないかは、パラメータで指示してます。
[ SQL例 ]
<!-- 船スペックマスタリスト -->
<!-- 使用 -->
<select id="selVesselSpecList2" parameterType="ParamVesselSpec" resultType="VesselSpec">
select * from (
select
v.ukfld as id, v.ukfld as imo,
v.Name as vessel,
coalesce(fc_acc6m(v.Name), '') as acc6m,
coalesce(en.cnt_en, 0) as cnt_en,
coalesce(`in`.cnt_in, 0) as cnt_in,
coalesce(eo.cnt_eo, 0) as cnt_eo,
coalesce(io.cnt_io, 0) as cnt_io,
coalesce(ua.cnt_ua, 0) as cnt_ua,
v.TEU, v.`Year`, v.Speed, v.GrossT, v.DeadW, v.`call`, v.Cntry,
v.tm_add, v.tm_updt,
greatest(v.tm_add, v.tm_updt, coalesce(mt.TM_ADD, '2000-01-01 00:00:00')) as tm_lastupdt,
v.is_manadd,
coalesce(mt.LENGTH_OVERALL, 0) as `length`,
coalesce(mt.BREADTH_EXTREME, 0) as width,
coalesce(mt.DRAUGHT, 0) as draught,
coalesce(round(mt.LENGTH_OVERALL, 0), 0) as `length_int`,
coalesce(round(mt.BREADTH_EXTREME, 0), 0) as width_int,
coalesce(round(mt.DRAUGHT, 0), 0) as draught_int,
COALESCE(mt.LIQUID_OIL, 0) AS liquid_oil,
COALESCE(mt.FUEL_CONSUMPTION, '') AS fuel_consumption,
COALESCE(mt.OWNER, '') AS owner,
COALESCE(mt.MANAGER, '') AS manager,
COALESCE(mt.MANAGER_OWNER, '') AS manager_owner
from
vessel2 v
left outer join vessel_mt mt on (v.ukfld = mt.IMO)
<if test="!isTEUselAll">
inner join tmp_imo_teu tt on (
v.ukfld = tt.IMO and tt.ID_SESSION = #{idSession} and tt.NM_GRID = 'specs'
)
</if>
<if test="!isYearselAll">
inner join tmp_imo_year yt on (
v.ukfld = yt.IMO and yt.ID_SESSION = #{idSession} and yt.NM_GRID = 'specs'
)
</if>
<if test="!isSpeedselAll">
inner join tmp_imo_speed st on (
v.ukfld = st.IMO and st.ID_SESSION = #{idSession} and st.NM_GRID = 'specs'
)
</if>
<if test="!isLengthselAll">
inner join tmp_imo_length lg on (
mt.IMO = lg.IMO and lg.ID_SESSION = #{idSession} and lg.NM_GRID = 'specs'
)
</if>
<if test="!isWidthselAll">
inner join tmp_imo_width wd on (
mt.IMO = wd.IMO and wd.ID_SESSION = #{idSession} and wd.NM_GRID = 'specs'
)
</if>
<if test="!isDraughtselAll">
inner join tmp_imo_draught dr on (
mt.IMO = dr.IMO and dr.ID_SESSION = #{idSession} and dr.NM_GRID = 'specs'
)
</if>
left outer join (
select imo, count(*) as cnt_en from sch_export_source group by imo
) en
on (v.ukfld = en.imo)
left outer join (
select imo, count(*) as cnt_in from sch_import_source group by imo
) `in`
on (v.ukfld = `in`.imo)
left outer join (
select imo, count(*) as cnt_eo from sch_export_source_old group by imo
) eo
on (v.ukfld = eo.imo)
left outer join (
select imo, count(*) as cnt_io from sch_import_source_old group by imo
) io
on (v.ukfld = io.imo)
left outer join (
select u.vessel, count(u.id) as cnt_ua from sch_useradd u group by vessel
) ua on (v.Name = ua.vessel)
where
1 = 1 and v.Name like concat('%', #{vsllk}, '%') and
ukfld like concat('%', #{imolk}, '%')
<if test="isModOnly">
and is_manadd = 1
</if>
) a
order by
${sidx} ${sord}
limit ${skip}, ${pgrec}
</select>
[ 使っているテーブル ]
船マスタ
CREATE TABLE `vessel2` (
`Name` VARCHAR(36) NOT NULL DEFAULT '' COMMENT '船名',
`call` VARCHAR(9) NOT NULL DEFAULT '' COMMENT '無線局呼出符号',
`Year` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '建造年',
`Speed` FLOAT NOT NULL DEFAULT '0' COMMENT '速力',
`TEU` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '積載TEU',
`GrossT` INT(11) NOT NULL DEFAULT '0' COMMENT 'グロス重量',
`DeadW` INT(11) NOT NULL DEFAULT '0' COMMENT '重量重量',
`Cntry` VARCHAR(2) NOT NULL DEFAULT '' COMMENT '船籍国コード',
`ukfld` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
`tm_add` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
`tm_updt` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '最終更新時刻',
`is_manadd` TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'ユーザー更新フラグ',
`ChgName` VARCHAR(36) NOT NULL DEFAULT '0' COMMENT '参照結合用変換船名',
PRIMARY KEY (`ukfld`),
INDEX `vessel2_Name` (`Name`),
INDEX `vessel2_ChgName` (`ChgName`)
)
COMMENT='船マスタ (利用)'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
一時テーブル (一部)
・ID_SESSION はTomcatのセッションIDでユーザー別
・ほっておくと肥大するので、夜中に全レコード削除
CREATE TABLE `tmp_imo_teu` (
`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
PRIMARY KEY (`ID`),
INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5627
;
CREATE TABLE `tmp_imo_year` (
`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
PRIMARY KEY (`ID`),
INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
[ 一時テーブル削除追加用SQL ]
・テーブル名を ${tmptbl} のパラメータで渡すことで、まとめてます。
<!-- マルチセレクト用一時テーブル更新 -->
<!-- 削除 -->
<delete id="delIMOTmpAll" parameterType="MyBatisParent">
delete from ${tmptbl} where ID_SESSION = #{idSession} and NM_GRID = #{nmGrid}
</delete>
<!-- 追加 -->
<insert id="insAIMOTmp" parameterType="MyBatisParent">
insert into ${tmptbl}
(ID_SESSION, NM_GRID, IMO)
select #{idSession}, #{nmGrid},
ukfld
from ${vslmsttbl}
where ${vslColumn} between #{insMinVal} and #{insMaxVal}
</insert>
<insert id="insAIMOTmpmt" parameterType="MyBatisParent">
insert into ${tmptbl}
(ID_SESSION, NM_GRID, IMO)
select #{idSession}, #{nmGrid}, IMO
from ${vslmsttbl}
where ${vslColumn} between #{insMinVal} and #{insMaxVal}
</insert>
![[商品価格に関しましては、リンクが作成された時点と現時点で情報が変更されている場合がございます。]](https://hbb.afl.rakuten.co.jp/hgb/2d6b6799.7e4f498b.2d6b679a.47787c76/?me_id=1213310&item_id=16706340&pc=https%3A%2F%2Fthumbnail.image.rakuten.co.jp%2F%400_mall%2Fbook%2Fcabinet%2F2212%2F9784797372212.jpg%3F_ex%3D240x240&s=240x240&t=picttext "[商品価格に関しましては、リンクが作成された時点と現時点で情報が変更されている場合がございます。]")
コメントを投稿するにはログインしてください。