SQL質疑応答スレ 18問目

**NAME IS NULL** · 2018/01/04(木) 22:12:22.14

このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。

SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。

質問するときはDBMS名を必ず付記してください。

【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明

前スレ：
SQL質疑応答スレ 17問目
https://mevius.5ch.net/test/read.cgi/db/1468157341/

**383** · 2018/06/26(火) 16:58:48.22

FROM C WHERE Bid = t1.id) t3
の部分で、「t1はここから参照できない」みたいなエラーです。

**383** · 2018/06/26(火) 17:50:23.15

自己解決しました
(SELECT B.id, COUNT(col1) AS nullでない数 FROM C GROUP BY B.id) t3
として、WHERE句を取ればいいだけでした

無駄な検索時間は増えてしまうと思いますが・・・

**NAME IS NULL** · 2018/06/27(水) 01:56:23.14

>>385
カラム名を変えさせてもらった
tableB id, A_id
tableC B_id, col

select tableA.id, tableB.id,
count(C.col) as count
from tableA,tableB,
(select
B_id,
(case
when col1 is NULL then NULL
else 1 end) as col
from tableC) as C
where
tableA.id=tableB.A_id and
tableB.id=C.B_id and
C.c is not NULL
group by A_id,C.col;

**NAME IS NULL** · 2018/06/27(水) 02:05:38.77

訂正　最後から2行目

×　C.c is not NULL
○　C.col is not NULL

**NAME IS NULL** · 2018/06/27(水) 08:42:50.79

>>383
BCを先にjoinしてカウント。その結果とAをjoin

**NAME IS NULL** · 2018/06/27(水) 11:12:53.09

お願いします。
質問文を入力すると403 Forbiddenとなるのですが、なぜでしょう？
こういう文章は入力できるのに・・・

**NAME IS NULL** · 2018/06/27(水) 11:16:55.56

再度質問を投稿してみます。

ＣＲＥＡＴＥ　ＴＡＢＬＥ　ＴＢ（
ＭＭ　ＶＡＲＣＨＡＲ（８１）　ＤＥＦＡＵＬＴ　’ＮＯＴＨＩＮＧ’　ＮＯＴ　ＮＵＬＬ）

なのですが、DEFAULTで'NOTHING'が設定されていてNULLにならないと思うのですが
文末にNOT NULL制御を掛けているのはなぜなのでしょうか？

**NAME IS NULL** · 2018/06/27(水) 12:49:39.15

>>390
> NULLにならないと思うのですが
NULLは後からでも設定できるだろ

**NAME IS NULL** · 2018/06/28(木) 05:17:45.28

>>390
insertでカラムにnull指定すれば出来るからでは

**NAME IS NULL** · 2018/06/28(木) 09:25:57.73

>>391
>>392
ありがとうございました！言われると恥ずかしいくらい簡単なことだったのですね。先生方、ありがとうございました。

**NAME IS NULL** · 2018/07/02(月) 08:04:38.26

1時間に1回だけ引けるガチャを実装したいんだけどどうすればいいですか？
一応、ユーザがいつガチャを引いたかを記録するテーブルを作って（不正が分かるように必須）
そこから検索かけて該当するのがあれば残り時間を返すみたいな実装を考えてます

・MariaDB 10
・user_id, time
・残り時間（引ける時はいらない）
・

**NAME IS NULL** · 2018/07/02(月) 09:11:16.24

>>394
DB設計を語るスレ 10 [無断転載禁止]©2ch.net
https://mevius.5ch.net/test/read.cgi/db/1495438711/

**NAME IS NULL** · 2018/07/14(土) 15:21:18.46

条件1～条件10のうち9個以上の条件を満たすものを抜き出したい。

select * from table1 where 条件;

の条件のところに
(条件1 AND 条件2 AND ...AND 条件10)
AND
(条件1 AND 条件2 AND ... AND 条件9 AND (NOT 条件10))
AND
(条件1 AND 条件2 AND ... AND 条件8 AND (NOT条件9) AND 条件10)
AND
...
AND
((NOT 条件1) AND 条件2 AND ... AND 条件10)

