SQL質疑応答スレ 6問目

1 ：NAME IS NULL：2008/05/16(金) 21:21:17 ID:???

参考リンク
http://sql.main.jp/cont/sql/map.html
http://www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html
http://oraclesqlpuzzle.hp.infoseek.co.jp/
http://www.techscore.com/tech/sql/

前スレ：SQL質疑応答スレ 5問目
http://pc11.2ch.net/test/read.cgi/db/1193486961/

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

662 ：NAME IS NULL：2008/08/12(火) 13:46:08 ID:???

>>660
>>3

663 ：NAME IS NULL：2008/08/13(水) 23:52:20 ID:Eh6nC21k

テーブルの内容をソートして表示したい

●パターン１（分類＋日付）
ORDER BY BUNRUI, DATEで期待した表示できるので問題なし。

BUNRUI | DATE
---------------
A | 7/20
A | 7/21
A | 7/22
B | 7/10
B | 7/15
B | 7/20
C | 7/16
C | 7/17

●パターン２（日付＋分類）
下記のように分類をひとかたまりとした結果がほしいが、
ORDER BY DATE, BUNRUIではうまくいかない。
GROUP BY BUNRUI

BUNRUI | DATE
---------------
B | 7/10←一番小さい日付があるので分類Bの塊が先頭
B | 7/15
B | 7/20
C | 7/16←次に小さい日付があるので分類Cの塊が続く
C | 7/17
A | 7/20
A | 7/21
A | 7/22

664 ：NAME IS NULL：2008/08/14(木) 00:04:45 ID:???

>>663
SELECT *,(SELECT min(date) FROM Table WHERE bunrui=T1.bunrui) AS sort
FROM Table AS T1 ORDER BY sort,date;

665 ：NAME IS NULL：2008/08/14(木) 00:14:48 ID:???

あ、
SELECT * FROM Table AS T1
ORDER BY (SELECT min(date) FROM Table WHERE bunrui=T1.bunrui),date;
の方がよかったかな。
やってることは一緒なんだけど(^^)

666 ：NAME IS NULL：2008/08/14(木) 14:33:29 ID:8JYNX0Qf

>>664,665 サンクス。
で、試してみた。
が、DBはMySQLの古いやつ、バージョン3.23。
スカラー副問い合わせ、インラインビューがサポートされていない。
がーん。どおりで文法エラーになるわけだ。

別な方法はないだろうか。。。

667 ：NAME IS NULL：2008/08/14(木) 14:54:25 ID:???

>>666
こっちだとどぉ?
SELECT * FROM Table AS T0 JOIN
(SELECT bunrui,min(date) AS sort FROM Table GROUP BY bunrui) AS T1 USING (bunrui)
ORDER BY sort,date;

こっちもサブクエリを使うけど、Viewを定義しておいて置き換えるとか。
あと、USINGじゃなくてON(またはWHERE) T0.bunrui=T1.bunruiでも桶。

668 ：NAME IS NULL：2008/08/14(木) 15:48:58 ID:8JYNX0Qf

バージョン3.23は、CREATE VIEWも使えないみたい。
ワークテーブル使えばできるかな。

669 ：NAME IS NULL：2008/08/14(木) 15:58:25 ID:???

別な方法を探す努力を
バージョンアップする方に向けようぜ

670 ：NAME IS NULL：2008/08/14(木) 16:02:56 ID:8JYNX0Qf

そのとおりだ。
バージョンアップか。
もしくは仕様変更だ。

がんばるよ。

671 ：NAME IS NULL：2008/08/14(木) 17:10:01 ID:???

さすがにサブクエリが使えないと色々と面倒だから、4.1以上に上げる事を薦める

672 ：NAME IS NULL：2008/08/14(木) 22:14:17 ID:???

>671
別人だが、うちの上司はバージョンアップする気は全く無いようだ……

673 ：NAME IS NULL：2008/08/14(木) 23:12:40 ID:???

>>672
おまえの上司が馬鹿なのはデフォだから

674 ：NAME IS NULL：2008/08/15(金) 23:01:07 ID:???

テーブル格納されているレコード数を高速に取得したいと思っています。
select MAX(count) fromだと、1つ1つレコードを数えていくためか、
レコード数が多い場合は速度的にとても耐えられません。
そこで、テーブルにレコードの番号を示すフィールドを用意して、
各レコードに1から番号を振ることにしました。
こうすれば、最後の行の番号=レコード数となるから、
最後の行だけを取得するsqlを発行すればよいはずです。
このようなsqlはどう書いたらよいでしょうか。

675 ：どうせネタだろうけど：2008/08/15(金) 23:49:30 ID:???

> select MAX(count) fromだと、1つ1つレコードを数えていくためか、
> レコード数が多い場合は速度的にとても耐えられません。

