質問編集履歴
2
```lang-sql → ```sql としました。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -3,7 +3,7 @@
|
|
|
3
3
|
あるシステムを移植しようとしております。
|
|
4
4
|
|
|
5
5
|
元のテーブル`table`はphpmyadminからsql文を
|
|
6
|
-
```
|
|
6
|
+
```sql
|
|
7
7
|
select o.orders_id as 伝票番号, o.customers_id as 顧客コード, o.date_purchased as 受注日, o.ec_shukkabi as 出荷日, o.customers_public_id as 顧客分類, o.customers_company as 顧客会社名, o.customers_company_post as 顧客部署名, o.customers_name as 顧客名, o.payment_method as 支払方法名, o.ec_nyukimbi as 入金日, o.ec_noshigami as のしがみ有無, o.ec_noshigamina as のしがみ名, o.ec_noshigamiatena as のしがみ宛名, op.ec_seq as SEQ_NO, op.ec_dempyougyouban as 伝票行番, case WHEN ps.child_products_id > 0 THEN ps.child_products_id ELSE op.products_id END AS 製品id, (select products_model from products where products_id = 製品id) AS 商品コード, (select products_name from products_description where products_id = 製品id and language_id = '2') AS 商品名, o.delivery_public_id as 出荷先分類, o.delivery_company as 出荷先会社名, o.delivery_company_post as 出荷先部署名, o.delivery_name as 出荷先名, o.delivery_postcode as 出荷先郵便番号, o.delivery_state as 出荷先都道府県, o.delivery_city as 出荷先市町村, o.delivery_street_address as 出荷先住所, o.delivery_suburb as 出荷先住所２, o.delivery_telephone as 出荷先電話番号, o.delivery_fax as 出荷先FAX番号, op.ec_nouhinyoteibi as 納品予定日, op.ec_nouhinyoteijikan as 納品予定時間時頃, op.ec_takkyuubintoiawasebangou as 宅急便問合せ番号, case WHEN ps.child_products_id > 0 THEN ps.child_products_quantity * op.products_quantity ELSE op.products_quantity END as 数量, case WHEN ps.child_products_id > 0 THEN '' ELSE op.products_price END as 販売単価, case WHEN ps.child_products_id > 0 THEN '' ELSE op.ec_kingaku END as 金額, op.orders_products_id as 製品別伝票番号, o.ec_unsougyousha as 運送業者, case WHEN ps.child_products_id > 0 THEN '' ELSE o.order_total END as 合計金額 from orders o left join orders_products op on o.orders_id= op.orders_id left join products_set ps on ps.parent_products_id = op.products_id where ec_shukkabi = date('0000-00-00') and ec_kyanserufuragu = 0 ORDER BY o.orders_id , op.orders_products_id , ps.products_sort_order
|
|
8
8
|
```
|
|
9
9
|
と実行すると
|
1
コードを https://teratail\.com/help のように-------- コードを入力 ```lang-言語名 テキスト ``` -------- で編集しました。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -3,17 +3,17 @@
|
|
|
3
3
|
あるシステムを移植しようとしております。
|
|
4
4
|
|
|
5
5
|
元のテーブル`table`はphpmyadminからsql文を
|
|
6
|
+
```lang-sql
|
|
7
|
+
select o.orders_id as 伝票番号, o.customers_id as 顧客コード, o.date_purchased as 受注日, o.ec_shukkabi as 出荷日, o.customers_public_id as 顧客分類, o.customers_company as 顧客会社名, o.customers_company_post as 顧客部署名, o.customers_name as 顧客名, o.payment_method as 支払方法名, o.ec_nyukimbi as 入金日, o.ec_noshigami as のしがみ有無, o.ec_noshigamina as のしがみ名, o.ec_noshigamiatena as のしがみ宛名, op.ec_seq as SEQ_NO, op.ec_dempyougyouban as 伝票行番, case WHEN ps.child_products_id > 0 THEN ps.child_products_id ELSE op.products_id END AS 製品id, (select products_model from products where products_id = 製品id) AS 商品コード, (select products_name from products_description where products_id = 製品id and language_id = '2') AS 商品名, o.delivery_public_id as 出荷先分類, o.delivery_company as 出荷先会社名, o.delivery_company_post as 出荷先部署名, o.delivery_name as 出荷先名, o.delivery_postcode as 出荷先郵便番号, o.delivery_state as 出荷先都道府県, o.delivery_city as 出荷先市町村, o.delivery_street_address as 出荷先住所, o.delivery_suburb as 出荷先住所２, o.delivery_telephone as 出荷先電話番号, o.delivery_fax as 出荷先FAX番号, op.ec_nouhinyoteibi as 納品予定日, op.ec_nouhinyoteijikan as 納品予定時間時頃, op.ec_takkyuubintoiawasebangou as 宅急便問合せ番号, case WHEN ps.child_products_id > 0 THEN ps.child_products_quantity * op.products_quantity ELSE op.products_quantity END as 数量, case WHEN ps.child_products_id > 0 THEN '' ELSE op.products_price END as 販売単価, case WHEN ps.child_products_id > 0 THEN '' ELSE op.ec_kingaku END as 金額, op.orders_products_id as 製品別伝票番号, o.ec_unsougyousha as 運送業者, case WHEN ps.child_products_id > 0 THEN '' ELSE o.order_total END as 合計金額 from orders o left join orders_products op on o.orders_id= op.orders_id left join products_set ps on ps.parent_products_id = op.products_id where ec_shukkabi = date('0000-00-00') and ec_kyanserufuragu = 0 ORDER BY o.orders_id , op.orders_products_id , ps.products_sort_order
|
|
8
|
+
```
|
|
9
|
+
と実行すると
|
|
6
10
|
--------
|
|
7
|
-
|
|
11
|
+
This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
|
|
8
|
-
--------
|
|
9
|
-
と
|
|
12
|
+
-------- とでますが思った通りの値が返ってきます。
|
|
10
13
|
|
|
11
|
-
|
|
12
14
|
ですがそのデータベースを同じmysqlの`test_tabel`に`table`をコピーして
|
|
13
15
|
同様のsql文を発行すると
|
|
14
16
|
--------
|
|
15
|
-
select o.orders_id as 伝票番号, o.customers_id as 顧客コード, o.date_purchased as 受注日, o.ec_shukkabi as 出荷日, o.customers_public_id as 顧客分類,……
|
|
16
|
-
--------
|
|
17
17
|
#1054 - Unknown column 'o.ec_shukkabi' in 'field list'
|
|
18
18
|
--------
|
|
19
19
|
とのエラーが出てしまいます。
|