と愚直に11通り書く他に、スマートに書く方法無いの？

**NAME IS NULL** · 2018/07/14(土) 15:48:00.68

where case when 条件1 then 1 else 0 end
　　　+ case when 条件2 then 1 else 0 end
　　　...
　　　+ case when 条件10 then 1 else 0 end
　　　>= 9

**NAME IS NULL** · 2018/07/14(土) 16:33:57.42

なるほど、CASE式を使うのか。サンQ

ってか質問のころ、11通りを結ぶのはANDじゃなくてORの間違いだったな。

**NAME IS NULL** · 2018/07/16(月) 18:50:59.75

>>398
複数のselect文を書いてunion allの方が意図がわかりやすいうえに、条件を変えるときも変更時のリスクも低い。

**NAME IS NULL** · 2018/07/16(月) 19:13:40.21

>>399
ちょっと具体的に書いてみ

まともな頭持ってたら書いてる途中で顔真っ赤になると思うが…

**NAME IS NULL** · 2018/07/16(月) 19:16:01.66

>>399
条件ちゃんと読んでないのかもしれんが、複数条件つなぐのはorだぞ
ちょっとunionで書いてみてくれよ

**NAME IS NULL** · 2018/07/16(月) 20:50:23.35

単純に、orでつないでる式を一つにしたsql文をunionすれば良いと思うのだが

**NAME IS NULL** · 2018/07/16(月) 21:32:24.07

>>402
それのどこが「スマート」なんだ？

**NAME IS NULL** · 2018/07/16(月) 21:39:54.23

質問者自身にしろ、CASE使った回答にしろ、
SELECT条件で9個以上の判定をしていますが、
UNION使って列挙した場合、それぞれのSELECTが
独立してテーブルをスキャンする事になるので
仮に上手く書けたとしても効率が悪いって気がします

**NAME IS NULL** · 2018/07/16(月) 22:51:06.23

>>404
それは素人の考え方です。先の例ではSQLの見た目はシンプルそうに見えますが、実際の処理コストは高いと思われます。

**NAME IS NULL** · 2018/07/16(月) 22:53:57.62

それなりにデータベースに詳しくてもいまだにSQLはこう書くと内部でこう処理されると主張する方がいますが、それはリレーショナルデータベースの根本から否定する主張です。SQLは処理方法を指定しない非構造化言語です。

**NAME IS NULL** · 2018/07/16(月) 23:01:38.78

>>404
リレーショナルデータベースはそんなにお馬鹿ではありません。SQLの発行回数を問題視したり、SELECT句が難度も出てきたり、見た目だけでコストが高い、性能が悪いと言われて仕事でもかなりの支障をきたしますのでそういった発言は控えてください。

まずは実行計画と実際にどのように処理されたかSQLをトレースして己の知識を高めてから偉そうなことを言ってください。

**NAME IS NULL** · 2018/07/16(月) 23:03:33.07

後の例を提示してもらった上で性能なり効率なりを評価してみましょう

**NAME IS NULL** · 2018/07/16(月) 23:07:17.64

>>408
すぐに回答を欲しがるいつものあなたですが、ここは教育スレッドではありません。

ちなみにさきほどの非構造化とは非構造化データのことではなく、構造化定義のない言語という意味です。

**NAME IS NULL** · 2018/07/16(月) 23:12:29.73

教えてもらうスレではなく、質疑応答スレです。

＞402 名前：NAME IS NULL[sage] 投稿日：2018/07/16(月) 20:50:23.35 ID:???
＞単純に、orでつないでる式を一つにしたsql文をunionすれば良いと思うのだが

この人がSQL書いて提示するのを待っているところですが

**NAME IS NULL** · 2018/07/16(月) 23:18:34.32

>>397 の良くない点を挙げましょう。

SELECTが一度しか出てきませんが、WHERE句の条件を見れば、普通のプログラマはループのネストとわかるはずです。

一番の問題はそこではなく、仕様の変更に弱く保守性が低いところです。

複数のSELECT文のUNION ALLは結果セットを最後に単純に繋ぐだけで、それぞれのSELECT文が独立していますので仕様の変更が全体に影響しません。

