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/

**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で絞り込むって書いてたけど

**NAME IS NULL** · 2018/07/29(日) 09:18:12.72

ちょっと前のunionの話だけど
処理効率や保守性はおいといて、10個の条件のうち9個を満たすものをとるのにunion使ってどうやるの？

**NAME IS NULL** · 2018/07/29(日) 09:28:13.30

それを書いた本人の真意はわからんけど、

(1番目以外の9個を満たすクエリ)
union
(2番目以外の9個を満たすクエリ)
union
:
でできるはず。

**NAME IS NULL** · 2018/07/29(日) 09:43:51.54

>>485
それselectごとに条件9個ずつ書くの？

**NAME IS NULL** · 2018/07/29(日) 09:46:11.97

ごめん、保守性はおいといてって言ったの自分だね
たしかにそのやり方ならできるか

**NAME IS NULL** · 2018/07/29(日) 09:52:42.06

自分なら union 化して
where x.cond > 条件 group by id having count(*) > 件数
を副問合せにする

**NAME IS NULL** · 2018/07/29(日) 10:21:47.78

>>485
unionだけだと元は異なるレコードでも結果が同じだったら1レコードにされちゃう
かと言ってunion allだと複数の条件にヒットするレコードが複数出ちゃうよ

**NAME IS NULL** · 2018/07/29(日) 10:52:46.82

>unionだけだと元は異なるレコードでも結果が同じだったら1レコードにされちゃう

主キーが存在しないテーブルならともかく、適切なselectリストにすりゃいいだけ。

>かと言ってunion allだと複数の条件にヒットするレコードが複数出ちゃうよ

(1番目を満たさずそれ以外の9個を満たすクエリ)でおｋ。

**NAME IS NULL** · 2018/07/29(日) 14:29:08.48

なんでいまさら話をループさせてるんだ

**NAME IS NULL** · 2018/07/29(日) 19:49:38.36

>>491
ここに常にいるキチガイ

**NAME IS NULL** · 2018/08/21(火) 16:31:56.98

(プライマリではない) authorがaaaとbbbの最新のレコードをそれぞれ1件ずつ取得したいのですが
どのようにしたらいいでしょうか?　最新かどうかは updated_at で判定できます

SELECT * FROM news WHERE author IN ('aaa', 'bbb')

**NAME IS NULL** · 2018/08/21(火) 17:41:25.74

>>493
もう少し詳しく書いて欲しい
テーブル定義と、updated_atでどうやって判定するのかなど

**NAME IS NULL** · 2018/08/21(火) 18:04:34.79

>>3じゃないのか？

**NAME IS NULL** · 2018/08/21(火) 21:30:01.28

MySQLでREPLACE関数を使って複数行を置換するにはどうすればいいのでしょうか？

例えば「body」というカラムがあるとして以下の値が登録されているとします。
あいうえお
かきくけこ
さしすせそ

これをREPLACEして「さしすせそ」のみ残したい時、

UPDATE news SET body=REPLACE(body,"あいうえお\r\nかきくけこ\r\n","")

としたのですが、置換できません。

**NAME IS NULL** · 2018/08/21(火) 21:57:14.63

>>496
出来ると思うんだが、出来ないとしたら
bodyの文字列中改行コードが、
replace関数で指定したものと違っているのかも知れない。

**NAME IS NULL** · 2018/08/22(水) 01:37:20.21

>>497
\nにしても駄目でした。一行だけだと置換できるんですけどね・・・

**NAME IS NULL** · 2018/08/22(水) 01:59:39.45

>>498
select hex(body) from table名
とやって、
改行部分を16進数で見てみるとか

**NAME IS NULL** · 2018/08/22(水) 03:03:57.79

>>494-495
遅くなりました
>>3 のをいろいろ弄ってみて実現できました
よく見ず質問してしまいすいません。
ありがとうございましたm(_ _)m

**NAME IS NULL** · 2018/08/28(火) 06:20:52.88

板復帰(OK!:Gather .dat file OK:moving DAT 215 -> 193:Get subject.txt OK:Check subject.txt 215 -> 215:Overwrite OK)0.86, 0.75, 0.74
age subject:215 dat:193 rebuild OK!

**NAME IS NULL** · 2018/08/30(木) 23:59:48.61

すいません。DB2でフラッシュバッククエリのコマンドを実行したいのですが、
ググってもOracleのコマンドしか出てきません。
どなたかご教授お願いします。

**NAME IS NULL** · 2018/08/31(金) 05:07:54.46

>>502
IBM DB2 総合スレ2
https://mevius.5ch.net/test/read.cgi/db/1166153254/

**NAME IS NULL** · 2018/08/31(金) 14:35:48.08

そっちのスレは過疎ってますし、そもそも回答になってませんが・・・

**NAME IS NULL** · 2018/08/31(金) 14:57:35.99

ここは特定のDBMLの操作方法を質疑するスレじゃないので

