SQLでLEFT JOINを使わずに文字列結合と完全nullレコードはなしでの出力

SQLでLEFT JOINを使わずに文字列結合と完全nullレコードなしでの出力

どんなヒントでもいいので、視点をください。
SQL初心者ですが、回答しなければ仕事がもらえない状況で切羽詰っています。

「left joinを使わずにという件名で考えたsqlにleft jonがある」のは、元々問題を与えられたときにはupdateやinsert、drop、createは使うな、書き換えは禁止と言われていただけだったので、自分なりに調べleft joinの自己結合でなら出力が可能でした。そして、それを持っていったところ、急にleft joinも使わずにおこなうように言われました。

前提・実現したいこと

updateやinsertを使わずにselectで
上のテーブルの状態から下のテーブルの状態での出力

スプレッドシートの白い部分はnullです。よろしくお願いします。

該当のソースコード

SQL server
自分なりに考えたソースコード

select
 ROW_NUMBER() OVER(ORDER BY a.id ASC) id,
 a.名前,
 a.性別,
 a.住所 + b.住所 + c.住所 as 住所,
 a.id2,
 a.備考
from table a
left join table b
on a.id = b.id - 1
left join table c
on a.id = c.id -2
where 備考 is not null;

家ではSQLを扱える環境がないため、上記のコードが間違えているかもしれませんが、よろしくお願いします。

なんでもいいので、アドバイスをください。

試したこと

・LEFT JOINの代わりにNOT EXISTSが使えるみたいにインターネット上には書いていますがどうしてもうまくいきません。
・自己結合をinner joinで行うとレコードが減ります。
・基準をidではなくid2でもできないかとも考えましたが、id2で一つにまとめる方法や
エクセルのように空白のセルを上と同じにする方法がSQLでどうすればいいのかわかりません。

心優しい方、解き方をご教示ください。

丸投げみたいな感じで申し訳ありませんが、私には難しすぎて困ってます。解放の手がかりが欲しいのです。
よろしくお願いします。

gentaro

2020/04/08 11:09

> SQL初心者ですが、回答しなければ仕事がもらえない状況で切羽詰っています。 > 家ではSQLを扱える環境がないため、上記のコードが間違えているかもしれませんが、よろしくお願いします。まず家で学習環境作れないような人は企業も雇いたくないと思うんですが…。

Orlofsky

2020/04/08 11:10

質問者さんは表計算ソフトの延長でデータベースを使うことしか考えていなので、誰も指摘しませんが。 SQLをシンプルにパフォーマンス良く実行できるように、データベースの正規化 https://oss-db.jp/measures/dojo_info_04.shtml に沿って設計します。通常第3正規化まで行います。そろそろ破綻してきたかと。

yuki-yuki

2020/04/08 11:11

確かにそうです。すみません。

yuki-yuki

2020/04/08 11:14

Orlofsky様ありがとうございます。勉強になります！熟読します！

papinianus

2020/04/08 12:33

left joinを使わずにという件名で考えたsqlにleft jonがあるのはどういうことですか？あと、テーブル定義とスプレッドシートの空白はnullなのか空文字なのか明記していただけませんか

yuki-yuki

2020/04/08 12:44

papinianus様コメントありがとうございます。「left joinを使わずにという件名で考えたsqlにleft jonがある」のは、元々問題を与えられたときにはupdateやinsert、drop、createは使うな、書き換えは禁止と言われていただけだったので、自分なりに調べleft joinの自己結合でなら出力が可能でした。そして、それを持っていったところ、急にleft joinも使わずにおこなうように言われました。正直なところ新人イジメなのかな？とさえ思いましたが、ここであればそういう事に精通している方からアドバイスをいただけると思い、質問をさせていただきました。スプレッドシートの白い部分はnullです。よろしくお願いします。

Orlofsky

2020/04/08 12:46

今は自宅でデータベースを動かしてテレワークできる環境を用意できないと、通勤途中や職場で COVID-19 に感染して発病したら「健康優良20代」で症状が軽くてもこんなに悲惨になることも。 http://nyanyannyan.hatenablog.com/entry/2020/04/05/233420 人口約860万人のニューヨークのようになったら銀行の勘定システムの運用であろうとテレワークできなかったら運用も開発もできなくなるかと。左下 Admin0 → Admin2 76,876 confirmed New York City New York US 4,009 deaths New York City New York US Last Updated at (M/D/YYYY) 4/8/2020 9:15:00 午後 https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6