ただし本当のところはオプティマイザの判断ですので、必ずこうなるわけではありません。

**NAME IS NULL** · 2018/07/16(月) 23:27:47.58

評価は後の例が出てきてからで良いでしょう？

**NAME IS NULL** · 2018/07/16(月) 23:29:52.01

>>410
条件が10個もあり、そのうちの9個の条件が真の場合のSELECT文のパターンは膨大だと思うでしょう。

SELCT * FROM Aテーブル WHERE 条件パターン1
UNION ALL
SELCT * FROM Aテーブル WHERE 条件パターン2
UNION ALL
・
・
・

とかなり並べないといけないと思うと思います。

ただ冷静に考えてください。10個条件があり、すべての条件を満たすSELECTはひとつです。10個の条件のうち9個を満たすSELECT文は10個、10種類です。

たった11パターンのSELECT文のUNION ALLです。簡単でしたね。

**NAME IS NULL** · 2018/07/16(月) 23:38:47.97

このスレでは集合演算子がないことにしている回答ばかりです。おそらく集合演算子が使いこなせないのでしょう。

もっとシンプルに考えてください。プログラマとは思えません。

SQLを組み立てるときに頭の中でWHERE句内でどうにかしなければいけないと思い込んでいませんか？

常にSELECT文がひとつでSELECT句内でSELECT文を使う回答やWHERE句の服問い合わせでどうにかしようとする回答も多いですね。

特定の人物が回答しているのがよくわかります。その場しのぎの小手先のSQLほど迷惑なものはありません。

**NAME IS NULL** · 2018/07/16(月) 23:38:56.00

条件パターンを例に従って具体的に書いてみてくれませんか？
そうすれば実際に性能評価ができそうですし

**NAME IS NULL** · 2018/07/16(月) 23:43:50.42

>>415
なぜ11種類の条件違いのWHERE句違いのSELECT文が理解できないのですか？

SELECTの結果を足し算、くっつければすればいい話ですので、あなたがUNION ALLを理解できないだけです。

SQLの入門書の絵でも見て勉強してください。

**NAME IS NULL** · 2018/07/16(月) 23:45:50.92

この人NGにした方が良さそうですね？

**NAME IS NULL** · 2018/07/16(月) 23:52:58.66

UNION ALLでなくてもSELECTがひとつでないといけないルールがあるなら、WHERE句に10個の条件がすべて真、9個の条件がすべて真の条件をOR条件でつないでもかまいません。

**NAME IS NULL** · 2018/07/16(月) 23:58:36.32

条件1～条件10のうち9個以上の条件を満たすものを抜き出したい。

select * from table1 where 条件;

の条件のところに
(条件1 AND 条件2 AND ...AND 条件10)
AND
(条件1 AND 条件2 AND ... AND 条件9 AND (NOT 条件10))
AND
(条件1 AND 条件2 AND ... AND 条件8 AND (NOT条件9) AND 条件10)
AND
...
AND
((NOT 条件1) AND 条件2 AND ... AND 条件10)

と愚直に11通り書く他に、スマートに書く方法無いの？

↑は本人も訂正していますがORの間違いでANDと書いています。さらに本人は気づいていなかったようですが「 NOT 条件」部分はいりません。

質問者はスマートではないと言っていますが、本人がスマートではないと見た目だけで思っているだけで、まったく悪くはありません。

**NAME IS NULL** · 2018/07/17(火) 05:22:06.92

>>413
おれなら11個も同一テーブルからのunion allとか書いてたらアホかと思うけどな
whereで11個or条件書く方がマシだわ
どっちにしても、9個以上が８個以上とか７個以上とかになったらどうするんだろうね

ああ、たんに最近union覚えて使いたいだけか

理解できてないのはお前のunionに対する実行効率だよ

まあ>>397がループのネストとか俺には意味不明だから、俺を遙かに超越した理解の持ち主なのかもしれんが

**NAME IS NULL** · 2018/07/17(火) 06:35:01.38

環境はそれぞれだったら、いろんな方法を試したらいいんじゃない？
で、目的に一番合うもの選べば。

