SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ >>534
CASE式、COALESCE、IFNULL, サブクエリ、デフォルト値有りの一時テーブルへINSERTとかかな
IFNULLを使いたくない理由が手間のみなら諦めたほうがいい
真っ当な理由があるならCASE式での代替を考えるといいのでは >>535
>>536
そうですか・・・
clumnの数がかなりあるので糞面倒ですが地道にやります
ありがとうございました ソース全部手で打ち込んでいるのか？
環境が分からないけど、
大概のエディタって置換機能あるだろう 単純な繰り返しならエクセルの計算式でsql文作ってコピペすれば楽チン すごくたくさんあるならSQLを生成するスクリプトを書くとか SELECT *, "default" AS [name] FROM foo
こんな感じでfooテーブルのレコードに任意の値をくっつけたものを出力可能
NOT EXISTSとかのサブクエリと組み合わせて、EXISTS側とUNIONで合体する
自分ならまずやらない方法ではあるが
単発のSQLじゃなくアプリケーションで使うSQLだとすると
DB設計かアプリケーション設計かどっちか考えなおしたほうがいいかもね CentOS6 + mysql 5.57
社のシステムなので、アップデートやインストール、ログ設定の変更ができない前提です。
まず前置きですが
既存のデータをDB化する一環で、大量（毎日80000行）くらいのinsert文を実行しなければなりません。
insert文自体は、既存のテキストデータから必要事項を抜き出して私が組み立てています。
元のデータはwindowsだったりワープロ専用機だったりですが、最終的に全てUTF-8に変換しています。
まぁこの処理自体はマクロ等駆使してやっているので問題がないのですが、元々のテキストに本来SQLで避けなければならないような半角記号とかが含まれている可能性がありますが、そこまでのチェックは物理的にできません（やっていません）。
一応、改行を\n、半角の引用符号は全角に、程度の処置はしています。
で、この大量のテキストをsource文で読み込んで処理するのですが、所々warning＊＊みたいな表示がでているのがわかります。
登録済み行数のチェックくらいはできますが、フィールドの中身が正しく登録されたかどうかまでのチェックはとても手が回りません。
そこで質問なんですが、SQLを実行する前にエラーになりそうな箇所を知る方法、あるいは、実際にエラーやwarningが出た行を知る方法（ツール、設定）がないでしょうか。 改行コードを入れてはいけないとか
引用符を入れてはいけないとか
そんな「俺ルール」はDBにとっては知ったこっちゃないので
まずはその「俺ルール」を正確に定義しないと何も始まらない >>543
エラーが出た行が分からない理由がよく分からない
自分ならINSERTじゃなくインポート(LOAD)使う
8万行くらいならよっぽどレコード長が長くない限りすぐ終わる
でDBからデータをエクスポートして元データと差分比較して検証する
検証が完了したらインポート先のテーブルから本番テーブルへデータ移行する
まあ100件くらいの少量でやり方を確立してから本当に必要な量でやったほうがいいよね あとSQLの質問じゃないしMySQLスレで聞いたほうがいいんじゃないの？ エラーハンドリングのやり方がわからないという意味なのかな？
それなら製品ごとにやり方違うからMySQLスレで聞いたほうがいいと思う >>543
> そこまでのチェックは物理的にできません（やっていません）。
やれよ...
チェックすべきなのは
https://dev.mysql.com/doc/refman/5.6/ja/string-literals.html
表 9.1 特殊文字エスケープシーケンス を参照
> 実際にエラーやwarningが出た行を知る方法（ツール、設定）がないでしょうか。
tee と warning コマンドで全部出力すればいいだけだろ
まあ何度もやるなら>>545の言うようにLOAD（もしくはそれをラップしたmysqlimportコマンド）を使うべきとは思う
https://dev.mysql.com/doc/refman/5.6/ja/mysqlimport.html >>543
＞チェックはとても手が回りません。
なんで？時間が無い、技術が無い？ もうSQLというより、IT技術者の初心者の質問だよなw >>551
宇宙飛行士は頭がいいというよりは、超絶バランス感覚がある人でないとできない。
頭が良すぎる人には向いてない。 自分は生まれつきもの凄く頭が悪いのですが、東京大学理学部数学科に入って数学を学びたいという目標があります。
生まれつきもの凄く頭が悪い人でも、人並み外れた努力を積み重ねれば、その目標を実現することはできると思いますか？
どうでしょうか？ >>553
東京大学に入ることは努力で可能なことです。そんなに難しいことではありません。
高校生までに東大に合格するような勉強方法や、強い意志がみなないだけです。 insert into >>553(atama)
values
select エッセンス from ドラゴン桜; MySQL5で1つのカラムに空白区切りの単語がはいっているとします。
テーブル構成は以下の通りです。
id｜word
.1｜バナナ
.2｜バナナ みかん
COUNTで集計したいのですが、
空白文字で区切って「バナナ:2」「みかん:1」のようにすることって出来ますか？
（空白区切りじゃなくてカンマ区切りでもいいです）
別に単語分割したテーブルを用意するのではなく、
1つのテーブルでカラム内の値を分割して集計する方法があれば教えてください 各DBでのやり方見たら分かると思うけど
そのケースをSQLで処理するためには
単語分割したテーブルを用意するのと同じような処理が必要 関数従属性の話って「意味論」でいいんだよね?
ある「カラムAから -> カラムB」の従属性があるかどうか
って現在のテーブルに格納されている値を見比べるだけじゃ
わからないよね？
もしカラムAとカラムBの値が全く同じ組み合わせで対応している
ように見えても、それが偶然これまではそうなだけなのかもしれないし、
「A -> B」が「値を見る限り従属している」のが分かったとしても、
それが「直接従属している」か「推移的な従属」なのかって、
値を見てもわからないよね？
カラムの意味を考えたり、値を格納している実装を見ないと
「どんな従属性なのか」ってわからないっすよね？ 今あるデータがすべてならばデータを見れば判断できる。
それ以外のこれから入れるデータがあるのであれば当然、今あるだけのデータからは判断できない。 意味論て言ってるのにデータを見ただけで分かるわけないだろ
頭が悪い奴だなｗ >>565
「意味論だよね？」って確認してるの。
関数従属性について図で例示されてる解説があるだろ？
でもこういうのみてもさっぱり理解できないから、よく考えたら
例示では理解しにくい概念なんじゃないかって思ったんだよ。
やはりそうなのか。 うん、関数従属性はデータからだけじゃわからないよ
既存のテーブル構造が関数従属性を100%表現できている場合は
データじゃなくその構造を見ることでどういう従属性があるか理解できるかもしれないが
現実には100%表現できてることなんてまずないからね 集合論を知っていればわかるように、集合自体が構造を表す。
つまり、テーブル内のデータが全体集合なのであればそこに存在する従属性はそれでわかる。
現実的には全体集合を得られていることなんてレアケースだろうから、そのときは
「(いま得られている)データからだけじゃわからない」ってことになるが。 >>568
言い訳が苦しすぎんだろw
{ A , 11 , 999 , タコス , 2017/08/13 }
{ B , 12 , 777 , ドンタコス , 2017/08/14 }
{ C , 13 , 555 , ポリンキー , 2017/08/15 }
{ D , 14 , 333 , ネオポリンキー , 2017/08/16 }
{ E , 15 , 111 , ポンキッキー , 2017/08/17 }
{ F , 16 , 333 , ドストエフスキー , 2017/08/18 }
これが全体集合だったとして、ここに存在する従属性がわかるなら教えてくれよ 3番目のカラムを除く組み合わせほぼ全てだろ？
Aならばタコスだし2017/08/13だし、ドンタコスならばBだし12だ。 質問スレで念のために自分の聞きたい質問がスレの主旨に合っているかを問うのだが
その問い自体が既にスレの主旨から外れているという矛盾を抱えた石橋叩きの人生
生きるのが大変そうですね BigQuery ってなんだ？
ってググったら Google のサービスか
何を聞きたいのか知らんけど普通に Google に聞けよ MYSQLで
まったく同じ構造の表三つを、
表１ 表２ 表３
１ null １
２ ２ null
３ ３ ３
null ４ null
５ null ５
nul ６ ６
のように結合したいのですが、どのようなSQLにすればよいのでしょうか
表は三つあるのですが、どこがデータ量が一番多いのかはわかりません 推測するに各テーブルがカラム1個で、
全データを列挙したいんじゃ？ つまり、こういうことか？
select 表１.カラム１,表２.カラム１,表３.カラム１
from
(select カラム１ from 表１
union
select カラム１ from 表２
union
select カラム１ from 表３) t
left join 表１ on 表１.カラム１= t.カラム１
left join 表２ on 表２.カラム１= t.カラム１
left join 表３ on 表３.カラム１= t.カラム１ SQL Server 2014 で教えてください。
１）
select * from tableA join tableB;
や
select * from tableA left join tableB;
と書いた場合、上は inner join、下は left outer join と同等でしょうか。
引き継いだコードが上のように書いてあるのですが、join で検索を掛けても inner join とかの記事しか出てこず。
２）
テーブルから特定の値のレコードを検索する場合、
a) 検索したい値を格納した(一時)テーブルと結合する
select * from tableA t1 inner join tableB t2 on t1.id = t2.id; など /* tableB に欲しいレコードの id を格納しているとします */
b) where で in を使用して検索したい値を列挙する
select * from tableA t1 where id in (/* 欲しいレコードの id を列挙 */)
などの方法があるように思います。
a と b の実行時間を計ると b の方が圧倒的に速いのですが、そういうものなのでしょうか。 >>581
ありがとう。
1) は動作結果からそう考えていたのですが、仕様的確認をとれて助かりました。
2) はそうですよね。SSMS の実行計画は見ていたのですが、そういう差があること以上のことは分かりませんでした。
教えていただいた方法でも調べてみます。 サブクエリでのORDER BYが、メインクエリでも有効なのかどうか、
ご存知の方教えていただけないでしょうか。
例えば、学生別点数テーブルから点数上位10名を取得したい場合、
SELECT
*,
ROWNUM as num
FROM (
SELECT *
FROM 学生別点数テーブル
ORDER BY 点数 DESC
)
WHERE num <= 10;
というソースだと、
サブクエリでは学生別点数テーブルが点数の降順でソートされるはずですが、
メインクエリでROWNUMが各レコードに通番(num)を振る際にもその並びが保持されているのか。
「サブクエリの並び順って、メインクエリでは保持されないんじゃなかったか？」と質問を受け、
回答に困っている状態です。
oracle 11gのリファレンスには、ROWNUMを利用した上記SQLで、点数上位10名が取得可能と読み取れます。
「ORDER BY句を副問合せに埋め込んでROWNUM条件をトップレベル問合せに置いた場合、行の順序付けの後でROWNUM条件を強制的に適用させることができます。たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。」
https://docs.oracle.com/cd/E16338_01/server.112/b56299/pseudocolumns009.htm
別の箇所で質問させてもらった際は、
「サブクエリーでソートされた結果を出力するだけ（joinやwhere条件など索引に関係するものが無ければ）なら、並びを変更される要因がないので、同じ結果となる」
との回答をもらっていますが、
もしよろしければこちらの有識者の方のご意見も頂けると幸いです。
よろしくお願い致します。 その回答した人は、ROWNUMがOracle固有の疑似列だということを知らなかったんじゃないかね。
ちゃんとそれがわかるように質問した？
あとOracleの場合でも、順序はROWNUMで得られるけれども出現順が保持されるとは言っていないと思う。 >>583
保証されないって思っておいた方がいいと思う
> たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。
これは
「小さい順に10個の従業員番号を持つ従業員」を戻します
であって
小さい順に「10個の従業員番号を持つ従業員」を戻します
じゃない
> 「サブクエリーでソートされた結果を出力するだけ（joinやwhere条件など索引に関係するものが無ければ）なら、並びを変更される要因がないので、同じ結果となる」
これOracleサポートの正式回答ならとりあえずは信じていいと思うけど、バージョン変わった時も保証されるのかを聞いておいた方がいいと思う
そもそも普通に
order by num asc
を追加しとけばいいだけじゃないの？ でたらめアドバイスだらけだが、OracleのFROM句の副問い合わせは、インラインビューと呼ばれ、並び替えも含めてインラインビューの結果そのものを、再度、検索する。
rownomをSELECT句に書いて、別名を付けるのは一般的ではない。 >>584,585
ご回答ありがとうございます。
別場所で質問した際は、oracle11gで、と前置きしていましたが、回答者の方がoracle固有の疑似列と知ってらっしゃったかは分かりません…
お二人に指摘頂いてるリファレンスの解釈については、確かにそのとおりですね。
公式の見解を聞きたいですが、サポート契約が無いのが辛いorz >>586の方もご指摘ありがとうございます。
確かに、リファレンスにもROWNUMにasで別名を付けるソースではないんですよね…
ただ、既に稼働しているシステムのソースがROWNUM別名形式でして、弄くる箇所は最小限にしたいなあ、と。 >>588
そもそもrownumをSELECT句に挙げる慣習はOracleにはない。
最近、SELECT句で選択していない項目は条件で使えないと思っていたベテランがいたがw 既に稼働しているシステムのソースをなるべく弄らない方針にすると
負債がどんどん貯まっていって見るのも嫌になるのでおすすめ >>583
外側のクエリでサブクエリの並び順を変更する要素がなければ、サブクエリの並び順のまま出力されるけど、それは現状の実装がそうなってるだけ
外側にもサブクエリと同じorder byを付けとけばオプティマイザが2回目のソートは不要って判断してくれるから心配なら付けといて損はない >>592
それはおかしい。
間違いは素直に認めろ。 インラインビューをサブクエリとわざわざ曖昧にしてるあたり分かっているとは思えない。 >>590
row_numberは記述が冗長になるのと、機能を拡張しすぎていてかえって分かりにくくなってしまう。 >>592
あなたはrownumはフェッチ時に番号が振られるのを理解してないのだと思う。
番号を付けるときにorder by rownumしても何の意味もない。 >>597
アウターにorder byがなくてもオラクルが順序を保証してくれると主張してるの？
最低限、何を主張したいのか分かるように書いてくれる？ >>596
Oracle方言よりANSI SQL使うに越したことはない >>598
rownumという列があるわけではない。
並び順はorder byを指定しないかぎりは保証しないというSQLの規格とは関係ない。
Oracleは内部的なことをSQLに書いているだけで、SQL ServerのTOP関数も内部では二度SELECTしていることが多い。 >>600
で？
保証してくれるの？してくれないの？ rownum絡みの仕様変更はずっとアナウンスされていない。 order byがなければ順序は保証しないとマニュアルに明記されているにも関わらず
rownum使ったtop-N最適化のケースは例外的に順序が保証されると言うならその根拠が必要だよね どういう思い込みなのか、rownumの変な使い方にこだわって、やってもかまわないが、変な人を貫きたいというので答えたのにな。
さらにオラクル社のいうこと、伝統、実装も突っぱねて、さらにオラクルでは、リソース食いのrow_numberを使用しようともしている。
どのRDBMSも標準化SQLが先にあって作られているわけではないから、標準SQLの方が性能も動きも読めないうえに、バグにあたる可能性がある。
さらに標準化SQLの構文を拡張してるから、どんどんわかりにくくなる。
なんで調べないのかな。
rownumは列じゃないんだよ。
インラインビューのorder byは意味があり、入れ子のrownum同士は別物。
オラクルではインラインビューのorder byは意味があり、インラインビューをSELECTするSQLではrownumをorder byをするのはかまわないが滑稽。
使い方は自由だからそう書きたいならそう書けばよい。 >>604
どういう初心者なのか知らないが、ソートしたものの中から、初めの何件かを取得するというSELECT文は、Oracle Databaseではインラインビューでソートしてrownumで絞り込む構文になっているだけ。
他のRDBMSでもOracleと同じものもあれば、ひとつのSELECT文で表現するものもある。
SQLの見た目で判断しているようでは、データベースをやめた方がいいよ。
どうしてソートしてないのに先頭何件かが取れるのか考えろよw 諦めろお前らはとっくに ID:oX0yCwR9 にマウント取られてんだよw >>604
Oracle Databaseは保証しないことは書いてあっても、保証するとは明言していないのが特徴。
こんな実績があって有名な話題で、さらにorder byを特殊なOracle構文にも摘要させたがるのは、世界初のリレーショナルデータベースのオラクルが憎くて、標準SQL側の攻撃のようだw 内部実装についていくら書いても
オラクルが順序を保証する根拠にならないよ
ただの無意味な長文 >>610
じゃ、保証してないんでしょ？
保証してんの？ >>586
一般的な書き方かどうかはどうでもいいけど
>並び替えも含めてインラインビューの結果そのものを、再度、検索する。
のソース教えてくれ
とくに、並び替えも含めてってとこな
これが正確で保障された動作なら、ORACLEにおいては保障されてるって話になるな >>613
なんねーよw
再度検索した後の結果セットの並び順が
その理屈でなんで保証されるんだよ Oracle初心者が調べもせずに教えろと騒ぐスレになったのか。 例の目的を達するにはこう書くと決まっているのに。rownumは値が固定のカラムではないと何度言ったらわかるのかw >>614
保障されないならrownumでトップnとか取れないって事になるぞ
あれは単に行のフェッチ順のはずだから
でも実際にはあちこちでORACLEのトップnのクエリの例としてあがってるからな
>>616
理屈も解らずにただこう書くと言われても納得できない方が普通じゃないかね だから保証されていることを保証しているのかとアホみたいに聞くからおかしなことになる。
オラクルはインラインビューにあとからorder byを導入して、レコードの並び順を固定している。
だからインラインビューをSELECTする場合にはorder byがいらない。
だいたいマニュアルでも書籍でもネット上でもさんざん説明されているのに、オラクルスレでもないここで聞いている行為がおかしい。 Oracleスレでやれよ
ここSQL全般スレなんだから方言とか実装の話なんかいらん >>617
top-Nを取得することと、取得した結果セットの並び順がどうなるかは別の問題だってのが分からない？ 下のが質問者が引用してたリファレンス原文だけど結果セットの並び順については何も言及されてない
the 10 smallest employee numbersがreturnされると書いてるだけ
For example, the following query returns the employees with the 10 smallest employee numbers.
This is sometimes referred to as top-N reporting:
https://docs.oracle.com/cloud/latest/db112/SQLRF/pseudocolumns009.htm#SQLRF00255 でもって>>593リンクの中に並び順が保証されてるわけじゃないと書いてある
so, while I cannot imagine
select rownum, x.* from (select ... order by ... ) x
not being sorted by rownum (by the order by), it is permitted to not be sorted.
... Why would it be permitted not to be sorted? ...
because there is no order by on the outer query. that is why.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1137689100346245972 >>619
保証されてるというソースがあればみんな納得するよ >>619
> だからインラインビューをSELECTする場合にはorder byがいらない。
> だいたいマニュアルでも書籍でもネット上でもさんざん説明されている
ネットや書籍はどうでもいいからどのマニュアルのどこに載ってるか示してくれ >>625
マニュアルは外国に丸投げしたせいか、劣化が激しく、間違いや変な説明が多いので、マニュアルが正ともかぎらないし、あえて言及してないことも多い。 https://docs.oracle.com/cd/E16338_01/server.112/b56299/pseudocolumns009.htm
「SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM < 11;
前述の例では、ROWNUM値はトップレベルのSELECT文の値です。これらの値は、副問合せ内のemployee_idによって行が順序付けられた後で生成されます。」 そのトップレベルのSELECTの結果の出力順が
インラインのORDER BYの順序と同一であることが
保証されてるかどうかって話じゃなかったの？知らんけど。 「ORDER BY句を副問合せに埋め込んでROWNUM条件をトップレベル問合せに置いた場合、行の順序付けの後でROWNUM条件を強制的に適用させることができます。
たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。
これは、上位N番のレポートと呼ばれることがあります。
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
前述の例では、ROWNUM値はトップレベルのSELECT文の値です。
これらの値は、副問合せ内のemployee_idによって行が順序付けられた後で生成されます。」 >>628
rownumは主問い合わせの列で、その値は副問い合わせの結果と書いてある。
これを根底からくつがえすなら、あらゆるシステムで問題になっている。
これは内部的にソートされるから結果的に並び順が同じたぐいの話ではない。 うんだからROWNUMの値がインラインのORDER BYに紐付いてるのはわかったよ
そのROWNUM < 11なトップレベルの問合せはROWNUM順になるのが保証されてるのか
ってのが上の人らの指摘なんじゃないの？
個人的には保証されてるかどうかはどうでもいいんだけどズレた内容でドヤ顔されるのはうざいし インラインビューのorder byは無視されているのではなくて、order byの結果セットを返す仕様で、rownumは結果セットのフェッチ順。
構文が気持ち悪くみえる初心者がいるのは理解できるが、SQL ServerのTOPの方がよほど気持ち悪い。
レコードに位置の概念があったり、位置の概念がないと思いながらもソートがいきなりできると思い込むITオンチと話すときりがない。 誰でも知ってることをドヤ顔で永遠と書いた挙句に最後は八つ当たりww >>631
それは構文がそう見えるだけだろうが。
rownumの値は副問い合わせの結果で、かつrownumは主問い合わせの取得順。
Oracleの構文になんでそんなに文句があるのか。
外国人はデタラメが多いから気をつけろよ。 ■ このスレッドは過去ログ倉庫に格納されています