具体的に DBMS の名前、バージョン、レコード件数とどれぐらいかかるのか書いてくれ。

> そこで、テーブルにレコードの番号を示すフィールドを用意して、

件数を保持する１フィールドで１レコードだけのテーブル用意して、挿入のたびにその
フィールドの件数を更新すればいいんじゃね。
シーケンスが使えるなら、その値を直接読めばいいし。

676 ：NAME IS NULL：2008/08/16(土) 01:10:19 ID:???

>>675
>件数を保持する１フィールドで１レコードだけのテーブル用意して、挿入のたびにその
>フィールドの件数を更新すればいいんじゃね。
凄くいいアイデアですね!
件数をファイルに保存しようかなとまでは思いついたのですが、
別テーブルに件数だけ格納するという発想はなかったです。
早速使います!

677 ：NAME IS NULL：2008/08/16(土) 03:46:05 ID:f7tcE3XO

SQL文に弱いので、教えて下さい

表Ａ
学年　クラス　学級委員ID　保険委員ID　飼育委員ID
1　　　A　　　001　　004　　006
1　　　B　　　024　　035　　012
1　　　C　　　064　　023　　002
2　　　A　　　046　　072　　010
2　　　B　　　008　　015　　029
2　　　C　　　049　　033　　060　　
・・・

表Ｂ
ID 　　　氏名
001　　　田中
002　　　鈴木
003　　　松下
004　　　山崎
005　　　岡本
006　　　西村
・・・

この上の表を使って下記の様に繋げたいのです

学年　クラス　学級委員ID　氏名　保険委員ID　氏名　飼育委員ID　氏名
1　　　A　　　001　　田中　　004　　山崎　　006　　西村
・・・

どのようなSQL文を書けば、上の表の様になるでしょうか
手掛かりを教えて頂きたく、宜しくお願いします
環境はWINDOWS XP/ORACLE10Gです

678 ：NAME IS NULL：2008/08/16(土) 04:12:13 ID:???

select a.grade, a.class, a.hoge, c.name a.hage, d.name, a.foo, e.name
left join b as c on a.hoge=c.id
left join b as d on a.hage=d.id
left join b as e on a.foo=e.id;

かなぁ？みかくにんあてずっぽ

679 ：NAME IS NULL：2008/08/16(土) 14:48:41 ID:???

column | name
------------
A | りんご
B | みかん
C | ぶどう

id | A | B | C
------------
001 | 100 | 200 | 300 |
002 | 110 | 220 | 330 |

上記のような2つのテーブルがあって、id=001を指定すると

りんご | 100
みかん | 200
ぶどう | 300

を得たいのですが、可能でしょうか？

680 ：NAME IS NULL：2008/08/16(土) 16:53:57 ID:???

ネットで見つけたのですが、これは解けるのでしょうか？
gnarl、技術メモ
http://d.hatena.ne.jp/gnarl/20080810/1218307817

681 ：NAME IS NULL：2008/08/16(土) 20:38:36 ID:???

>>680
MySQL 5.0.51aでやってみたが制限がきついなー。ちょっと微妙だが下を許してもらえれば・・・
・rownum出したくてユーザ変数を使った
・グルーピング指定してないカラムをselectしてもエラーが出ないMySQLの変な仕様を利用した
・あとどのカテゴリーにも属さないのは別に選択して無理やりUNIONした・・・

select
(select category_name from category where category_id=A.category_id) as category,
(select item_name from item where item_id=A.item_id) as item
from (select * from item_category order by category_id, rand()) as A
group by category_id, (@i:=@i+1) % 5
union all
select *
from (select 'NULL' as category, item_name as item from item
where not exists(select * from item_category where item_id=item.item_id)
order by rand() limit 5) AS B
order by 1,2;

682 ：NAME IS NULL：2008/08/16(土) 21:19:27 ID:???

>>679
> 質問テンプレ
> ・DBとバージョン

大抵の DBMS で可能だろうけど、２個目のテーブルは

id | column | Value
-------------------
001 | A　　　| 100
001 | B　　　| 200
001 | C　　　| 300
002 | A　　　| 110
002 | B　　　| 220
002 | C　　　| 330

のようにしたほうが色々楽だと思う。

683 ：679：2008/08/16(土) 22:47:59 ID:???

失礼しました＞＜
SQLServer2005になります。
よろしくお願いします。

684 ：677：2008/08/16(土) 23:03:21 ID:f7tcE3XO

>>678
どうもレスして下さってありがとうございます
ここでは試せないので、後日現場で試したいと思います

685 ：NAME IS NULL：2008/08/16(土) 23:38:26 ID:???

>>680
「ランダムに5件ずつ」というのは「毎回不規則に5件ずつ」と言う意味だろうか?
んでもそれってDBの仕事じゃないよなぁ。で、「どの5件かは不問」って意味で取ると、

