1590759207 SQLでサブクエリの中の結果もクエリの実行結果に表示させたいです

こちら→ https://xfs.jp/Mgz34x は、「水族館テーブル」と「生物テーブル」の2つのテーブルを持つ、Accessのデータべースファイルです。

それに対して、下記のクエリAを実行してみます。

【クエリA】-------------
select 水族館テーブル.都道府県ID,種類テーブル.seibutsu
from 水族館テーブル left join
(select 管轄,count(管轄) as seibutsu
from 生物テーブル
where 20200601
group by 管轄
) 種類テーブル on (水族館テーブル.都道府県ID = 種類テーブル.管轄)
order by 水族館テーブル.順番
------------------------

そうすると、添付画像の①のような結果になります。
水族館テーブルから、サブクエリにて生物テーブルから取得した「管轄」列およびcount関数で「管轄」列に入っているデータを集計した列がselectで選択され、結果が「seibutsu」列として、「都道府県ID」列とともに表示されます。

そして上記のクエリのうち、"()"で囲まれたサブクエリの中のSQL、

------------------------
select 管轄,count(管轄) as seibutsu
from 生物テーブル
where 20200601
group by 管轄
------------------------

だけを実行すると、添付画像の②のような結果が出てきます。

回答の条件
  • 1人20回まで
  • 登録:
  • 終了:2020/06/02 06:50:43
id:moon-fondu

ここにおきまして、②も、最終的な一覧の結果に表示させたいと思いまして。

【クエリA】をどのように書き換えれば、添付画像の③のような結果を表示させることができるのか、考えているのですが判らず…。

お力添えいただけますと幸いです。
よろしくお願い致します<m(__)m>

ベストアンサー

id:a-kuma3 No.1

回答回数4848ベストアンサー獲得回数2090

ポイント1300pt

水族館テーブルと、生物テーブルへの副問い合わせの結果を結びつけるキーがないために、質問されているような状態になってます。
添付された画像にある③の見た目を作るために、こじつけて考えると、水族館テーブルの都道府県ID に 100 を足したものが生物テーブルの管轄になっているように見えるので、以下のような SQL とかになります。

select 水族館テーブル.都道府県ID,種類テーブル.管轄,種類テーブル.seibutsu
from 水族館テーブル left join
(select 管轄,count(管轄) as seibutsu
from 生物テーブル
where 20200601
group by 管轄
) 種類テーブル on (水族館テーブル.都道府県ID + 100 = 種類テーブル.管轄)
order by 水族館テーブル.順番
  • 外側の select に、種類テーブル.管轄を追加
  • left join の結合条件(on)で、都道府県ID + 100 と水族館テーブルの管轄を比較
  • 意味のない where(常に真)は、敢えて残してあります


質問の世界で考えて見ると、生物テーブルの管轄は水族館のID かと思います。
水族館テーブルの方を見ると、auto increment と思われる順番はありますが、水族館を一意に表すID がありません。

そう思うと、以下のようになるのが自然なのかな、と想像します。

  • 水族館テーブルに、水族館を表すID(水族館ID)を作る
  • 実行する SQL の on は、水族館テーブル.水族館ID = 種類テーブル.管轄
他3件のコメントを見る
id:moon-fondu

質問がわかりにくく、申し訳ございません。
join句はそうだったのですね。勉強になります。

そうなんです、自分はサブクエリで2つ架空テーブルを作って、それをjoin句で繋げて1つのテーブルにすることに固執しておりましたが。
a-kuma3さんとやり取りをする中で、その必要はないことに気付きました。
頓珍漢な質問をしてすみません。
添付画像③のように、2つのテーブルを1つにするなら、結合条件となる主キーを指定しないとですよね。
質問では、主キーを何にしたいのかも明示できていなかったと思います。

2つ目にやり直した質問も、滅茶苦茶でした。
都道府県ID、水族館IDに重複した値が出てきており、集計結果になってないですね…。
水族館テーブルに出てくる列の値は、同じ値は出てこない、結合条件となる主キーを保持しているテーブルであることが抜け落ちておりました。

都道府県IDの数、水族館IDの数を調べるのが目的でした。
集計結果は、別々のクエリを作成して、何かしら名前を付けて架空テーブルを生み出し、一旦解決できそうです。

ありがとうございました。

2020/06/02 06:49:46
id:a-kuma3

自分はサブクエリで2つ架空テーブルを作って、それをjoin句で繋げて1つのテーブルにすることに固執しておりましたが。
a-kuma3さんとやり取りをする中で、その必要はないことに気付きました。
頓珍漢な質問をしてすみません。

