SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ よくある質問４
（問）
列の数が可変な問合せはどう書きますか？
（答）
標準SQLでは書けません。
pivotという機能を搭載したDBMSなら一見書けそうですが実はやっぱり書けません。
Oracle 11g以降でpivot xmlというキーワードを使用すれば一応可変っぽくはなります。
が、素直にプロシージャを書くかアプリケーションで処理したほうが良いでしょう。
SQL Serverのpivot（2005以降）
http://msdn.microsoft.com/ja-jp/library/ms177410.aspx
Oracleのpivot（11g以降）
http://download.oracle.com/docs/cd/E16338_01/server.112/b56299/statements_10002.htm#CHDCEJJE
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html ううむ、ブロックされてしまいました。どうしたらいいだろう。
Why have I been blocked?
This website is using a security service to protect itself from online attacks.
The action you just performed triggered the security solution.
There are several actions that could trigger this block including submitting a
certain word or phrase, a SQL command or malformed data. SQLソースの部分は外しておきます。
よくある質問５
（問）
年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい
例：201006を指定したら、以下の結果を得たい
20100601
20100602
・
・
・
20100630
（答）
SQLでは存在しないデータを生成することはできません。
この問いの場合は素直にカレンダーテーブルを用意しましょう。
どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
（PostgreSQLのgenerate_series()関数なら辛うじてセーフかもしれませんが
賛否の分かれるところでしょう。） 以上、テンプレ終わり
よくある質問５のSQLソース部分がアタックと受け止められてしまいました。
そのためソースは省略しました。 ここで募集するのも筋違いだとおもうけど、SQLの文を書いたのを訂正してほしい・・・
中級者には30分ほどでおわる内容かも。
謝礼は7000で、
多分、チョー簡単。
詳しくは
remorse2015@yahoo.co.jp
日曜までとりあえず募集します。
メールで内容確認だけでも良いです/ >>12
お願いできないですか？
ワードに見本表と完成表があってコード書かれているんですが、間違えてる文がある感じです。22ページあったんですが16から全然進まなくて、、、
とりあえず、メールお願いします。
ワードファイル添付しておくります。
支払いはすぐやります。 ワードファイル送りつけられても迷惑メールからのゴミ箱インよ SELECT *
FROM 氏名表 AS A
WHERE A.番号=
(SELECT 番号
FROM 成績表
WHERE 点数=479)
SELECT *FROM 氏名表 AS A
WHERE A.番号IN
(SELECT 番号
FROM 成績表
WHERE 点数>=500)
という間違った文があって
SELECT *
FROM 氏名表 AS A
INNER JOIN
(SELECT 番号
FROM 成績表
WHERE 点数=479) AS B
ON A.番号=B.番号
SELECT *
FROM 氏名表 AS A
INNER JOIN
(SELECT 番号
FROM 成績表
WHERE 点数>=500) AS B
ON A.番号=B.番号
を治すというものでこんなのを20個くらいやれば終わりです。 金もらって引き受けるほどの仕事じゃないようだし
そういう手間かけるくらいなら、ご自身で治した方が良いのでは？ > SELECT *FROM 氏名表 AS A
> WHERE A.番号IN
> (SELECT 番号
> FROM 成績表
> WHERE 点数>=500)
これは何が間違ってるんだ？ >>16
inをjoinに置き換えろって話のようにみえるけど
joinしたビュー作っとけばもっと楽かもしれんぞ
そもそもそんなことする必要があるのか
一度inとjoinで実行計画見といた方が良いんじゃね
>>18
文法的に間違ってるってなら、番号とINの間に空白がないとかじゃねw これについてはインラインビュー(FROM句の副問い合わせ)はやめた方がいいな。
WHERE句で絞った方がいい。 実行計画見るまでもないレベルのデータ量な気がするが。
何やっても数百ms程度で戻る気が。 >>21
その理由は？
単なる性能的な話なら、まず実行計画見るよって事だし
whereがどうこうじゃなくて、inの方だと、結果セットに点数含まれないのが問題なんじゃないのか
>>23
データ量は示されてないし、実際のテーブルレイアウトがどうなってるかもわからんし
まあ、性能的な問題じゃないと思うけど >>24
> データ量は示されてないし、実際のテーブルレイアウトがどうなってるかもわからんし
> まあ、性能的な問題じゃないと思うけど
氏名表：1,000レコード未満
成績表：100,000レコード未満
くらいかなと。
まあどんなクエリ書こうがたいしたことないと思うが、性能云々ならindexを貼るくらいでいいのでは。 答えたい気持ちは分かるがお前ら7000円をどうやって分けるつもりなの？
そういう事は最初にキッチリ決めとかないと後々遺恨を残すぜ 僕のために争ってくれてありがとう。
依頼する人は見つけたから大丈夫だよー
これで単位も一安心 >>24
理由も何もまずはSQLの普通の書き方しろってことだよ。 >>24
INリストには数の制限があるからな。
OR条件の羅列にすぎない。 ↓これ何？
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内ですることはできないでしょうか？ ■ このスレッドは過去ログ倉庫に格納されています