Orlofsky

2020/04/08 12:50

質問者さんはSQLの能力で給料がもらえるレベルには程遠いです。同じSQLでもデータベースやそのバージョンによって方言が大きいです。SQLの観点から Oracle Database, PostgreSQL, MySQL の特徴を整理しよう！ https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html >使うデータベース名 SQL 入門でググってURLを見るなる、入門書を買ってきて勉強しては？

yuki-yuki

2020/04/08 12:54

Orlofsky様通勤途中や職場で COVID-19に感染することのご心配までありがとうございます。テレワークできる会社であれば嬉しいのですが、今は触らせていただけていませんが開発などは社内のパソコンでしか触れないセキュリティーになっています。ただ、新人でそういったものにすら触らせてもらえず、雑用を毎日押し付けられ、教育という名目で何も教えられないまま難問を与えられております。自分が選んだ会社ではありますが、流石に最近は心滅入ってしまっています。

yuki-yuki

2020/04/08 13:06

Orlofsky様ありがとうございます。確かにそうですよね。給料をいただけるレベルではないと思います。会社説明では研修制度もしっかりしているとおっしゃっていましたが、いざ入ってみると皆様お忙しいようです。会社の本は読んではいますが、やはり会社にいるときだけだと足りないですね。早速明日出社時にsql serverの本を購入します。良いアドバイスありがとうございます。

papinianus

2020/04/08 14:02

作りがまずい以前におよそ何がしたいか分からないのですが本当にこれは必要な処理なんですよね？ SQL でできないことはないとは思いますが、それでもできなくないですか？ id2 が空白の行がつまったせいで、清水玲子さんは id が 8 から 7 に変更されているんですが、本当にこうしたいんでしょうか？

gentaro

2020/04/08 14:14

会社の研修問題とかなら、解決できないなら教育担当者に正直に相談しましょう。ここで答えだけ聞いても無意味です。会社側が自分で勉強して回答を持ってこい、という趣旨で出している問題であれば、ここで質問よりまず自習じゃないですかね。

yuki-yuki

2020/04/08 14:14

papinianus様、本当にそうですよね。何がしたいのか私もわかりません。上司の新人イジメか激励としての試練なのか・・・。本当に解ける必要あるの？って思いました。私は、ROW_NUMBERでIDに再数値化しましたが、すっかり忘れていました。あとで修正しておきます。業務では必要ないと思いますが、そのことを上司に伝えたところ、上司いわく考え方がなってないと一撃されました。正直こんな難問、プロの方はすぐに解けるものでしょうか？

yuki-yuki

2020/04/08 14:24

gentaro様、ありがとうございます。確かにその通りで、自分なりに調べたりしたのですが、一つの解放以外皆目検討もつかず途方にくれてしまいました。教育担当の方は違うとだけしか教えてくれず、何を聞いてもお前にはできないの一点張りです。なので、何かしらのキッカケをいただけたらと思い、ここでご相談させていただきました。私の行動が間違っているかもしれませんが、正直疲弊しています。

gentaro

2020/04/08 14:30

まぁ心中はお察ししますが、課題で出されているものの回答を答えるサイトじゃないので。 https://teratail.com/help/avoid-asking 「何かを作りたいのでコードを書いてほしい、学校の課題を解いてほしい等の質問は、具体的にプログラミングで困っている質問ではないと考え、推奨していません。」直接的なアドバイスをもらえないなら、せめて参考になるテキストを聞いてみたり、研修を受けさせてくれと言ってみたり、同僚に相談してみたり、その教育担当者の上司に相談してみたり、色々アプローチの方法はあろうかと思いますが、それでもどうしようもないならその会社があなたに合ってない（求められているスキルとマッチしていない）と判断するしかないですね。自分の能力を引き出せない馬鹿な会社だ、ぐらいに思って別の会社に移ることを検討してもいいんじゃないですかね。

yuki-yuki

2020/04/08 14:38