頓珍漢ってことはないと思います。

Access で、複数の join を書けないのは、はっきり言って地雷です。
公式ドキュメントでも、制限のところには書いてありませんし。
他の RDB を経験している人ならあるあるかもしれません。

あと、Access だと、あまり気にする場面が無いかもしれませんが、クエリの数は、性能に直結します。
サブクエリの数は、少ない方が良い。
join を使うときは、ベースになるクエリでインデックスをフルに使って、join で結合するのは、プライマリキーか、かなり狭い範囲まで絞り込めるインデックスを使う、というのが性能的に吉です。

これから先で使う場面があるかもしれないので、頭の片隅に置いといても良いかな、と思います。

2020/06/02 21:24:23

その他の回答0件)

id:a-kuma3 No.1

回答回数4848ベストアンサー獲得回数2090ここでベストアンサー

ポイント1300pt

水族館テーブルと、生物テーブルへの副問い合わせの結果を結びつけるキーがないために、質問されているような状態になってます。
添付された画像にある③の見た目を作るために、こじつけて考えると、水族館テーブルの都道府県ID に 100 を足したものが生物テーブルの管轄になっているように見えるので、以下のような SQL とかになります。

select 水族館テーブル.都道府県ID,種類テーブル.管轄,種類テーブル.seibutsu
from 水族館テーブル left join
(select 管轄,count(管轄) as seibutsu
from 生物テーブル
where 20200601
group by 管轄
) 種類テーブル on (水族館テーブル.都道府県ID + 100 = 種類テーブル.管轄)
order by 水族館テーブル.順番
  • 外側の select に、種類テーブル.管轄を追加
  • left join の結合条件(on)で、都道府県ID + 100 と水族館テーブルの管轄を比較
  • 意味のない where(常に真)は、敢えて残してあります


質問の世界で考えて見ると、生物テーブルの管轄は水族館のID かと思います。
水族館テーブルの方を見ると、auto increment と思われる順番はありますが、水族館を一意に表すID がありません。

そう思うと、以下のようになるのが自然なのかな、と想像します。

  • 水族館テーブルに、水族館を表すID(水族館ID)を作る
  • 実行する SQL の on は、水族館テーブル.水族館ID = 種類テーブル.管轄
他3件のコメントを見る
id:moon-fondu

質問がわかりにくく、申し訳ございません。
join句はそうだったのですね。勉強になります。

そうなんです、自分はサブクエリで2つ架空テーブルを作って、それをjoin句で繋げて1つのテーブルにすることに固執しておりましたが。
a-kuma3さんとやり取りをする中で、その必要はないことに気付きました。
頓珍漢な質問をしてすみません。
添付画像③のように、2つのテーブルを1つにするなら、結合条件となる主キーを指定しないとですよね。
質問では、主キーを何にしたいのかも明示できていなかったと思います。

2つ目にやり直した質問も、滅茶苦茶でした。
都道府県ID、水族館IDに重複した値が出てきており、集計結果になってないですね…。
水族館テーブルに出てくる列の値は、同じ値は出てこない、結合条件となる主キーを保持しているテーブルであることが抜け落ちておりました。

都道府県IDの数、水族館IDの数を調べるのが目的でした。
集計結果は、別々のクエリを作成して、何かしら名前を付けて架空テーブルを生み出し、一旦解決できそうです。

ありがとうございました。

2020/06/02 06:49:46
id:a-kuma3

自分はサブクエリで2つ架空テーブルを作って、それをjoin句で繋げて1つのテーブルにすることに固執しておりましたが。
a-kuma3さんとやり取りをする中で、その必要はないことに気付きました。
頓珍漢な質問をしてすみません。

頓珍漢ってことはないと思います。

Access で、複数の join を書けないのは、はっきり言って地雷です。
公式ドキュメントでも、制限のところには書いてありませんし。
他の RDB を経験している人ならあるあるかもしれません。

あと、Access だと、あまり気にする場面が無いかもしれませんが、クエリの数は、性能に直結します。
サブクエリの数は、少ない方が良い。
join を使うときは、ベースになるクエリでインデックスをフルに使って、join で結合するのは、プライマリキーか、かなり狭い範囲まで絞り込めるインデックスを使う、というのが性能的に吉です。

これから先で使う場面があるかもしれないので、頭の片隅に置いといても良いかな、と思います。

2020/06/02 21:24:23

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません