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/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で複数のテーブルをまたいでクエリを投げられる

という意味？

**NAME IS NULL** · 2018/09/29(土) 20:51:07.76

リレーションってのは要はテーブルだ。
リレーションでできているデータベースだからリレーショナルデータベース。

**NAME IS NULL** · 2018/09/29(土) 21:24:57.55

レスありがとう。
リレーショナルではないデータベースというのは、テーブルでは無いデータベースが想像つかないな・・・。
二次元的なものではなく、テキストに羅列されているだけという感じだろうか。
昔pc-98で桐ver2とか名前だけは知っていたけど、あの頃はすでにテーブルという概念があったのかしら？

**NAME IS NULL** · 2018/09/29(土) 21:34:54.80

すごい会話だなw

**NAME IS NULL** · 2018/09/29(土) 21:36:14.75

>>568
自作自演？

**NAME IS NULL** · 2018/09/29(土) 21:38:54.14

>>568
桐もDBASEもリレーショナルデータベースだな
テーブルであるならそうかといえば、ちょっと違う

複数のテーブルを関係性で紐付けが出来るなら
リレーショナルデータベースと呼んで良いと思う

Excelの表はテーブルだけど誰もそうは呼ばないしな

**NAME IS NULL** · 2018/09/29(土) 22:03:17.34

情報処理試験とかデータベースの勉強をすれば初めの方で説明されているはずだけど、
リレーショナルの前には階層型やネットワーク型といったデータベースの形式があった。

ついでに言えば、リレーショナルデータベースには「複数のテーブル」などという要件はない。
複数のテーブルはその直積である1テーブルと等価なんで。

**NAME IS NULL** · 2018/09/29(土) 23:12:42.69

なんか変な素人が出てきたなw

**NAME IS NULL** · 2018/09/29(土) 23:27:01.69

普通はRDBMSと言えばリレーショナルデータモデルを表現できるだけでなく
ACID特性を備えるのが必要条件とされる
最近流行りのNoSQLなんかはリレーショナルデータモデルを表現するものではない上に
ACID特性のいずれかを満たさないのが普通だったりする

**NAME IS NULL** · 2018/09/29(土) 23:29:52.65

NoSQLが最近ものだと？

タイムマシンで過去から来た人間の書き込み？

**574** · 2018/09/30(日) 00:54:24.60

しまった
自ら正解を出すことはできず文句しか言わない人に絡まれてしまった

**NAME IS NULL** · 2018/09/30(日) 01:17:21.17

NGにすると吉

**NAME IS NULL** · 2018/09/30(日) 09:28:56.36