SELECT category_name AS category,item_name AS item
FROM item AS T1 LEFT JOIN item_category AS T2 USING(item_id) LEFT JOIN category AS T3 USING(category_id)
WHERE 5 > CASE
WHEN category_id IS NOT NULL THEN (SELECT count(*) FROM item_category WHERE category_id=T2.category_id AND item_id<T1.item_id)
ELSE (SELECT COUNT(*) FROM item AS T4 WHERE item_id<T1.item_id AND NOT EXISTS (SELECT * FROM item_category WHERE item_id= T4.item_id))
END ORDER BY 1,2;

でいけそう。「毎回不規則に5件ずつ」なら、乱数を生成してitem_idのかわりにそちらで比較して5件か、
あぁでもサブクエリがあるから、一時テーブルに落とさないとダメそうなんでワンクエリじゃ無理か。

686 ：NAME IS NULL：2008/08/17(日) 15:38:33 ID:???

ランダムは後者の解釈で良いと思う。
指定しなければそうなるしね。

687 ：NAME IS NULL：2008/08/18(月) 01:02:13 ID:???

質問です。

id, a, b, c, ....
のようなテーブルαがあり、

select * from α order by a;

の結果の中からidがxからyまでの"間"のデータをorder by aの順番とおりに取り出せないでしょうか？

例：

select * from α order by a;
　　　↓
id a b c
----------------------
29 1900 xxx yyy
9 2000 xxx yyy
15 2100 xxx yyy
130 2200 xxx yyy
156 2300 xxx yyy
25 2400 xxx yyy
2 2500 xxx yyy

idがx=15, y=25のときこの結果の中の3番目から5番目を取り出したいです。

dbはsqliteです。よろしくお願いします。

688 ：NAME IS NULL：2008/08/18(月) 01:47:13 ID:???

>>687
SQLiteで動くかどうか不明。

SELECT * FROM α WHERE a
BETWEEN (SELECT min(a) FROM α WHERE id in (x,y))
AND (SELECT max(a) FROM α WHERE id in (x,y)) ORDER BY a;

689 ：NAME IS NULL：2008/08/18(月) 23:40:41 ID:???

>>687
この場合に動きません

id a b c
----------------------
15 1800 xxx yyy
29 1900 xxx yyy
9 2000 xxx yyy
15 2100 xxx yyy
130 2200 xxx yyy
156 2300 xxx yyy
25 2400 xxx yyy
2 2500 xxx yyy

690 ：NAME IS NULL：2008/08/18(月) 23:55:14 ID:???

>>689
idっていいながら重複を許すのかよ。

その場合は2100〜2400を取り出したいの？

id a b c
----------------------
15 1800 xxx yyy
25 1900 xxx yyy
15 2000 xxx yyy
25 2100 xxx yyy
の場合は何を取り出したいの？

691 ：NAME IS NULL：2008/08/18(月) 23:56:29 ID:???

ユニーク制約を使えば簡単なんだけど、ユニーク制約を使っちゃ駄目、っていうコーディング規約が急に出来てしまった、場合はどうしたらいいですか？会社を辞めるってのはナシで。

具体的には

Insert を発行して、値がユニークでなかったらエラーを返す、

というユニーク制約と同じことを、SQL できたら一文でやりたいんですが、出来ますか？

692 ：NAME IS NULL：2008/08/19(火) 00:10:39 ID:???

insert前にトリガで判断する。
MSSQLならINSTEAD OFトリガ

693 ：NAME IS NULL：2008/08/19(火) 00:13:16 ID:???

>>691
CHECK制約ならいいだろ？とか言っちゃうとかトリガー使うとか。

それも無理なら一般的なINSERT文じゃあ無理だろうね。
てかユニーク制約使っちゃダメっていうただでさえ不条理な規約に
一文でやらなきゃダメっていうさらに不条理な制約を付け加えてどうする。

694 ：NAME IS NULL：2008/08/19(火) 00:20:55 ID:???

あたらしい上司は、どうも SQL もその下のアプリ層もよく理解していないっぽい。コードに書かれてないことがどうもいやみたいです。自分も昔、他人の引き継ぎで、SQL が全部ストアドプロシージャに入ってて、発狂しそうになったことがあるから、文句は言いませんが。

>>692, 693

出て行くまえに、なんか SQL で嫌がらせをしたくて www 多分トリガもユニーク制約と同じ理由で却下だと思うので、ありがとう、でも今回は止めときます。素直に select 文でも書きます。ちなみに MySQL 5.0 でした。

695 ：693：2008/08/19(火) 00:31:43 ID:???

>>694
と、思ったけどCASE式とか使って
重複してたら型違いとかオーバーフローとかの値をINSERTするようにすれば
いけるような気がしてきた。