**NAME IS NULL** · 2018/07/17(火) 07:11:11.28

効率云々の前にunion allとかにしたら重複行の扱いで結果異なるし
>>417が正解かと

**NAME IS NULL** · 2018/07/17(火) 11:41:13.48

まあちゃんとnot条件書けばいちおうunionでもunion allでも同じになるんじゃね
unknownになるような条件書かなきゃな

**NAME IS NULL** · 2018/07/17(火) 19:05:25.28

>403
どっちがスマートかと言えば、unionで繋げた方が"sql文"としてはスマートかな

**NAME IS NULL** · 2018/07/17(火) 20:31:21.01

現実社会じゃクイズ解いてるわけじゃないんだから、アプリ側でやっちゃうだけ

**NAME IS NULL** · 2018/07/17(火) 20:40:16.91

>>425
この例で
アプリ側でどんなクエリ発行してどんなロジックでチェックするのか教えてくれ

**NAME IS NULL** · 2018/07/17(火) 21:16:08.13

>>424
だからSQL書いてみろって
書いてる間に顔真っ赤になるだろ w

**NAME IS NULL** · 2018/07/17(火) 21:20:42.11

お前は読めるのか?

**NAME IS NULL** · 2018/07/17(火) 22:10:19.21

unionおじさんの次の一手は誰も言ってない要件を持ち出す、かな

**NAME IS NULL** · 2018/07/17(火) 22:47:07.54

UNIONとUNION ALLはまったく別物だ。

**NAME IS NULL** · 2018/07/17(火) 22:50:39.33

>>422
だから質問者のWHERE句での条件でいいのに。
なんでSQLを直すことになったらすぐにWHERE句全体が崩壊する方法を勧めるひとがいるの？

**NAME IS NULL** · 2018/07/17(火) 22:57:06.79

>>422
確かにUNION ALLでは同じレコードが返ってくることには気づかなかった。

**NAME IS NULL** · 2018/07/17(火) 23:05:43.48

質問者が静的SQLにこだわっている理由も気になる。10個の条件のうち9個が真という条件が激しく変化するのなら、動的SQLでSELECT文を組み立てた方が、てっとり早い。

こういう条件の場合は動的にSQLを組み立てるのが普通で、静的SQLにこだわる理由がみつからない。

こういう検索機能を動的SQLではなく、静的SQLにしようとすると、例の仕様変更に耐えられないSQLを使用しなくてはいけないなる。

**NAME IS NULL** · 2018/07/17(火) 23:26:06.21

>>433
静的/動的は関係ないでしょ？
まあ動的に生成するなら>>396でもそんなに面倒じゃないけど>>397でなんの問題もないしな
ちな>>399は論外

**NAME IS NULL** · 2018/07/17(火) 23:31:51.56

素朴な疑問だけど、なぜみんなUNIONを嫌うの？

**NAME IS NULL** · 2018/07/18(水) 00:03:15.34

適切な使い方なら良いんだが、これは違うだろう

**NAME IS NULL** · 2018/07/18(水) 02:54:15.10

この例で最悪でもテーブルスキャン1回で済ます実行計画吐くならまあunion allでもいいんじゃね
結果行数が多くなるならunionはダメだろうけど

**NAME IS NULL** · 2018/07/18(水) 08:36:07.89

誰か最初のUNION(ALL)の手前までででいいからSQL書いてくれ

**NAME IS NULL** · 2018/07/18(水) 12:04:56.31

言うに事欠いて1回のスキャンで済むunionとか…

**NAME IS NULL** · 2018/07/19(木) 15:29:12.57

【3.11津波は自民由来!?　安倍逮捕秒読みか!?】　ロシア国防省『日本は地震を偽装した核実験を止めよ』
http://rosie.5ch.net/test/read.cgi/liveplus/1531966541/l50

２０１８年、テレビが隠している大ニュース！

**NAME IS NULL** · 2018/07/20(金) 00:47:16.08

ここのキチガイは実行計画もみたことがないからな。文字数が少ないSQLがスマートと言い張る。
なんでプログラマなのに

**NAME IS NULL** · 2018/07/20(金) 12:58:13.55

