都道府県添加的sql

-- 郵便番号データテーブルの削除drop table if exists ups_pdb.tbl_post_no_data;-- 郵便番号データテーブルの作成create table if not exists ups_pdb.tbl_post_no_data(syz_cd varchar(5) not null,post_no_old varchar(5) not null,post_no varchar(7) not null,prefecture_nm_kana varchar(100),syz_nm_kana varchar(100),syz2_nm_kana varchar(100),prefecture_nm varchar(100),syz_nm varchar(100),syz2_nm varchar(100),prefecture_cd varchar(3),update_datetime timestamp (3),update_id varchar(50) not null);COMMENT ON TABLE ups_pdb.tbl_post_no_data IS '郵便番号データテーブル';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.syz_cd IS '市区群コード';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.post_no_old IS '（旧）郵便番号（5桁）';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.post_no IS '郵便番号';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.prefecture_nm_kana IS '都道府県名（カナ）';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.syz_nm_kana IS '市区群町村名（カナ）';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.syz2_nm_kana IS '町域名（カナ）';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.prefecture_nm IS '都道府県名';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.syz_nm IS '市区群町村名';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.syz2_nm IS '町域名';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.prefecture_cd IS '都道府県コード';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.update_datetime IS 'データ更新日';COMMENT ON COLUMN ups_pdb.tbl_post_no_data.update_id IS 'データ更新管理ID';create index on ups_pdb.tbl_post_no_data using btree(post_no, syz_cd, prefecture_nm, syz_nm);create index on ups_pdb.tbl_post_no_data using btree(syz_cd, syz_nm);create index on ups_pdb.tbl_post_no_data using btree(prefecture_cd, prefecture_nm);create index on ups_pdb.tbl_post_no_data using btree(prefecture_cd, syz_cd, syz_nm);create TEMPORARY table if not exists ups_pdb.tmp_post_no_data(syz_cd varchar(5) not null,post_no_old varchar(5) not null,post_no varchar(7) not null,prefecture_nm_kana varchar(100),syz_nm_kana varchar(100),syz2_nm_kana varchar(100),prefecture_nm varchar(100),syz_nm varchar(100),syz2_nm varchar(100));INSERT INTO ups_pdb.tbl_post_no_dataSELECT t.syz_cd,t.post_no_old,t.post_no,t.prefecture_nm_kana,t.syz_nm_kana,t.syz2_nm_kana,CASE  WHEN t.syz_cd LIKE '131%' THEN '東京都(23区内)'  WHEN t.syz_cd LIKE '13%' THEN '東京都(23区外)' ELSE t.prefecture_nm END AS prefecture_nm,t.syz_nm,t.syz2_nm,CASE  WHEN t.syz_cd LIKE '13%' THEN LEFT(syz_cd,3) ELSE LEFT(syz_cd,2) END AS prefecture_cd,now() AS update_date_time,'irasawa' AS update_idFROM ups_pdb.tmp_post_no_data tORDER BY syz_cd, post_no,syz2_nm_kana;select t.cnt,p.*from ups_pdb.tmp_post_no_data p,(select post_no,count(*) AS cntfrom ups_pdb.tmp_post_no_data tgroup by post_no) twhere cnt > 1and p.post_no=t.post_noorder by cnt desc, p.syz_cd, p.post_no     SELECT DISTINCT prefecture_cd,prefecture_nmFROM ups_pdb.tbl_post_no_dataORDER BY prefecture_cd;SELECT DISTINCT syz_cd,syz_nmFROM ups_pdb.tbl_post_no_dataWHERE CASE  WHEN '13x'=/* prefectureCd */'13x' THEN (prefecture_cd LIKE '13%' AND prefecture_cd <>'131')  ELSE prefecture_cd=/* prefectureCd */'13x' ENDORDER BY syz_cd;SELECT DISTINCT prefecture_cd,prefecture_nm,syz_cd,syz_nmFROM ups_pdb.tbl_post_no_dataWHERE post_no=/* postNo */'4520961'ORDER BY syz_cd;