質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

3回答

336閲覧

MYSQLでカラムの編集と別テーブルへレコードの作成

starslip0120

総合スコア28

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2017/07/13 02:04

編集2017/07/13 03:50

以下のテーブルがあります。

顧客マスタ
(主キーは『顧客ID』と『拠点ID』になります)

顧客ID拠点ID拠点名住所エリア電話番号
0000011A店東京都足立区〇〇〇〇足立区000-000-0000
0000012B店東京都足立区××××足立区111-111-1111
0000013C店東京都墨田区□□□□墨田区222-222-2222
0000021H店東京都足立区△△△△足立区333-333-3333
0000022I店東京都台東区◎◎◎◎台東区444-444-4444
0000031X店東京都練馬区◆◆◆◆練馬区555-555-5555

このテーブルの顧客IDとエリアを使用して、空のエリアテーブルにレコードを追加を行い、
顧客マスタのエリアのデータ形式とフィールドの変更がしたいです。
変更後以下の顧客マスタとエリアテーブルは以下のようにしたいです。

顧客マスタ
(主キーは『顧客ID』と『拠点ID』になります)

顧客ID拠点ID拠点名住所エリアID電話番号
0000011A店東京都足立区〇〇〇〇1000-000-0000
0000012B店東京都足立区××××1111-111-1111
0000013C店東京都墨田区□□□□2222-222-2222
0000021H店東京都足立区△△△△1333-333-3333
0000022I店東京都台東区◎◎◎◎2444-444-4444
0000031X店東京都練馬区◆◆◆◆1555-555-5555

エリアテーブル
(主キーは『顧客ID』と『エリアID』になります)

顧客IDエリアIDエリア名
0000011足立区
0000012墨田区
0000021足立区
0000022台東区
0000031練馬区

このように追加と変更ができるSQLが必要なのですが、どのようなSQLを書けばよろしいでしょうか?

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

daive

2017/07/13 03:15

同一エリアIDで、別なエリア名が割当されている理由等を追記する必要が有るのでは?
starslip0120

2017/07/13 03:48

エリアテーブルは顧客IDとエリアIDが主キーとなります。
A.Ichi

2017/07/13 04:02

エリアテーブルの000001は、顧客マスタでの拠点ID1,2の2名を指していると考えて良いでしょうか?
starslip0120

2017/07/13 04:10

はい、顧客マスタの顧客IDが000001の拠点IDが1と2のエリアは同一のエリアとなるので、エリアテーブルの一番上の足立区を参照する形になります。
A.Ichi

2017/07/13 04:25 編集

拠点1,2、3の000001顧客は同一人物なのでしょうか?顧客マスタなので別人ですか?
guest

回答3

0

ベストアンサー

エリアテーブルの仕様がおかしくないですか?
正規化をするのであればエリア毎にユニークにエリアIDを振っていくものだと
思いますがそうなっているようには見えません
エリアIDはなにに紐付いているのでしょうか?

sample

あらかじめエリアIDカラムを作っておくといいでしょう。
(エリアカラムが不要であれば後で削除します)

create table tbl( 顧客ID int(6) unsigned zerofill, 拠点ID int, 拠点名 varchar(20), 住所 varchar(100), エリア varchar(20), エリアID int null, 電話番号 varchar(20) ); insert into tbl values (1,1,'A店','東京都足立区〇〇〇〇','足立区',NULL,'000-000-0000'), (1,2,'B店','東京都足立区××××','足立区',NULL,'111-111-1111'), (1,3,'C店','東京都墨田区□□□□','墨田区',NULL,'222-222-2222'), (2,1,'H店','東京都足立区△△△△','足立区',NULL,'333-333-3333'), (2,2,'I店','東京都台東区◎◎◎◎','台東区',NULL,'444-444-4444'), (3,1,'X店','東京都練馬区◆◆◆◆','練馬区',NULL,'555-555-5555');

エリア名順に採番してエリアIDに埋め込みます

SQL

1update tbl as t0, 2(select (select count(distinct 顧客ID)+1 from tbl as t2 3where t1.顧客ID=t2.顧客ID and t1.エリア>t2.エリア 4)as エリアID ,顧客ID,エリア from tbl as t1) as t3 5set t0.エリアID=t3.エリアID 6WHERE t0.顧客ID=t3.顧客ID and t0.エリア=t3.エリア;

エリアテーブルはインタラクティブに参照できるので不要では?
上記更新のうちエリアテーブルに該当する箇所

SQL

