バージョン選択

フォーラム

メニュー

オンライン状況

74 人のユーザが現在オンラインです。 (60 人のユーザが フォーラム を参照しています。)
登録ユーザ: 0
ゲスト: 74
もっと...

サイト内検索

質問 > 管理機能 > CSV高度な設定 配送先を含んだ受注データの配送先県名について

管理機能

新規スレッドを追加する

スレッド表示 | 新しいものから 前のトピック | 次のトピック | 下へ
投稿者 スレッド
l58
投稿日時: 2018/2/19 21:29
対応状況: −−−
半人前
登録日: 2015/2/15
居住地:
投稿: 24
CSV高度な設定 配送先を含んだ受注データの配送先県名について
[EC-CUBE] 2.13.5
[レンタルサーバ] WADAX
[OS] win7
[PHP] 5.3.3
[データベース] MySQL 5.1.73
[WEBサーバ] Apache

いつもお世話になっております。
CSV高度な設定 配送先を含んだ受注データの配送先県ですが、IDではなくて県名で出力したいのですが、どのように書けばよいのでしょうか。

注文者情報の件はIDではなく数字で表示できたのですが、、

dtb_order.customer_id,
dtb_order.order_id as "注文番号",
dtb_order_detail.order_detail_id,
dtb_order.create_date,
(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), ',')) as "shipping_id",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_name01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_name01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_name02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_name02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_kana01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')),
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_kana02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_kana01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_zip01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_zip01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_zip02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_zip02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_pref FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "県名",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_addr01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_addr01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_addr02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_addr02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_tel01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_tel01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_tel02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_tel02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_tel03 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_tel03",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_fax01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_fax01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_fax02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_fax02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_fax03 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_fax03",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_date FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_date",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_time FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_time",
dtb_order.payment_total,
dtb_order.payment_method,
dtb_order.deliv_fee,
dtb_order.charge,
dtb_order.use_point,
dtb_order.add_point,
dtb_order.device_type_id,
dtb_order.status,
dtb_order.order_name01,
dtb_order.order_name02,
dtb_order.order_kana01,
dtb_order.order_kana02,
dtb_order.order_sex,
dtb_order.order_birth,
dtb_order.order_email,
dtb_order.order_tel01,
dtb_order.order_tel02,
dtb_order.order_tel03,
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.message,
dtb_order_detail.product_name,
dtb_order_detail.product_id,
dtb_order_detail.product_code,
dtb_order_detail.classcategory_name1,
dtb_order_detail.classcategory_name2,
dtb_order_detail.price,
dtb_order_detail.quantity,
dtb_order.total,
dtb_order.tax,
dtb_order.subtotal,
dtb_order.discount,
dtb_order.note,
dtb_order.update_date,
dtb_order.commit_date

FROM
dtb_order
INNER JOIN
dtb_order_detail
ON
dtb_order.order_id = dtb_order_detail.order_id
WHERE
del_flg = 0
AND dtb_order.create_date BETWEEN '2017-12-28 00:00:00' AND '2020-12-31 23:59:59'

(SELECT array_to_string(array(SELECT dtb_shipping.shipping_pref FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "県名",

の箇所なのですが、どうしてもわかりません。
ご教授いただけると幸いです。
宜しくお願い致します。
yuh
投稿日時: 2018/2/21 16:36
対応状況: −−−
登録日: 2013/1/9
居住地: 大阪
投稿: 1515
Re: CSV高度な設定 配送先を含んだ受注データの配送先県名について
dtb_shipping.shipping_pref の部分を
(SELECT name FROM mtb_pref WHERE id = dtb_shipping.shipping_pref)
に変更すればできると思います。


----------------
ec-cube2系の速度UPしてみました。(商品数:100万件)
https://speedtest2.ec-lab.net/products/list.php?category_id=15
ec-cube2系最速!(自称)

l58
投稿日時: 2018/4/10 20:12
対応状況: −−−
半人前
登録日: 2015/2/15
居住地:
投稿: 24
Re: CSV高度な設定 配送先を含んだ受注データの配送先県名について
返答が遅くなり申し訳ありません。
色々試したのですが、構文エラーになってしまいます。

(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), ','))

【エラー内容】

[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR ',') FROM mtb_pref WHERE mtb_pref.id = dtb_shipping.shipping_pref) FR' at line 1]


間違っている箇所がわかりません。
ご教授いただけますと幸いです。
宜しくお願いします。



スレッド表示 | 新しいものから 前のトピック | 次のトピック | トップ


 



ログイン


EC-CUBEペイメント

統計情報

総メンバー数は33,204名です
総投稿数は87,773件です

投稿数ランキング

1
seasoft
7331
2
AMUAMU
2712
3
nanasess
1790
4
yuh
1515
5
468
1271
6
red
1178
7
umebius
1007
8
fukap
907
9
shutta
827
10
tsuji
815
11 ramrun 789
12
tao_s
664
13 karin 656
14 sumida 641
15
homan
633
16 DELIGHT 571
17
h_tanaka
571
18
patapata
502
19
flealog
483
20 tonton 436


ネットショップの壺

EC-CUBEインテグレートパートナー

Copyright© LOCKON CO.,LTD. All Rights Reserved.