作成したSQLの整形スクリプトによる,整形結果の例. 一応PostgreSQLを意識したSQLで確認していますが,Oracleでも使えるでしょう. 整形スクリプト(Perl)の内容は,SQLの整形ツールを参照.
入力SQLSELECT DISTINCT m.artist_id, m.sale_date, ( SELECT mp.url FROM merc_picture mp WHERE mp.seq_exhibit_id = m.seq_exhibit_id AND mp.merc_pic_type = 't' ORDER BY mp.url ASC LIMIT 1) AS url FROM shop_order_detail sod, shop_order so, merchandise m WHERE sod.seq_order_id = so.seq_order_id AND sod.seq_exhibit_id = m.seq_exhibit_id AND m.seq_exhibit_id IN ( SELECT m2.seq_exhibit_id FROM merchandise m2, merc_category mc WHERE m2.seq_exhibit_id = mc.seq_exhibit_id AND ( mc.categ_id = ? OR m2.reserve_merc_flg = 'N')) GROUP BY m.seq_exhibit_id, m.artist_id, m.sale_date, ORDER BY sum(sod.order_num) desc , m.sale_date DESC LIMIT ?
整形結果SELECT DISTINCT m.artist_id, m.sale_date, ( SELECT mp.url FROM merc_picture mp WHERE mp.seq_exhibit_id = m.seq_exhibit_id AND mp.merc_pic_type = 't' ORDER BY mp.url ASC LIMIT 1) AS url FROM shop_order_detail sod, shop_order so, merchandise m WHERE sod.seq_order_id = so.seq_order_id AND sod.seq_exhibit_id = m.seq_exhibit_id AND m.seq_exhibit_id IN ( SELECT m2.seq_exhibit_id FROM merchandise m2, merc_category mc WHERE m2.seq_exhibit_id = mc.seq_exhibit_id AND ( mc.categ_id = ? OR m2.reserve_merc_flg = 'N')) GROUP BY m.seq_exhibit_id, m.artist_id, m.sale_date, ORDER BY sum(sod.order_num) desc, m.sale_date DESC LIMIT ? |