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/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という何をどう使っているのかわからないくくりで答えているのか？

**NAME IS NULL** · 2018/09/04(火) 13:45:29.38

改行コードを含む文字列の置換を言っているのか。単に改行コードがSQLの改行コードとみなされるような環境で発行してねえだろうなw

**NAME IS NULL** · 2018/09/04(火) 20:40:30.85

質問者もＭｙSQLとしか書いてないので環境がよくわかんないから
とりあえずMySQL使ってればなんでもいいかとおもって。

**NAME IS NULL** · 2018/09/15(土) 02:10:48.09

個人からの申請で、登録、変更、削除を受け付けます。
その時、変更だけしか申請していない人を抽出するSQLはどう書いたらいいですか。

変更と登録、変更と削除など、他の申請を出した人は除いて変更だけの人を抽出したいです。

**NAME IS NULL** · 2018/09/15(土) 02:23:19.42

>>536
そのDBシステムで、
受け付けた登録、変更、削除をどう扱っているか次第

**NAME IS NULL** · 2018/09/15(土) 12:40:37.04

>>536
そのテーブルのカラム名すらも書けないんならSQLいじるの止めたほうが良い

**NAME IS NULL** · 2018/09/15(土) 13:28:08.11

>>536
個人Id | 申請
------+-----
001 | '登録'
002 | '登録'
001 | '変更'
002 | '削除'
003 | '変更'
で003だけ欲しくてSQL-Server使ってるとエスパー
select 個人Id from テーブル where 申請 = N'変更'
except
select 個人Id from テーブル where 申請 <> N'変更'

**NAME IS NULL** · 2018/09/15(土) 17:44:40.73

監査の話かと思った

**NAME IS NULL** · 2018/09/15(土) 18:18:49.96

>>539
「SQL Server」を「SQL-Server」と書きたい理由を教えてくれ。

**NAME IS NULL** · 2018/09/15(土) 20:31:26.68

【ちびまる子、乳ガン】　モー娘がヒキ逃げなら　　＜急に基準変更して逃走＞　　トー電はベク逃げかな
http://rosie.5ch.net/test/read.cgi/liveplus/1536979182/l50

関東の子供が、東電にベク逃げされて、棺桶が売れてる！

**NAME IS NULL** · 2018/09/15(土) 20:41:56.63

>>541
単なる好みだけどそれが何か？

**NAME IS NULL** · 2018/09/16(日) 06:31:37.33

製品名は変換する目的は何か？

**NAME IS NULL** · 2018/09/16(日) 08:31:26.77

お前みたいな日本語怪しいやつをあぶり出すため

**NAME IS NULL** · 2018/09/18(火) 15:27:23.99

固有名詞を改変したら駄目だろう

**NAME IS NULL** · 2018/09/18(火) 23:19:39.80

ええやろw

**NAME IS NULL** · 2018/09/19(水) 01:25:54.61

商標権を侵害してるな

**NAME IS NULL** · 2018/09/19(水) 11:22:26.10

半角スペース恐怖症なんだろうな。

**NAME IS NULL** · 2018/09/19(水) 19:30:55.56

誰でも間違いはある
気にするな

**NAME IS NULL** · 2018/09/19(水) 21:03:38.93

製品名を正しく書けないやつはたいてい危険人物。だいたいよくわかっていない。

**NAME IS NULL** · 2018/09/19(水) 23:26:57.94

なんやこの病的すぎるバカw

**NAME IS NULL** · 2018/09/20(木) 08:02:36.86

内容に突っ込めないから製品名に突っ込むしかないんだろ
どうでもええわ

**NAME IS NULL** · 2018/09/21(金) 14:45:01.05

お客相手に間違えなくて良かったじゃないか

**NAME IS NULL** · 2018/09/21(金) 15:39:47.79

ところで、IDが出てたり出てなかったりするのは何故？

**NAME IS NULL** · 2018/09/21(金) 21:51:00.56

>>554
そんなもんを気にしてるお客なんてお前の脳内にしかいないけどなww

**NAME IS NULL** · 2018/09/21(金) 21:52:53.93

>>555
sageかどうかでしょ
そもそもsageてない奴のレスは見る価値ないし

**NAME IS NULL** · 2018/09/21(金) 22:45:10.80

責任をとりたくない奴は、IDを隠すってこと

**NAME IS NULL** · 2018/09/21(金) 23:13:57.63

責任？ w

**NAME IS NULL** · 2018/09/21(金) 23:43:14.92

図星に脊髄反射しとる奴、おる？

**NAME IS NULL** · 2018/09/22(土) 07:06:16.16

図星に脊髄反射しとる奴 ⇒ >>558

**NAME IS NULL** · 2018/09/22(土) 13:24:41.08

>>557
いまどき上げ下げかw

**NAME IS NULL** · 2018/09/22(土) 19:23:47.16

sageるのって荒らしに見つからんようにとかそんな理由だったっけ？
今だと勢い順か新しい順、あるいはスレ検索して見るからもう完全に意味ないよな、わざわざ荒らすやつも激減してるし
ましてこんな過疎版じゃ気にするだけ無駄

**NAME IS NULL** · 2018/09/22(土) 20:12:24.86

質問スレだと、質問者は必ずageてって板もある
ID出た方が成りすましを防げるからやり取りで
混乱しなくて良いと言う側面もある

**NAME IS NULL** · 2018/09/23(日) 10:30:22.73

質問スレはそんなルールあったね
sageるとID出ないのはDB板の仕様か
技術的な話をするんだから一律IDつけたほうが勝手がいいと思うけどなあ
専ブラがデフォルトsageになってていちいち削除すんのもめんどいし

**NAME IS NULL** · 2018/09/29(土) 20:35:17.15

質問するのでIDありで・・・
リレーショナルデータベース＝JOINで複数のテーブルをまたいでクエリを投げられる

という意味？