1select (select count(distinct 顧客ID)+1 from tbl as t2 2where t1.顧客ID=t2.顧客ID and t1.エリア>t2.エリア 3)as エリアID ,顧客ID,エリア from tbl as t1;

エリアテーブルが必須な場合

そうなると話は逆ですね
正規化において、データからマスターテーブルを作るような泥縄方式はNGです。
最初からエリアテーブルをつくっておいて、それを元に顧客マスターの
データを埋めるのが正しいやり方です。

とは言え、間違った方法で顧客マスターを作っちゃったので、管理方法を
変えたいという意図であれば、前回提示したとおりエリアテーブルを
抽出するSQLを利用してエリアテーブルを一度つくればいいでしょう

SQL

1create table area( 2エリアID int, 3顧客ID int(6) unsigned zerofill, 4エリア varchar(20), 5責任者ID int null, 6unique(エリアID,顧客ID));

データ投入

SQL

1insert into area select distinct (select count(distinct 顧客ID)+1 from tbl as t2 2where t1.顧客ID=t2.顧客ID and t1.エリア>t2.エリア 3)as エリアID ,顧客ID,エリア,null from tbl as t1;

投稿2017/07/13 04:07

編集2017/07/13 07:06
yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

starslip0120

2017/07/13 04:20

エリアIDは顧客IDに紐づいています。 エリアテーブルの主キーは顧客IDとエリアIDになっています。 例えば、変更前の顧客マスタには足立区のエリアが3つありますが、顧客IDが違う場合は別々のレコードから参照したいです。
yambejp

2017/07/13 04:50

なるほど、なんとなく分かってきました 顧客IDとエリア名がユニークになるように、顧客IDごとに1番から採番する感じでしょうか? 顧客ID=1の足立と顧客ID=2の足立がそれぞれエリアIDが同じ1なのは 「たまたま」ということでよいですか?そうでないと顧客ID=3の たとえば墨田が1で足立が2となってもいいのでしょうか? 墨田のエリアIDが1である整合性がとれません。 ちなみにエリアIDの採番ルールは何順とかあるのでしょうか?
starslip0120

2017/07/13 05:46 編集

>>顧客IDごとに1番から採番する感じでしょうか?   はい >>顧客ID=1の足立と顧客ID=2の足立がそれぞれエリアIDが同じ1なのは「たまたま」ということでよいですか?   はい >>墨田が1で足立が2となってもいいのでしょうか?   はい >>エリアIDの採番ルールは何順とかあるのでしょうか?   1から連番で登録します
yambejp

2017/07/13 05:59 編集

追記しておきました エリアテーブルをどうしてもほしいのであれば テーブルではなくviewをつくっておけばいいかもしれません
starslip0120

2017/07/13 06:07

sampleをありがとうございます。 申し訳ございませんが説明不足の部分がありました。 エリアテーブルですが、今後エリアテーブルにカラムを追加して、顧客ごとかつ、エリアごとの情報を格納予定なのでエリアテーブルが必要になります。 例:特定の顧客の特定のエリア内の総合責任者など
yambejp

2017/07/13 07:07

エリアテーブルについて追記しておきました。 テーブルの設計はあとから無駄な拡張にならないよう きちんと仕様をきめてからおこなってください
starslip0120

2017/07/14 00:39

ありがとうございます。 参考にさせていただきます。
guest

0

参考程度。
複合 主キー さける
で、検索して、
改修前に「複合主キー」は、避けた方が良い理由を掴んで下さい。

投稿2017/07/13 06:43

daive

総合スコア2028

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ほしいモノと違うかもしれないが、参考まで。
エリアIDの連番をどうふるかだけが問題だが、エリアIDが連番でなくて良いのであれば、「グループ化して拠点IDのMAX」をエリアIDに使えば一応標準のSQLでかつ手続き的な処理無しでもいけることはいける。

SQL

1INSERT INTO エリアテーブル 2SELECT 顧客ID,MAX(拠点ID) AS エリアID,エリア名 FROM 顧客マスタ GROUP BY 顧客ID,エリア名 3 4INSERT INTO 顧客マスタ() 5SELECT a.顧客ID,a.拠点ID,a.拠点名,a.住所,b.エリアID,a.電話番号 FROM 顧客マスタ a 6INNER JOIN 7エリアテーブル b 8ON a.顧客ID=b.顧客ID AND a.エリア名=b.エリア名

投稿2017/07/13 05:02

kurokoba

総合スコア276

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問