出力したいものの仕様はこうします♪
- 「商品名」「規格1」「規格2」,「商品名」「規格1」「規格2」…、という形で 1 回で買った受注の商品をひとつのセルに出力させたい。
- 北海道,東京都,沖縄県…、という形で 1 回で買った受注のお届け先都道府県をひとつのセルに出力させたい。
- 商品は小計（dtb_order.subtotal）の前に、お届け先都道府県は配送情報 ID の次に出力させる。
SQL ポイント
- || 演算子で前後のカラムを文字列結合する。
文字列関数と演算子 - NULL を含む文字列を結合すると全体が NULL となる。これを防止するために COALESCE 関数を使用する。COALESCE(column, ”) とすれば column が NULL ならば空文字 ” で、空白で、置き換えられる。
条件式
SELECT 句のサブクエリを分解♪
上記ポイントなどを使用して、商品、お届け先都道府県を CSV に書き出しますが、その部分はサブクエリです。入れ子状態の複雑なものになってしまいましたので、分解して把握しやすくいたします。
なお、サブクエリについては次のページが参考になりました。ありがとう存じます♪
SELECT句でサブクエリを利用する場合、メインクエリの結果としてサブクエリの結果を取得できます。
まずは商品からです。
(
SELECT
array_to_string(
array(
SELECT
dtb_order_detail.product_name
|| COALESCE(
dtb_order_detail.classcategory_name1,
''
)
|| COALESCE(
dtb_order_detail.classcategory_name2,
''
)
FROM
dtb_order_detail
WHERE
dtb_order_detail.order_id = dtb_order.order_id
),
','
)
),
続いて、お届け先都道府県です。サブクエリの中に、更にサブクエリがありますので大変難しいですわね！
(
SELECT
array_to_string(
array(
SELECT
(
SELECT
mtb_pref.name
FROM
mtb_pref
WHERE
mtb_pref.id = dtb_shipping.shipping_pref
)
FROM
dtb_shipping
WHERE
dtb_shipping.order_id = dtb_order.order_id
),
','
)
)
この抜書きした部分のみに注目いたしますと、
- 外側の WHERE 句「dtb_shipping.order_id = dtb_order.order_id」に一致する行の中から、
- 内側の WHERE 句「mtb_pref.id = dtb_shipping.shipping_pref」に一致する行を抽出し、
- それらの mtb_pref を取り出している、
という手順ですの♪大変複雑ですわね♪
コンテンツ管理＞CSV出力項目設定＞高度な設定に設定する SQL 文
実際にフォームに入力するときは、1 行目の SELECT は入力しません。また、最後の 1 文の ; も入力しません。
SELECT
dtb_order.order_id,
dtb_order.customer_id,
dtb_order.message,
dtb_order.order_name01,
dtb_order.order_name02,
dtb_order.order_kana01,
dtb_order.order_kana02,
dtb_order.order_email,
dtb_order.order_tel01,
dtb_order.order_tel02,
dtb_order.order_tel03,
dtb_order.order_fax01,
dtb_order.order_fax02,
dtb_order.order_fax03,
dtb_order.order_zip01,
dtb_order.order_zip02,
(SELECT mtb_pref.name FROM mtb_pref WHERE mtb_pref.id = dtb_order.order_pref),
dtb_order.order_addr01,
dtb_order.order_addr02,
dtb_order.order_sex,
dtb_order.order_birth,
dtb_order.order_job,
(SELECT array_to_string(array(SELECT dtb_order_detail.product_name || COALESCE(dtb_order_detail.classcategory_name1, '') || COALESCE(dtb_order_detail.classcategory_name2, '') FROM dtb_order_detail WHERE dtb_order_detail.order_id = dtb_order.order_id), ',')),
dtb_order.subtotal,
dtb_order.discount,
dtb_order.deliv_fee,
dtb_order.charge,
dtb_order.use_point,
dtb_order.add_point, dtb_order.tax,
dtb_order.total,
dtb_order.payment_total,
dtb_order.deliv_id,
dtb_order.payment_method,
dtb_order.note,
dtb_order.status,
dtb_order.create_date,
dtb_order.update_date,
dtb_order.commit_date,
dtb_order.device_type_id,
(SELECT count(*) FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id),
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_id FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')),
(SELECT array_to_string(array(SELECT (SELECT mtb_pref.name FROM mtb_pref WHERE mtb_pref.id = dtb_shipping.shipping_pref) FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ','))
FROM
dtb_order
WHERE
del_flg = 0
;
おわりに
にて受注 CSV を SQL で実現する方法を確かめました。
そして次の投稿で、1 受注が複数行に鳴ることを許容して 1 商品を 1 行に出力する方法を投稿いたしました。
今回は、1 受注 1 行にこだわり、商品やお届け先など複数あるものをひとつのセルに詰め込む、ムギュウムギュウする方法を考えました。
最善な方法は、ケース・バイ・ケースと存じます。柔軟に対応できるように、力を蓄えたいですの♪
備えよう。カラテあるのみ。すべてはカラテなのだ。
以上です。