SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ ↓これ何？
26 NAME IS NULL 2016/07/15(金) 18:52:57.55 ID:OwU9VU0D
答えたい気持ちは分かるがお前ら7000円をどうやって分けるつもりなの？
そういう事は最初にキッチリ決めとかないと後々遺恨を残すぜ すみません、以下、質問させてください。
親テーブルAに対して子テーブルBとCがあり、
レコード数がそれぞれ、
A:B = 1:5、A:C = 1:20 の関係です。
この時、A１レコードに対して、
Aに紐付くBとCのレコード全てを最小のIO回数・データ量で取得したいです。
普通にA、B、Cを結合しただけでは
B×Cの組み合わせまで取得してしまい上手く取得できませんでした。
A:B、A:Cと別々に結合して取得するしかないのでしょうか。
DBはOracleです、よろしくお願いします。 >>33
リレーションの説明がありませんので、答えようがありません。 B×Cの中から自分が欲しいものを条件で絞り込むんだよ。その条件が無いから全部出てくる。 >>34
下のような感じです。
AとB、AとCが主キー項目aでそれぞれ1:5、1:20で紐付きます。
Aテーブル
a C(5)
…
Bテーブル
a C(5)
b C(5)
…
Cテーブル
a C(5)
c C(5)
… 質問がいまいち理解できん
そもそもIOとか、SQL書いたとおりに実行されるわけじゃないんだが
ほしい結果がABとACの二つあるなら、２回やるしかないわけだが、どんな結果を望んでるんだ >>38
俺もI/Oについては突っ込みたかったが、そもそもRDBの理論、概念を否定する内容だからスルーしたw >>36
その3つのテーブルをaという列だけで結合すればいい話なのかな？
Aテーブルの1レコードに対して20レコードがセットになればいいの？ BテーブルとCテーブルにリレーション、関連性があるのかないのかはっきりしてくれ。
AテーブルのとあるレコードはBテーブルに子レコードがあり、Aテーブルの別の種類のレコードはCテーブルに子レコードがあるようにも解釈できる。
どっちなの？ >>44
それはRDBMSのことであって特定の製品を指しているわけでもない。 >>45
ワケ分からん事言ってないで必死でググって顔真っ赤にして感謝しろよw
今頃もう真っ赤っ赤かなw RDBMSはSQLの処理の方法にRDBMSに任せるのがRDBなんだよ。
どう処理するかの手続きを指定するのはするのはRDBではない。 とりあえず書き込む前に書こうまず読み直してとりあえず書こう 【BS11:エンターテイメント】 ＜関根勤 KADENの深い夜＞放送時間：毎週木曜日 よる11時00分〜11時30分 #bs11 http://www.bs11.jp/entertainment/5749/ ジョインで連結しまくったクエリに
ORDER BY で並び替えかけると
えらい遅くなるのですが
改善する方法はないのでしょうか？ ジョインしたかどうかとソートの速度に関係はない
ソートのキーとなる列にインデックスを張っておけばソートが不要になる場合がある
ソートキーが複数のテーブルに跨るとすると話は面倒臭い
VIEWにインデックスを張れるDBMSならそれで解決するのも手 >>53
インデックスの貼り方がわるいのか
リレーションに関するカラムを中心に
インデックスはっても遅いんです。
特に GROUP BY と ORDER BY の組みあわせ GROUP BYもあるならmaterialized viewにしてインデックス張るしかないかな
メモリをひたすら積んでメモリソートでごり押しという手もあるが テーブルレイアウトと実行してるSQL全部書けば
ある程度汎用的なインデックスのアドバイスができるかもしれんが
まあとりあえず実行計画確認しろ 【質問テンプレ】
・DBMS名とバージョン
この辺も書いておくといいぞ うちのダイニングのテーブルの配置はどう言ったらいいでしょうか MySQL 5.1.73
次のようなカラムの入ったメインテーブルがあるとします。
T1
｜MAIN_ID｜NAME｜AGE｜TITLE_1｜COMMENT_1｜TITLE_2｜COMMENT_2｜
で、TITLE と COMMENT の部分は
横持ちになってるのでその部分は別テーブルにして
T2
｜ID｜MAIN_ID｜TITLE｜COMMENT｜
として、縦持ちにしたいとします。
問題は、この２つのテーブルをどうリレーションさせるかです。
例えば 次のようなレコードが入っているものを次のようにリレーションしようとします。
T1
｜MAIN_ID｜NAME｜AGE｜
｜1 ｜田中 ｜24｜
と
T2
｜ID｜MAIN_ID｜TITLE｜COMMENT｜
｜1 ｜ 1｜好きな｜うな重 ｜
｜2 ｜ 1｜趣味 ｜バイク ｜
｜3 ｜ 1｜嫌いな｜しいたけ｜
｜4 ｜ 2｜好きな｜グラタン｜
が
FROM
T1
LEFT JOIN
T2
ON
T1.MAIN＿ID = T2.MAIN_ID
で関連付けられ
｜ID｜MAIN_ID｜NAME｜AGE｜TITLE｜COMMENT｜
｜1 ｜1 ｜田中 ｜24｜好きな｜うな重 ｜
｜1 ｜1 ｜田中 ｜24｜趣味 ｜バイク ｜
｜1 ｜1 ｜田中 ｜24｜嫌いな｜しいたけ｜
この例で行くと田中が3つになります。
また、 WHERE でTITLE、COMMENTが検索対象にできるようになります。
10件表示とか リストで出力すると この例では田中が３つでてきてしまうので
GROUP BY で ID をまとめます。
その際 ORDER BYをかけると 何千件とかになると
パフォーマンスが非常に落ちてしまいます。
※ ORDER BYがなければパフォーマンスはそれほど問題はありません。
パフォーマンスをなるべく落ちないように
縦持ちカラムを組み合わせるにはどうすればいいでしょうか？ それだとGROUP BYよりEXISTSのほうがよくね？
select T1のカラム
from T1
where exists (
select *
from T2
where T1.MAIN_ID = T2.MAIN_ID
and T2の条件
)
order by T1のカラム >62
>GROUP BY で ID をまとめます。
それだとIDと1:1に結び付かない項目は全て不定だぞ
つまり結局T1のみselectするのと同じになるわけだが
それならまずはT1のソート項目にインデックス張って見るとか
ああ、またMySqlか
SelectとGruop ByとOrder ByとWhereと全部書いたフルのSQL晒せ ない項目ってどういう意味だ？
インデックスは項目（の組み合わせ）に対して作るものだぞ 質問です。
学生メール表 学籍番号 氏名 メールアドレス
教員メール表 教員番号 氏名 メールアドレス
補習予定表 教員番号 授業id 日付 連絡事項
授業名表 授業iD 授業名
授業展開表 教員番号 授業id 学籍番号
これで生徒に知らせる時のER図をつくるとき、
いらない情報はどれですか？
学生メール表⇔授業中展開表⇔授業名表⇔補習予定表 >>67
必要最低限にしてもいいけど、実際にはいちいち結合しないと取得できないので重複して持つこともある。 ちなみに>>67はおかしいですか？
先生にしらせるときと生徒に知らせる時でER図を書きなさいって問題なんですが 問題に書いてあることを誤読や読み落とししている気がする。 まずER図書いてみろって話だが
エスパーすると授業展開表.教員番号か補習予定表.教員番号
各テーブルの主キーが不明なんでどっちにしろ正確な答えはだぜんぞ ・DBMS名とバージョン
SQLServer2014 ent.
・テーブルデータ
名前 月 欠席日数
a 1 1
a 3 1
b 1 1
・欲しい結果
名前 月 欠席日数
a 1 1
a 2 0
a 3 1
b 1 1
b 2 0
b 3 0
・説明
欠けてる月のデータを 0 補完したいと思います。
「名前」列がなければ例えば下のようなテーブルと外部結合することで解決できるのですが、
「名前」ごとに「月」と「欠席日数」を補完する方法が分かりません。
「名前」列を含めて保管用のテーブルを作ることも考えられるのですが、「名前」の数が多い場合に困ります。
月 欠席日数
1 0
2 0
3 0
お知恵をお貸し下さい。
お願いします。 名前テーブルも作ってそれと外部結合すりゃいいじゃん。 id,name
--------
1,aaa
2,bbb
3,ccc
これにdddを1の下に追加して
id,name
--------
1,aaa
4,ddd
2,bbb
3,ccc
という風に出来るデータベースってありませんか？ 👀
Rock54: Caution(BBR-MD5:0be15ced7fbdb9fdb4d0ce1929c1b82f) 表示をその順序にしたいと言うなら、
その順序指定になるカラムを追加したら？ >>76
> これにdddを1の下に追加して
できない
上とか下の概念がないから mysqlでやってみた
select id , name ,
case name when "aaa" then 1
when "bbb" then 3
when "ddd" then 2
when "ccc" then 4
end as newcol
from hogehoge
order by newcol;
+----+------+---------+
| id | name | newcol |
+----+------+---------+
| 1 | aaa | 1 |
| 4 | ddd | 2 |
| 2 | bbb | 3 |
| 3 | ccc | 4 |
+----+------+---------+
結局は>>77さんの通りにやってるだけだし、件数が多ければやってられんw select id , name ,
case name
when "aaa" then 1
when "ddd" then 2
else null
end as newcol
from hogehoge
order by newcol is null; >>74
普通は名前のマスタテーブル用意しとくんじゃね
まあ、なければないで効率とか考えないなら
select distinct 名前 from ...
とかで代用できなくもないけど
>>76
すくなくとも、RDBでそれは基本的な考え方から外れてるから無理
データ補完とか、順序付けされてないデータの順序とか、設計考え直せ >>75,81
ありがとう。
75 で言われてハッとして distinct で作った名前の一覧と月の一覧とを掛け合わせて、
これと元のテーブルデータを外部結合して行けました。 SQLと直接関係ないのですが
お名前.comSDサーバーの データベースって
sshで入って dumpコマンドでエクスポートできないので
バックアップを取ることができないのですが
なにかうまく取る方法ないですか？ >>83
お名前.com sdサーバー データベース バックアップ
でググったらすぐに見つかったが？ ○前提
会社id、名前や所在地などが入っている会社テーブルと、
会社id、従業員名、性別などが入っている従業員テーブルがあるとして、
（プログラム側で外部から受け取った）会社idから名前や所在地を得て、かつ、その会社の従業員を列挙表示したい
○質問
こういうとき、DB側、SQL側としては、会社テーブル・従業員テーブルそれぞれ1回ずつ2回SELECT発行するしかないでしょうか >>85
joinを知らないとか分からないとか、そういうレベル？
とりあえず>>1読んで出直して >>86
joinすると全レコードに>>85で言う会社テーブルの名前、所在地などが入ってきませんか
返してもらうデータ量よりクエリ発行回数を気にするべきなんでしょうか
また>>85には書いていないことですがjoin対象が複数あったら、など考えるとどう考えたら良いのか >join対象が複数あったら、
複数joinすれば？ 深く考える前にやってみればいいんじゃないかな、とか。 >>87
>データ量よりクエリ発行回数を気にするべきなんでしょうか
そんなもんはケースバイケースだからすきにしろ
もともとクエリ発行回数を問題にしたのはお前だろうが >>85
その前提なら
> 会社テーブル・従業員テーブルそれぞれ1回ずつ2回SELECT発行する
でいいと思う この場合それぞれ2回ずつSELECTが正解じゃね？ 従業員に付与される会社情報の多さが気になるなら2回でいいんじゃね
というか自分も2回だね >>85
〇足りない情報
どんなときに使う処理か（バッチ？それともユーザー操作に対する応答？）
出力はどのような形式か（画面表示？CSVファイル？）
データベースを配置しているサーバーと出力を得るクライアントの構成は？（例：PostgreSQLto
Apacheが同じサーバーにあり、クライアントはWEB表示で結果を得る。サーバーはAWSに配置しており、ネットワークの転送速度は毎秒１MBr程度が期待できる）
レコードは何件あるのか。レコードあたりの容量は？要求されるレスポンスは？（1秒以内に出力完了など）。 転送速度が極端に遅い場合はローカルにデータベースをもってジョインするのも選択肢としてあり得なくもないが（2層方式も含めて検討だろう）
それだったら出力結果を圧縮して転送だろうな
HTTPで出力する場合は例えばgzip圧縮すりゃ設定いじる程度の工数で済むしな 月額2000円くらいの予算でvps借りるとしてmysqlで最大どれくらいのトランザクションを捌けるもんなの？ どこで質問したらわからないんですが総合スレってありませんかね？ Mysql で 出力データーを
20.00 → 20
21.40 → 21.4
23.05 → 23.05
20.10 → 20.1
みたいに少数以下の語尾のゼロを取ることをMySQL内ですることはできないでしょうか？ truncとかfloorとか
文字ならsubstrみたいなやつで アドバイスをいただきたく
CFINFOテーブルのカラムが全てcharでvarcharに変更したい場合のSQLです。
数十件のデータではテストしてOKでしたが、大量のデータだと問題でそうですかね？
alter table CFINFO modify (
CF_GROUP VARCHAR2(10),
CF_NAME VARCHAR2(30),
CF_ID VARCHAR2(50),
CF_PSWD VARCHAR2(50)
)
/
update CFINFO d
set (d.CF_GROUP,d.CF_NAME,d.CF_ID,d.CF_PSWD)
= (
select trim(CF_GROUP),trim(CF_NAME),trim(CF_ID),trim(CF_PSWD)
from CFINFO m
where d.CF_NO = m.CF_NO
)
/ >>109
/があるあたりでOracleだと思うが、なんで並列処理化するとか考えないの？ というか、ふつうにhoge=trim(hoge)で良い気がするんだが
なんで自己結合する必要があるんだ それか元のテーブルをRENAMEして
新しいテーブルにINSERT SELECTしたら？
表領域足りんのか mysqlの質問です
[2016-11-21 18:20:12]のような[yyyy-mm-dd HH:mi:ss]の形の値を持つdatetime型のフィールド、recordがあるのですが、
ここから2016年10月1日〜2016年10月31日の期間の9:00〜12:00のレコードのみを抽出するにはどのような命令を書けば良いでしょうか？
日付のみなら
SELECT * FROM t_open WHERE record BETWEEN '2016-06-01' AND '2016-07-01';
という簡単な形で出せたのですが、時刻を指定する方法が分からず詰まっています。 >>113
and date_format(record, '%H:%i') between '09:00' and '12:00' >>114
できました！ありがとうございます！
date_format関数について勉強しておきます >>114
こう言うのがササっと書けるようになるには何年くらいのSQL修行が必要ですか? >>116
> こう言うのがササっと書けるようになるには何年くらいのSQL修行が必要ですか?
本読まないでしょ。
初心者でも入門書を2,3冊こなせば書けると思うよ。 短いやつをちょっとずつ書けば慣れるけど
MySQLの方言だからねえ、、、 確かにここのレスを見ただけだとササっと書いてるように思うのも仕方がないだろう
だけど……裏では必死でググってんだぜオレたち 👀
Rock54: Caution(BBR-MD5:8368d31ad5c810f9ab23ea9fefa156d2) >>117
入門書を二冊も読んだら上級者でしょ？
それもかなり上のクラスの sql初心者で2、3行程度の簡単なsqlしか実行した事がないんですが、世の中では何百行、何千行のsqlを実行する事もありますか？ 100行程度なら描いたことがある
メンテナンス性を考えると
あまり長くしないようがいいように思う >>122
相対的には上級者になるのかもねｗ
絶対的にはもちろん違うけど >>124
自分がかなり上のクラスの上級者であると自己判定していて、自分のクラスになるには入門書2冊が必要だった、ということかも 入門書を何冊読んでも入門書レベルの知識しかつかないという、ごくあたりまえの話 >>121
そんな複雑なやつを書く前にストアドとかビューを組み合わせるとかを検討する
そんなの書いたら検証がえらいことになる 聞いた話でそれを見たことはないけど、1000行くらいのが出来上がったとかなんとか。
見たくもない（ある証券系のシステムでのお話） 問い合わせ文じゃなくて、ややこしいストアド書くなら数百は余裕であり得るだろうけど 行が増えざるを得ない状況がストアド開発には多すぎるからなぁ
つまり複雑度はなぜ行が多いのか次第 ■ このスレッドは過去ログ倉庫に格納されています