**NAME IS NULL** · 2018/07/20(金) 21:39:10.73

実行計画という用語を最近覚えたノロマ w

**NAME IS NULL** · 2018/07/20(金) 23:08:59.22

おまえの事やんけ

**NAME IS NULL** · 2018/07/20(金) 23:40:11.94

なにか気に触ったのか？ w

**NAME IS NULL** · 2018/07/21(土) 07:43:39.15

おまえ何時も他人の顔色うかがってばかりやな

**NAME IS NULL** · 2018/07/21(土) 08:27:53.48

ノロマは何をやっても駄目だな w

**NAME IS NULL** · 2018/07/21(土) 12:27:57.38

おまえの事やんけ

**NAME IS NULL** · 2018/07/21(土) 13:27:35.31

実行計画とか考慮したら、unionはないわｗ

**NAME IS NULL** · 2018/07/21(土) 15:38:38.03

>>448
>>444
いつものバカループ w

**NAME IS NULL** · 2018/07/21(土) 18:42:01.35

>>449
実行計画がなんなのか分かってますか？

多少の知ったかぶりはかまいませんが、少しは謙虚にわからないことはわからないと言うようにしましょう。

**NAME IS NULL** · 2018/07/21(土) 18:46:09.10

その実行計画をやってみたいから、早くUnion使ったSQLをここに晒してくれ

**NAME IS NULL** · 2018/07/21(土) 18:48:49.51

unionの1回目のselectで、キャッシュに乗る

**NAME IS NULL** · 2018/07/21(土) 18:50:04.62

それぞれのselectが異なるのに？

**NAME IS NULL** · 2018/07/21(土) 18:55:37.55

バッファキャッシュ

**NAME IS NULL** · 2018/07/21(土) 19:22:15.63

キャッシュに乗るかどうかは実行計画とは直接は無関係だがな
キャッシュにのったからって不要なテーブルスキャンが許容できるわけではない

**NAME IS NULL** · 2018/07/21(土) 20:00:11.00

でっかいテーブルだとキャッシュに収まるのは無理

**NAME IS NULL** · 2018/07/21(土) 20:17:41.08

条件によってはインデックスしかアクセスしない可能性もあるしな

**NAME IS NULL** · 2018/07/21(土) 21:13:40.08

不要なテーブルスキャン

**NAME IS NULL** · 2018/07/21(土) 21:14:00.83

>>451はアホやけどおまえらはその足下にも及ばんアホなんやで？
知らんかったやろ？

**NAME IS NULL** · 2018/07/22(日) 02:39:53.39

>>460
実行計画ではなくコストだったと間違いを認めなさい。

**NAME IS NULL** · 2018/07/22(日) 11:27:50.93

>>461
おまえが一番アホやなw

**NAME IS NULL** · 2018/07/22(日) 19:38:32.24

条件1～条件10のうち9個以上の条件を満たすものを抜き出したい。

テーブルは以下の内容とし、レコード件数は1万件、
cond*にはランダムに1～100までの数値を設定し、
抽出条件は各カラム値が50を超えるものとする

create table vote (
id int primary key auto_increment,
cond01 int,
cond02 int,
cond03 int,
cond04 int,
cond05 int,
cond06 int,
cond07 int,
cond08 int,
cond09 int,
cond10 int);

**NAME IS NULL** · 2018/07/22(日) 19:48:47.06

case when
https://ideone.com/ceErb8

union
https://ideone.com/9388vP

**NAME IS NULL** · 2018/07/22(日) 20:05:59.94

unionにだけorder byあるとか
やるならちゃんとやって

まあunionはたいがいの場合でソートするんだが
unionの押しの人はunion allって言ってなかったっけ？

どうせならインデックスの有無も

**NAME IS NULL** · 2018/07/22(日) 20:43:15.80

case whenはそのままid順になるけど、unionはならなかった
union all使えば重複の除外が発生するし、変わらないんじゃない？
index付けてもunion内のsql実行に影響するだけで、
マージする性能には多分影響しない

続きは各自で

**NAME IS NULL** · 2018/07/22(日) 21:05:02.26

