SQLの整形ツール～整形結果の例

— Posted inSoftware — 2 min read

作成したSQLの整形スクリプトによる，整形結果の例

一応PostgreSQLを意識したSQLで確認していますが，Oracleでも使えるでしょう．

整形スクリプト(Perl)の内容は，SQLの整形ツール を参照．

入力SQL

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 ?

整形結果

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 ?