カテゴリー: コンピューター

コンテンツ管理＞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,
        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), ','))
FROM
        dtb_order
WHERE 
        del_flg = 0
;