**NAME IS NULL** · 2018/08/31(金) 18:08:53.87

DB2にフラッシュバッククエリなんかないでしょ
ぐぐるとOracleのLogMinerみたいな機能ですら
db2ReadLog APIを使って自分で実装しろと出てくる

**NAME IS NULL** · 2018/08/31(金) 22:40:03.31

アクセスでの開発なのですが
氏名,エラー内容.エラー回数
A,ダメポ,1
A,無理ぽ,2
B,もうだめ,1
C,むり,1

みたいなものをクエリで出して、行列を入れ替えて
　　1回目　2回目
A　ダメポ　無理ぽ
B　もうだめ
C　むり
みたいに出力をしようとしています。

これが2万レコードほどあり、処理が重たくて困っています。

select 氏名
MAX（エラー内容）
from (抽出クエリ）
group by 氏名

等で出力すると、処理が非常に重たく

select 氏名,
[1回目].エラー内容
[2回目.エラー内容
from (抽出クエリ where エラー回数=1) as 1回目
left join on （抽出クエリ where エラー回数=2) as 2回目

みたいにしても重たいのです
どなたか助けてください

**NAME IS NULL** · 2018/09/01(土) 16:24:39.15

>>507
エラー回数は最大２ではなくNなわけ？
かりにNだった場合SELECT句はどうするつもり？

**NAME IS NULL** · 2018/09/01(土) 18:48:49.07

エラー回数は有限で3回までです。
accessがtempテーブルも使えず、実体テーブルにselect into しても
それだけなのに、2万件で1時間近くかかってしまっています。
助けてください。。。今日そのことばかり考えながら家事をしていました。つらい

**NAME IS NULL** · 2018/09/01(土) 19:17:06.19

抽出クエリ自体を実テーブルにして氏名にインデック貼って

SELECT
氏名,
Max(IIf(エラー回数 = 1,エラー内容,Null)) as 1回目,
Max(IIf(エラー回数 = 2,エラー内容,Null)) as 2回目,
Max(IIf(エラー回数 = 3,エラー内容,Null)) as 3回目
from 抽出クエリ結果
group by 氏名
order by 氏名

とかかな

**NAME IS NULL** · 2018/09/01(土) 19:31:19.34

抽出クエリってやつは実テーブルじゃなくて何かの条件で抽出した状態のビューを言ってるわけ？
それとDBがアクセスってだけでSQL自体は外部ツール（C#とか）から実行するの？

**NAME IS NULL** · 2018/09/01(土) 19:32:05.22

適切なスレが見当たらないんで、ここで聞くんだけど
sqlから、ER図を生成する方法ってある？
できればoracleで。
イメージ的には、プログラミングで言うと
ソースコードからクラス図を生成するイメージ

**NAME IS NULL** · 2018/09/01(土) 19:35:46.49

>>510
ありがとうございます。
実テーブルに入れるというところで30分以上かかってしまい。
select だけだと1分程度で抜けるのが、insert selectでも select insertでも
30分かかってしまって、その後にクエリ流すのに時間が掛かってしまっています。

>>511
from 句の中に書いたサブクエリになります。
アクセスに対して、エクセルのVBA側から呼び出しをかけています。

**NAME IS NULL** · 2018/09/01(土) 19:39:12.73

>>512
SQLってDDL分ってこと？
実態テーブルからER作成するのは
有償ツールはいくつかあるけど
とりあえずA5とかで試して見たら？
https://a5m2.mmatsubara.com/help/ER/
FKとか指定してないとさすが無理だけど

**NAME IS NULL** · 2018/09/01(土) 19:59:08.40

>>514
DDLではなく、普通のselect分です。
DBにつながる環境で、FKとかはDBから拾ってきてほしい想定です。
実テーブルから、ER図ではなく、
実テーブルと、select分から、ER図を作ってほしい

**NAME IS NULL** · 2018/09/01(土) 19:59:37.04

select
抽出テーブル1.氏名,
抽出テーブル1.エラー内容 as エラー内容1,
(select エラー内容 from 抽出テーブル as 抽出テーブル2 where 抽出テーブル2.氏名=抽出テーブル1.氏名 and 抽出テーブル2.エラー回数=2) as エラー内容2,
(select エラー内容 from 抽出テーブル as 抽出テーブル3 where 抽出テーブル3.氏名=抽出テーブル1.氏名 and 抽出テーブル3.エラー回数=3) as エラー内容3
from 抽出テーブル as 抽出テーブル1
where 抽出テーブル1.エラー回数=1
order by 抽出テーブル1.氏名;

access2016のSQLビューで動いた奴

**NAME IS NULL** · 2018/09/01(土) 20:00:42.02

エラー内容ってカラムが実は1つのテーブルから複数もってくるけどはしょられたら破綻するけどね

**NAME IS NULL** · 2018/09/01(土) 20:13:28.57

>>516
これはデータ件数が多いと時間かかりそう
レコード1件ごとカラムのサブクエリが実行されるだろう

**NAME IS NULL** · 2018/09/01(土) 20:30:03.52

>>518
おれもそうおもったんだよね
抽出テーブルがサブクエリだとなおさらだな

2万件っていってるのは元データが2万なのか、
氏名で出力する行が2万件なのか（2万×最大3列＝6万）なのかで結果がかわるけど、
どっちなんだろな

**NAME IS NULL** · 2018/09/01(土) 20:58:07.07

>>516
ありがとうございます。今一番早く出来たのがその形で、それで1時間掛かっています。

抽出対象が2万件。
そのそれぞれに最大3列、エラーが発生し得る状態です。

サブクエリで抽出している下記のデータは、30万件ほどから抽出していて
氏名,エラー内容.エラー回数
A,ダメポ,1
A,無理ぽ,2
B,もうだめ,1
C,むり,1
抽出もとは最終的に100万件ほどまで増えます。

30万件から抜くクエリは1分程度なので、×3でいけると思っていたら1時間掛かるようになってしまい。

**NAME IS NULL** · 2018/09/01(土) 22:02:50.69

>>520
問題を切り分けたほうがいい気がしてきたけど、
①単純にデータを取得する場合の実行速度
select 氏名,エラー回数,エラー内容
from 抽出テーブル
order by 氏名,エラー回数
②エラー回数を列別に変換する場合の実行速度(>>510)を参考
select
氏名,
Max(IIf(エラー回数=1,エラー内容,Null)) as エラー内容1,
Max(IIf(エラー回数=2,エラー内容,Null)) as エラー内容2,
Max(IIf(エラー回数=3,エラー内容,Null)) as エラー内容3
from 抽出テーブル
group by 氏名
order by 氏名
これはどのくらいの実行時間なわけ？※Openして何もせずにループだけする

**NAME IS NULL** · 2018/09/01(土) 22:05:12.33

いまなんの話題？3行で

**NAME IS NULL** · 2018/09/01(土) 22:53:54.26

s
q
l

**NAME IS NULL** · 2018/09/01(土) 23:00:11.61

>>521
状況をきちんと伝えられていなくて申し訳ないです。

30万行の元データ→エラー回数抽出サブクエリ（①　件数は2万件程度抽出）→エラー回数を列別変換（②）
という流れです。

①は1分程度で1クエリは抽出完了、②は1時間掛かってしまっています
当初>>510のように作っていましたが、処理を見直し>>516のようなクエリに書き換えています。
①は分析クエリのように演算で回数を出しています。

**NAME IS NULL** · 2018/09/02(日) 03:03:08.10

>>524

>>510が正解じゃない？
インデックス効かせるには。
書き込み時間がかかるかもしれないけど。

最終結果の使い方わかんないけど
VBAとかで2万件結果セットループで変数持ちとかじゃだめか。

**NAME IS NULL** · 2018/09/02(日) 07:52:34.77

>>524
> ①は1分程度で1クエリは抽出完了
①の結果をアプリ側で整形するってのはできないの？
SQLは行列変換得意じゃないし

**NAME IS NULL** · 2018/09/02(日) 09:56:31.15

>>525-526
他のDB情報と最終的に結合して出力するためできればクエリでやりたいと思っていました。
ただ、どうしても時間が掛かるならVBA側で実装するのもありですね。

もう少し性能測定して、改善の見込みが無かったらVBAに乗り換えてみます。
諸々失礼しました。

**NAME IS NULL** · 2018/09/02(日) 17:58:27.97

>>515 って、無理ですかね。

**NAME IS NULL** · 2018/09/02(日) 18:52:28.60

実テーブルから定義を引ければ十分だと思うけど、なぜselect文が必要で
>>514じゃ何が足りないのか、質問の意図が誰も理解できないんだと思う。

**NAME IS NULL** · 2018/09/02(日) 22:22:42.98

>>514 に書かれてるようにFKないんじゃね？
join の結合条件見ろと

**NAME IS NULL** · 2018/09/03(月) 03:08:13.74

>>496
なんでreplace関数を使わなきゃいけないのか？

where句の条件があいうえお、かきくけこで SET句の値がさしすせそでいいだろ。

**NAME IS NULL** · 2018/09/03(月) 23:51:22.93

>>531
さしすせそは不定で指定できないとか。
最初の2行はヘッダーとか特定できるなにかなんじゃないかな。

>>496
ちなみにxampp5.6.12でテストしたら>>496のクエリでできたよ。

命令送ってる側とかで勝手に改行コード省かれちゃってるとか？

サーバーでログとってみたら？

**NAME IS NULL** · 2018/09/04(火) 13:40:14.68

>>532
MySQLと質問者は書いているのに、なんでXAMPPという何をどう使っているのかわからないくくりで答えているのか？