おまえらが知る前から俺は使ってるんだ（　･`ー･´） +

**NAME IS NULL** · 2018/09/30(日) 14:41:59.34

>>574
RDBとDBMSの区別がつかないなら、ややこしくなるだけだから黙っとけな

**NAME IS NULL** · 2018/09/30(日) 22:46:54.70

プライマリキーをフォーリンキーにすることはできますか？

**NAME IS NULL** · 2018/10/01(月) 00:24:20.26

できるが変な設計だなとは思う

**NAME IS NULL** · 2018/10/01(月) 12:38:28.99

>>581
時系列データのテーブルが2つあって
時間をフォーリンキーにしたいんですけどおかしいですか？

**NAME IS NULL** · 2018/10/01(月) 12:51:24.62

>>580
単に1対1のテーブルだからおかしくはない。

**NAME IS NULL** · 2018/10/10(水) 11:28:56.03

インデックスが使われるSQL文の書き方というのは、例えば出庫データに
商品コード順+出荷日順+伝票番号順というインデックスを作っておいて
SELECT * FROM 出庫データ WHERE 商品コード='A01' ORDER BY 商品コード, 出荷日, 伝票番号
みたいにするとレスポンスが速くなるということでしょうか？

**NAME IS NULL** · 2018/10/10(水) 21:59:19.97

>>584
プラス記号の意味がよくわからんが
CREATE INDEX(商品コード,出荷日,伝票番号)
という複合主キーを作成すると
例示のSQLは速くなるだろうが
ただしインデックスの順序と異なる
ORDER BY 出荷日,商品コード
などと場合は効かない

**NAME IS NULL** · 2018/10/11(木) 11:55:45.37

>>585
ありがとうございます

**NAME IS NULL** · 2018/10/11(木) 23:22:18.86

>>584
忠告しておくが、インデックスが使われるSQLを書くのではなくて、問い合わせの内容とインデックスが必要かどうかを考えてSQLを書く。

SQLがこうだから、このSQLはこのインデックスが使われるとSQLだけでは言いきれない。

**NAME IS NULL** · 2018/10/11(木) 23:32:44.06

>>586
複合主キーじゃないわ複合インデックスね

>>587の言うように
実際に効くかどうかは実行計画で決まるので
インデックスを使ったかどうかは
実際の実行計画を取得しないとわからない
オプティマイザが意図したインデックスを使ってくれない場合
ヒントで特定インデックス使用を強制することもDBMSによっては可能

**NAME IS NULL** · 2018/10/13(土) 19:43:23.58

https://imgur.com/FAF05na.jpg

**NAME IS NULL** · 2018/10/14(日) 17:09:16.28

常にサクラのサイトが応援しているノーザンコペルニクス
https://mevius.5ch.net/test/read.cgi/blog/1538500447/

**NAME IS NULL** · 2018/10/14(日) 20:22:51.35

　私たち日本人の、日本国憲法を改正しましょう。
『憲法改正國民投票法』、でググってみてください。
（へいわ）は、勝ち取るものです。拡散も含め、お願い致します。

**NAME IS NULL** · 2018/11/01(木) 06:26:01.46

質問です。
以下のようなテーブルがあったとします。
id|name
------
0|aaa
1|bbb
2|ccc

aaa,bbb,ddd,eeeを呼び出し側から与えて、nameにない項目(dddとeee)を取得したいです。
こういう場合どのように書けばよいでしょうか。
擬似的にaaa,bbb,ddd,eeeの項目をテーブルのように扱えればできそうですが、やり方がわかりません。
SQLiteを使っていますが、汎用的な書き方でできればしたいです。
よろしくおねがいします。

**NAME IS NULL** · 2018/11/01(木) 09:10:57.14

>>592
case when thenとかは？

**NAME IS NULL** · 2018/11/01(木) 11:56:15.87

汎用的には、テーブルを作ってしまうのが良いと思う

**NAME IS NULL** · 2018/11/01(木) 12:45:48.88

SELECT
a.empno<br />
,a.ename
,a.job
,b.ename AS 'mname'
FROM
emp a
,emp b
WHERE
a.mgr = b.empno;
このようなSQLを書いたのですが、SELECTのAS句が機能しません。どうすれば機能しますか？

**NAME IS NULL** · 2018/11/01(木) 13:00:22.93

'mname' の囲みいらない

**NAME IS NULL** · 2018/11/01(木) 14:17:52.92

意見ありがとうございました。
汎用的なのは諦めて、SQLiteでは以下で疑似的なテーブルができるようなので、これをもとにSQLを考えていこうと思います。
WITH t(name) AS (SELECT * FROM (VALUES ("aaa"), ("bbb"), ("ddd"))) SELECT * FROM "t";

**NAME IS NULL** · 2018/11/01(木) 19:59:14.73

>>595
行頭に,を書く悪習はいいかげんヤメロ

**NAME IS NULL** · 2018/11/01(木) 21:02:02.93

行頭に,を書くとどういう問題があるんだ？

**NAME IS NULL** · 2018/11/01(木) 21:32:48.31

>>599
ヴィジュアル的に認識しにくい

**NAME IS NULL** · 2018/11/01(木) 21:50:22.90

まさかそれだけ？

**NAME IS NULL** · 2018/11/02(金) 09:06:50.55

>>599
見た目にブサイク
（個人の感想です）

**NAME IS NULL** · 2018/11/02(金) 09:48:07.02

膨大なカラム数があって、スクリプトで処理しているときには都合が良いかもしれないが、
手作業で編集する分にはどっちでもいい気がする

**NAME IS NULL** · 2018/11/02(金) 12:32:46.28

コードは読むもんだと教わらんかったんかおまえら？
ヴィジュアル問題の大切さがいまいちわかっとらんな

**NAME IS NULL** · 2018/11/02(金) 13:13:36.22

前カンマの方が打ち忘れが減るので
自分はこちらのヴィジュアルが合っているらしい

**NAME IS NULL** · 2018/11/02(金) 19:10:39.71

それヴィジュアルちゃうやんw

**NAME IS NULL** · 2018/11/02(金) 19:39:46.15

前にカンマの方がコメントアウトする時に楽

**NAME IS NULL** · 2018/11/02(金) 22:38:08.71

だからヴィジュアルちゃうやんそれ
しかもどっちゃでも変わらんしw

**NAME IS NULL** · 2018/11/02(金) 22:53:44.45

カンマの忘れとか、余分なカンマとか、割とよくやるｗ