>>466
ただのunion はdistinctで重複除去、除去しないのがall指定
この例でunion allだと重複行がでるはずだぞ

caseはID順（おそらく主キーか物理格納順）でテーブル舐めてるからそのままID順だな
unionで順番変わるってことは、重複除去の方法がソート系じゃない可能性が高い
まあなんにしても、order by書かないと順番は不定ってのが原則なので
両方に書くか両方に書かないか合わせないと

indexは、unionの各whereには効くだろうけど、caseにはうまく効かないんじゃないかと
まあそれで実行性能がひっくり返るとしても、unionで書こうとは思わんが

**NAME IS NULL** · 2018/07/22(日) 22:25:29.43

>>464
ごめん9は間違いで8以上だったSQL直してくれる？

**NAME IS NULL** · 2018/07/23(月) 07:46:29.52

sql自体でなくて申し訳ないが，前の例で、10億レコードで，テーブルが列ストアで保持されているとした場合、whereでー発フィルタリングするのと、unionするのとどっちが効率的なのだろう?
条件が適用されるカラムのメンバー数にもよりそうな気もするが、どんなもんなんでしょうか

**NAME IS NULL** · 2018/07/24(火) 19:21:58.65

やってみればいいのに。見た目がシンプルなのと処理の重さは反比例する。

**NAME IS NULL** · 2018/07/24(火) 21:25:13.19

見た目で決まるほど今のオプティマイザはアホじゃないし

**NAME IS NULL** · 2018/07/27(金) 12:11:49.90

質問です。

select a + b +c -d as 計算結果
where 計算結果 > 0

とすると行が表示されません。where句を外すと、
計算結果は0より大きい値でちゃんと出てきます。

where a + b +c -d > 0　

と書き換えると表示されたのですが、
where句でselectの as 別名は使えないのでしょうか？

**NAME IS NULL** · 2018/07/27(金) 13:33:09.67

一部つかえるDBMSもあるようだが原則使えない

**NAME IS NULL** · 2018/07/27(金) 18:03:08.40

普通はselect句でつけた別名を使えるのはorder by句だけだな

**472** · 2018/07/27(金) 18:21:36.74

そうだったんですね
できそうなイメージだったんですが
ありがとうございます

**NAME IS NULL** · 2018/07/27(金) 22:18:42.27

いい加減使えるようにしろよとは思う

**NAME IS NULL** · 2018/07/27(金) 22:54:00.11

select a + b +c -d as 計算結果
having 計算結果 > 0

**NAME IS NULL** · 2018/07/28(土) 13:12:09.25

>>475
よく考えてくれ。WHERE句が先に評価され、そのあとにSELECT句の選択リクトが評価される。

いったんSELECTした結果に対して、絞り込みのWHERE句つきSELECTを再度するのを基本としてしまうと、SELECT文は常にフルテーブルスキャンをして、その結果を一時的に保管して、保管した結果に対してさらに検索することになる。

**NAME IS NULL** · 2018/07/28(土) 13:13:33.29

>>471
だから見た目をシンプルにするのは意味がないと言っているのだが？

**NAME IS NULL** · 2018/07/28(土) 13:36:47.39

>>478
なんでそんな難しいことを考えるんだよ…
> select a + b +c -d as 計算結果
> where 計算結果 > 0
ってあったら
select a + b +c - d as 計算結果
where a + b +c - d > 0
って展開すれば良いだけだろ

**NAME IS NULL** · 2018/07/28(土) 14:05:45.09

>>479
>見た目がシンプルなのと処理の重さは反比例する
反比例ってどういうことだ？
基本的にはシンプルなSQLほどオプティマイザの裁量が広がって有利なんだが

**NAME IS NULL** · 2018/07/28(土) 15:08:24.77

>>478
この場合はどっちにしても全行計算してから絞り込むから大差ないような・・・

**NAME IS NULL** · 2018/07/28(土) 15:12:20.21

>>478
ああ一時テーブルのメモリと、捜査が２度手間になるのか
whereが先だったのか
どっかのサイトにselect結果をwhereで絞り込むって書いてたけど