696 ：694：2008/08/19(火) 01:48:45 ID:???

>> 693

あ、なるほどね〜。多分今回は使わないけど、ちょっと考えてみるのも面白いかも。

697 ：NAME IS NULL：2008/08/19(火) 21:10:18 ID:???

MySQL 5.1.20-betaを使っています

table1
-----
ID(auto_increment) | DATA1
-----

table2
-----
ID(unique) | DATA2
-----

table1へのINSERTと同時にtable2に前者で作成した行のtable1.IDを使いINSERTしたいのですが、どうすればよいでしょうか？

とりあえず試してみたクエリを書いておきます
INSERT INTO table1(`DATA1`) VALUES('aaa');
INSERT INTO table2(`ID` ,`DATA2`) VALUES(table1.ID, 'bbb');

よろしくお願いします

698 ：NAME IS NULL：2008/08/19(火) 21:51:44 ID:???

T1
F1 F2
-------
01 aa
02 bb
03 cc

T2
F1
----
01
03

２つのテーブルT1とT2があります。
T1のフィールドF1に、T2のフィールドF1が存在するT1のレコードを削除するにはどうしたらよいでしょうか？

上の状態で、T1の01と03のレコードを削除したいのですが、、、、

699 ：687：2008/08/19(火) 22:09:52 ID:???

>>688-690
すみません、返事遅くなりました。

idが重複することはありません。
ただ、a,b,cは重複する可能性があります。

そして、688さんのコードをテストしょうとしたときに気づいたですが、
自分の質問と実際の問題が微妙に違ってました。
実際は
select * from α where ... order by a;
の結果はすでにあるtemporary tableのβに入ってあり、
そのときユニークなidxとyが与えられたときに、
その結果テーブルβの中のxからyの間にあるレコードを順番通りに取得しないと
いけなかったです。
β作る際、後で使うselect用fieldの追加は自由です。
なにかスマートな方法ありませんか？
ちなみにβを作るためのwhereとorderbyはかなり複雑です。

700 ：NAME IS NULL：2008/08/19(火) 22:10:04 ID:???

>>697
ストアドを使って、あとは DB で用意されてる方法を使う。
もしくは、Timestamp みたいな列を用意しておいて、Max で取るか。

>>698

Where F1 in (Select F1 From T2)

701 ：NAME IS NULL：2008/08/19(火) 22:41:22 ID:???

>>699
ROW_NUMBERか相関サブクエリでレコードに連番振ってしまえば大分簡単になるんじゃない。
あとはその連番の順序を頼りに範囲指定すればいいから。

702 ：697：2008/08/20(水) 01:40:51 ID:???

>>700
ストアドとやらを使ってみます
ありがとうございました

703 ：NAME IS NULL：2008/08/20(水) 13:33:25 ID:???

テーブルが空であるかを調べるには、select文の結果が0行であることを
確かめるしかないでしょうか。

704 ：NAME IS NULL：2008/08/20(水) 13:55:57 ID:???

COUNTじゃだめかい

705 ：NAME IS NULL：2008/08/20(水) 14:30:42 ID:???

>>704
countでokでした。
ありがとうございます。

706 ：NAME IS NULL：2008/08/20(水) 22:07:04 ID:???

>>697
insert into table2
select max(ID)
, 'DATA2'
from table1

>>698
delete T1
where exists(
select *
from T2
where T2.F1=T1.F1
)

707 ：NAME IS NULL：2008/08/21(木) 10:11:51 ID:MIDV4hwk

SQL injection 被害が色々と出ているようですが、www 経由で自分のサイトの injection 脆弱性を
攻撃して発見してくれるようなツールはあるのでしょうか。
単なる攻撃ツールとの見分けのために http://localhost:8080/ しかターゲットにできない、とかでもいいです。

708 ：NAME IS NULL：2008/08/21(木) 17:37:58 ID:???

>>699
βを作るときのorder byがややこしいならβに連番を振っておく
そうではないなら、>>688のようなやり方をβに対して行い、order byを自分でつける

709 ：NAME IS NULL：2008/08/21(木) 21:10:08 ID:???

>>701,708
ありがとうございます。
いろいろ調べてみましたが、sqliteはROW_NUMBER使えないようなので…、
ROW_NUMBERなしで連番はきびしいでしょうか？

710 ：NAME IS NULL：2008/08/21(木) 21:48:59 ID:???

select rowid from β;
で簡単にできました！

レスして頂いた方、ありがとうございました！

711 ：NAME IS NULL：2008/08/21(木) 22:18:59 ID:???

>>707
"sql injection スキャンツール" とか "セキュリティ スキャンツール" とかで
ググレば、フリーから商用までいやというほどある。