gentaro様、確かにそうですね。間違った質問してしまい申し訳ありません。そういう場合はどうすれば削除できるのでしょうか？エンジニアのかたがたが集まるサイトのようでしたので、希望が見つかるかと思い間違った質問してしまいました。申し訳ありません。質問に答えてくださった方々のお掛けで少し、なにか見えてきた気がします。明日からも頑張ります。本当にありがとうございました。

papinianus

2020/04/08 14:54

削除依頼を出すと削除できたはず。しかし、回答がつくと削除できない縛りがあったはず。

yuki-yuki

2020/04/08 19:41

このサイトの使い方も分からず、皆さまに不快な思いをさせてしまい申し訳ありません。エンジニアの夢を持ちましたが、右も左も分からない私はエンジニアは向かないのかなと思うこともありますが、このサイトの皆さまのおかげで勉強の意欲がさらに高まりました。会社の研修などに頼ってたり、先輩や上司に甘えていたのかもしれません。もっと家でも自学自習を行い皆さまのように価値を生み出せるエンジニアになって行きます。本当にありがとうございました。

行動規範の内容に同意します

回答1件

ベストアンサー

SQL Server が起動しなかったので MySQL で。
MySQL だと ID の振り直しがしんどいので、適宜やってください。

結果として見えているものは、id2 および備考列が空白でない行と言えるので、それを最外の WHERE で絞って、あとは欲しい値を埋めればいいと考えました。

前回質問のように住所がかならず 3 レコードで構成されることが確定していれば、前回回答のように id を計算することで、住所を横に展開できます。concat したければ適宜どうぞ。

sql
1CREATE TABLE `252254` (
2	`id` INT(11) NOT NULL AUTO_INCREMENT,
3	`name` VARCHAR(50) NULL DEFAULT NULL,
4	`sex` TINYINT(4) NULL DEFAULT NULL,
5	`addr` VARCHAR(50) NULL DEFAULT NULL,
6	`id2` INT(11) NULL DEFAULT NULL,
7	`hobby` VARCHAR(50) NULL DEFAULT NULL,
8	PRIMARY KEY (`id`)
9)
10COLLATE='utf8_general_ci'
11ENGINE=InnoDB
12;
13INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (1, 'yamada', 1, 'oosakapref', 1, 'surf');
14INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (2, NULL, NULL, 'osakacity', 2, 'hiking');
15INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (3, NULL, NULL, 'kita', 3, 'diving');
16INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (4, NULL, NULL, NULL, 4, 'climb');
17INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (5, 'yamamoto', 1, 'kyotopref', 1, 'swim');
18INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (6, NULL, NULL, 'kyotocity', 2, 'cook');
19INSERT INTO `252254` (`id`, `name`, `sex`, `addr`, `id2`, `hobby`) VALUES (7, NULL, NULL, 'nakagyo', NULL, NULL);
20
21SELECT
22 id,
23 NAME,
24 sex,
25 CASE WHEN NAME IS NOT NULL THEN (SELECT addr FROM `252254` WHERE id = o.id - o.id2 + 1) ELSE NULL END AS pref,
26 CASE WHEN NAME IS NOT NULL THEN (SELECT addr FROM `252254` WHERE id = o.id - o.id2 + 2) ELSE NULL END AS city,
27 CASE WHEN NAME IS NOT NULL THEN (SELECT addr FROM `252254` WHERE id = o.id - o.id2 + 3) ELSE NULL END AS dist,
28 id2,
29 hobby
30FROM `252254` AS o
31 WHERE id2 IS NOT NULL

投稿2020/04/08 14:52

papinianus

総合スコア12705

yuki-yuki

2020/04/08 19:35

papinianus様ありがとうございます。 CASE文の使い方を理解するための問題だったのですね。かたい頭の私には難問に思えてた事も発想力豊かな方は直ぐに解かれてビックリです。まだまだ知識も技術も低いかもしれませんが、エンジニアで世界に貢献する夢のために勉強も技術の向上をおこなっていきます。分かりやすくコードで教えてくださりありがとうございます。 idを基準にid2で条件を定めることなどは全く発送もしませんでした。本当にありがとうございます。本も昨日Amazonで購入しましたので今後の励みにしていきます。