SQL質疑応答スレ 8問目

SQL質疑応答スレ 8問目 at DB

1:NAME IS NULL
09/03/05 20:45:54
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。

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

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

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

前スレ：
SQL質疑応答スレ 7問目
ｽﾚﾘﾝｸ(db板)

2:NAME IS NULL
09/03/05 20:46:43
SQL言語リファレンス一覧
Oracle Database
URLﾘﾝｸ(otndnld.oracle.co.jp)
Microsoft SQL Server
URLﾘﾝｸ(msdn.microsoft.com)
IBM DB2 Database
URLﾘﾝｸ(publib.boulder.ibm.com) （目次から参照情報→SQL）
PostgreSQL
URLﾘﾝｸ(www.postgresql.jp)
MySQL
URLﾘﾝｸ(dev.mysql.com)

参考リンク
URLﾘﾝｸ(sql.main.jp)
URLﾘﾝｸ(www.atmarkit.co.jp)
URLﾘﾝｸ(oraclesqlpuzzle.hp.infoseek.co.jp)
URLﾘﾝｸ(www.techscore.com)

3:NAME IS NULL
09/03/05 20:50:44
過去スレ
SQL質疑応答スレ 6問目
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ 5問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ 4問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ 3問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ 2問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ Part 2
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ
ｽﾚﾘﾝｸ(db板)

4:NAME IS NULL
09/03/05 20:52:25
よくある質問１

（問）
ID | DATE　　　　 | DATA
--+----------+-----
1 | 2007-11-11 | aaa
2 | 2007-11-11 | bbb
1 | 2007-11-10 | ccc
3 | 2007-11-12 | ddd
3 | 2007-11-11 | eee
4 | 2007-11-10 | fff
1 | 2007-11-12 | ggg

このようなテーブルから、下記のように

1 | 2007-11-12 | ggg
3 | 2007-11-12 | ddd
2 | 2007-11-11 | bbb
4 | 2007-11-10 | fff

各idに対して最新の１件だけ抽出するSQLの書き方を教えてください。

（答）
select A.ID,
　　　 A.DATE,
　　　 A.DATA
from TableName A
　　 inner join
　　 (select ID, max(DATE) as MAX_DATE
　　　　from TableName
　　　　group by ID
　　 ) B
　　 on A.ID = B.ID
　　 and A.DATE = B.MAX_DATE
;

5:NAME IS NULL
09/03/05 20:53:22
よくある質問２

（問）
key　　　data
----------------
1　　　　　a
1　　　　　a
1　　　　　b
1　　　　　b
1　　　　　a
2　　　　　b
2　　　　　a
2　　　　　a

というテーブルから

key　　　a　　　b
--------------------
1　　　　3　　　2
2　　　　2　　　1

というExcelのピボットの様なデータを取得したいのですが、どういうSQLになりますか？
a,bというのは固定なので、仮にcというデータがあっても無視して構いません。

（答）
SELECT key,
　　　 SUM(CASE data WHEN 'a' THEN 1 END) AS a,
　　　 SUM(CASE data WHEN 'b' THEN 1 END) AS b
FROM table
GROUP BY key
ORDER BY key
;

6:NAME IS NULL
09/03/05 21:11:51
>>1乙＆>>2のリンク修正

SQL言語リファレンス一覧
Oracle Database
URLﾘﾝｸ(otndnld.oracle.co.jp)
Microsoft SQL Server
URLﾘﾝｸ(msdn.microsoft.com)
IBM DB2 Database
URLﾘﾝｸ(publib.boulder.ibm.com) （目次から参照情報→SQL）
PostgreSQL
URLﾘﾝｸ(www.postgresql.jp)
MySQL
URLﾘﾝｸ(dev.mysql.com)

参考リンク
URLﾘﾝｸ(sql.main.jp)
URLﾘﾝｸ(www.atmarkit.co.jp)
URLﾘﾝｸ(oraclesqlpuzzle.hp.infoseek.co.jp)
URLﾘﾝｸ(www.techscore.com)

7:NAME IS NULL
09/03/07 00:30:17 lFENR4eI
ちょっとSQLとは離れてしまうのですが、テーブルのなかにテーブルを作れる DBMS ってあるのでしょうか？

id | table | data
--+----------+-----
1 | sub_tb1 | aaa
2 | sub_tb2 | bbb

こんなかたちで、普通のデータとテーブルを混合して扱えるといいのですが、

8:NAME IS NULL
09/03/07 00:48:02
>>7
少なくともOracleではできるけど激しくお勧めしない
今すぐデータ構造を見直したほうがいい

9:NAME IS NULL
09/03/07 00:51:03
>>7
何故それをやらなければならないのか、それが問題だ。

10:NAME IS NULL
09/03/07 02:33:50 lFENR4eI
>>8 >>9

一つのプロジェクト情報を1レコードで管理しようと考えていまして、こんな感じのデータ入れようと思っています。
主キーはプロジェクトコードになります。

A工程作業 10人の氏名や単価などなど5カラムくらい。
B工程20人...
C工程30人...

となると、1レコードあたり300カラム以上になってしまい、また委託先がバラバラなので外注マスタみたいなものを
作るのが困難なため、こんな方法にしようかと、、、なにかいい方法はないでしょうか

11:NAME IS NULL
09/03/07 03:40:34
>>10
一つ確認なのですが、300カラム以上と言うのは、

(10人　x 5カラムぐらい) + (20人 + 5カラムぐらい) + (30人 + 5カラムぐらい)

という計算で良いのでしょうか？

12:NAME IS NULL
09/03/07 07:19:26
>>10
RDBMS を勉強し直せ

13:NAME IS NULL
09/03/07 10:40:24 lFENR4eI
>>11
はいそうなんです。こんな形で考えてみましたのですが、ありなのでしょうか？
|A名前1|A単価1|A期間1|A社名1|A備考1|・・・|A名前10|A単価10|A期間10|A社名10|A備考10|B名前1|・・・|B備考20|C名前1|・・・|C備考30|

>>12
なにか根本的に間違っているのでしょうか、、

14:NAME IS NULL
09/03/07 11:15:19
>>13
根本的に間違ってるからまずリレーショナルデータモデルを勉強したほうがいい

15:NAME IS NULL
09/03/07 11:40:31 lFENR4eI
>>14
なんか根本的に間違ってる気がしてきた、、項目が多い部分は、外部のテーブルにしろってことですかね。

□主テーブル
|プロジェクトコード|プロジェクト名|予算|工期|・・・・

□外部のテーブル
|プロジェクトコード|工程種別|No|名前|期間|備考|

こんな形にしてプロジェクトコードで結合。この場合、正規化する必要の無いテーブルを分割しているのですが、それはありですか？

16:NAME IS NULL
09/03/07 12:21:23
主キーとか正規化とか知ってる割には... 釣りとしか思えん。

釣りじゃないと言うなら、>>12, >>14 が言ってるように本屋に
行ってデータベース関連の本買ってきて勉強した方がいい。

17:NAME IS NULL
09/03/07 12:52:13
釣りじゃないですよ。もう少しちゃんと勉強しないとまずそうですね。
スレ汚しみたいになってしまってすみませんでした。本買ってきます。

18:NAME IS NULL
09/03/07 13:00:27
なんて偉そうなw

19:NAME IS NULL
09/03/07 14:15:18
純粋にデータベース理論に基づく議論として、>>13のスキーマがダメで
>>15のようなスキーマの方がよい理由って何でしょうか。

20:NAME IS NULL
09/03/07 14:24:28
ここは SQL なスレであって、テーブル設計のスレではないことに
そろそろ気づこうな。

21:NAME IS NULL
09/03/07 23:08:49 lFENR4eI
本から調べてみましたが、正規化するする必要がないテーブルを分割するのは設計上ありえない、もしくは仕方の無い場合のみ
しかしカラム数が膨大になる場合でも分割するのは、EDI規約では分割しては "ダメ" とのこと、、、

まあ結局 Oracle あたりで調べてみるしかなさそうですね

どうでも良いけど、「リレーションをつかえば．．」「そういう設計はない」「もう少し調べてろ」とかうちのアホ上司と
答えが同じで面白かったです。で、困ると「俺に(ここ)で聞くな」的なw　知らなければ知らないでいいですよ。無理しないでね。

22:NAME IS NULL
09/03/07 23:13:25
何をもって「正規化する必要がない」なのか、俺には全然理解できん。
普通に正規化する必要ありそうに見えるけどな。

23:NAME IS NULL
09/03/07 23:16:39
>>21
正規化について考えたのであれば>>10のデータに関して
関数従属性(FD)を見当したはずですが、どのようなFDを
列挙したか書いてもらえますか？

24:NAME IS NULL
09/03/07 23:22:06
ACCESSだけど、>>13と同じ構造でテーブル設計されてるDBを見たことがある。
もうなんていうか、え…っ？何を見たんだろう…？って感じ。
でもさすがに300カラムはなかった。255カラムだったｗ

>>19
人の入れ代わりが繰り返されたら、そのテーブル構造だとどうなるかをまず考えてみよう。
そうして、正規化について基礎から勉強しなおそう。

25:NAME IS NULL
09/03/07 23:24:54
スレ違いなのに、逆ギレを装いつつ煽ることで答えを引き出そうとするテクニックですね。
いい加減、スレ違いなお馬鹿さんにつきあうのはやめにしましょう。

26:NAME IS NULL
09/03/07 23:29:09
>>24
いや、なので純粋に「関係データベースモデル的に」ですよ。ネタです。
とりあえず部分従属も推移的従属も多値従属もないですよ？

27:NAME IS NULL
09/03/07 23:37:48 lFENR4eI
>>23
なんかスレ違いを続けてすみません。

プロジェクトA の作業A のスタッフ1 はプロジェクトコードAにのみ従属するので断念しました。スキル不足かもしれません。
また、正規化の基礎が分かっている方なら、それが出来ないくらい分かりそうなものだと思いますが、、

データベース関連の本買ってきて勉強した方がいい。ですよw

28:NAME IS NULL
09/03/07 23:43:51
カタカナ言葉で逃げないで、ちゃんと読めって言われた本やマニュアル読みなよ。
自己満足で変な物作るなよ。

29:NAME IS NULL
09/03/07 23:45:37
>>27
失礼しました。関数従属性も見当しないで「正規化すると・・・」なんて
文句言う人も少なくないので一応ちゃんと見当したか確認したかった
のです。見当したという事で、了解しました。

もう一つ確認なのですが、各プロジェクトのスタッフの数は10人・20人・
30人で固定でしょうか。
欠員や人数割れは発生しませんか？あるいはこれらの人数は単に
上限を示すものでしょうか。

30:NAME IS NULL
09/03/08 00:01:01
データーベースってさ、
「作る前に」
いっしょうけんめい、考えないといけないよね。

31:NAME IS NULL
09/03/08 00:03:27
>>24
> ACCESSだけど、>>13と同じ構造でテーブル設計されてるDBを見たことがある。
> でもさすがに300カラムはなかった。255カラムだったｗ

Excel の概念から脱却できなかったんだろうな。
まあ、Excel 2003 まででよかったよ、Excel 2007 使いだと、カラム数 1,000
とか平気で作りそうだし。(w

32:NAME IS NULL
09/03/08 00:06:45
>>26
ヒント：第一正規形

33:NAME IS NULL
09/03/08 00:16:05 OlHguXyr
>>32
>>13のリレーションは第一正規形のようですが。

34:7
09/03/08 00:19:08 vyix2FVv
>>29
こちらそ失礼をすみません。スタッフの数は極端な話 0人(全てお任せで社名のみ)～100人近くになる可能性もあります。

直接プロジェクト管理する場合は、全てのスタッフ情報を登録する必要があり、外注先が子会社、別会社、個人とバラバラなので
マスタを作るとなるとショットで頼んだ個人まで登録しなくてはならないので、>>13 みたいな形を考えました。

ただ想定している上限100名×5 = 500カラムはちょっと無理そうなので、テーブルのなかにテーブルがつくれるのがいいのかな？と考えました。

35:NAME IS NULL
09/03/08 00:29:25 OlHguXyr
>>34
了解しました。実は以下の回答はあらかじめ書いてあったものです。
無駄にならなくて良かったｗ

まず私の意見としては「正規化すれ」云々のコメントは全く見当違いです。
例としてあげられた>>13のスキーマは第五正規形まで見事に満たしています。
これ以上正規化しようがありません。更新時異常も起こりようがありません。

失敗はリレーションの正規化以前の、データ設計にあります。
例としてプロジェクトの「作業Aのスタッフ1の名前」を挙げます。
>>13のスキーマではこれは次の関数従属性で表現されています。

プロジェクトID -> 作業Aのスタッフ1の名前

これに対して、ベターな表現は次のようなものです。

プロジェクトID, 作業A, スタッフ1 -> 名前

何故これがベターかは左辺の作業名とスタッフ番号を具体値から属性名
に置きかえると判ります。

プロジェクトID, 作業名, スタッフ番号 -> 名前

幸いこれは多値ではない関数従属です。ですので綺麗に第一正規形の
リレーションで表現出来ます。試しに元の表現でもやってみます。

プロジェクトID -> 作業名, スタッフ番号, 名前

これは多値関数です。ですのでナイーブに実装しようとすると思わず入れ子
リレーションを発想してしまうかも知れません。>>7のような質問が出てきた
理由を無理矢理理屈立てて推測すると、多分こんなところです。

いずれにしても>>15のスキーマは>>13のスキーマの正規化を崩している
のではなく、元より異なるデータ構造を実装したものなのです。
なので安心して>>15のアプローチでデータベースを作ると良いと思います。

36:NAME IS NULL
09/03/08 00:29:41
>>31
Oracleならありそうで怖いｗ
Accessは幸いなことに…いや全然幸いでも何でもないけど、1テーブル255カラムが最大。

>>26
>>13のテーブル構造で、それぞれのプロジェクトにかかるコストを算出することを考えてみて。
工程ごとの小計もそれに加えたりなんかすれば…、死ねるｗ
DB構築する意味ないじゃん。

37:NAME IS NULL
09/03/08 00:40:45
なんだ～、さっぱり理解できない…
最後の1文だけ同意するｗ

38:NAME IS NULL
09/03/08 01:02:50
正規化できない暴威は結局，答えが欲しいんでしょ？
素直になれば良いのに。
「教えてください」って言えない子は，自分勝手に設計して
後で理解できれば本人のためにも良いと思う。
スレチってことも自覚してるようだし。
Access弄ってる気の利いた素人でもそんな設計しないよｗ

39:7
09/03/08 01:24:03 vyix2FVv
>>35
>失敗はリレーションの正規化以前の、データ設計にあります。
>元より異なるデータ構造

このあたりを考えていなかったため、おはずかしい考えをさらしてしまいました、、、
別テーブルを、異なるデータ構造を扱うためのものとしてとらえてみます。

ベテランの方だと思うのですが、失礼すみませんでした。

40:NAME IS NULL
09/03/08 01:34:46
>>37
要約すると、次のような感じ。

(1) >>13のスキーマは正規形
(2) >>15のスキーマは>>13のとは異なるデータ構造を実装したもの
(3) なので>>15のスキーマは正規化崩しではない。安心してOK。
(4) 正規化正規化言っている人は、ちゃんと理解して言っていますか？

(4)が屁理屈っぽく書いた理由です。
ただ正規化正規化言っているのも、ちょっと無責任だなと思ったので。

余興ついでにもちっと数学的に屁理屈をつけると>>13のスキーマに
含まれる関数従属は>>15のスキーマで表現されている関数従属

プロジェクトID, 作業名, スタッフ番号 -> 名前

の左辺にある作業名・スタッフ番号という属性に対してカリー化という
操作を適用し、出来た関数にこの２属性の具体値を適用する事で、
この一つの関数従属を複数の関数従属へと分割したものです。
数学的も何ら問題ない操作ですし、データモデリングの際には逆カリー
化と共に広く行われている操作です。

問題はこの操作によって出てくる関数従属の数や種類は適用された
２属性(作業名・スタッフ番号)の具体値(またはドメイン)に依存します。
例えばスタッフ番号の数が変わると、異なる数の関数従属が出ます。

他方でデータベーススキーマの一つの目的はデータ中の関数従属を
十分に表現する事ですから、関数従属の数や種類が変わるとそれに
応じてスキーマも変更しなければなりません。

スキーマ＝時間不変な構造、が、スタッフ番号の数などでコロコロ
変化されては困ります。屁理屈を言えば>>29の質問の意図はこの点
を確認するものでした。

ところが実際はスタッフの数はどうも曖昧っぽかったので、理屈的にも
ベターなのはこのカリー化を適用していない>>15のスキーマです。

41:NAME IS NULL
09/03/08 02:10:38
>>40
詳しい説明をどうもです。
でもカリー化とやらをググってたら脳ミソ逃避し始めた…ｗ

42:NAME IS NULL
09/03/08 02:33:51
>>41
ごめんなさい。ググるほどの概念でも、難しい操作でもありません。
手順を書くと次のような操作です。

(元の関数従属)
プロジェクトID, 作業名, スタッフ番号 -> 名前

(カリー化)
作業名, スタッフ番号 -> (プロジェクトID -> 名前)

これだけです。右辺も関数になっているのがミソです。

(左辺に具体値を適用)
作業A, スタッフ1 -> (プロジェクトID -> 名前)
作業A, スタッフ2 -> (プロジェクトID -> 名前)
...
作業B, スタッフ1 -> (プロジェクトID -> 名前)
作業B, スタッフ2 -> (プロジェクトID -> 名前)
...

あとはそれぞれの左辺の具体値を新たな属性名として利用して、
右辺の関数従属を

「プロジェクトID -> 作業Aスタッフ1の名前」

のようにスキーマ内で実装すれば>>13のスキーマが出来ます。

ただこれらは「屁理屈」ですからね。理解としては楽しいですが
あまり世間的に役に立つとは思いません。

43:NAME IS NULL
09/03/08 10:08:10
俺の意見としては >>13 は「正規化できてない」だな。

単純に「作業名（？）」が重複してるから。
そんなん、「作業」が増えたときにすぐ死ねる。

「スタッフ」もとくに固定ではない、ってことだし。

マスタ化しづらいなら、マスタから値を引っ張るケース、その行に単一スタッフを
書き込むケース、なんて形に分けると思う。

44:NAME IS NULL
09/03/08 10:38:07
俺もそう思う。
難しい言葉はともかく、第一正規形は
「テーブル内テーブル」を排除したものでしょ？

45:NAME IS NULL
09/03/08 14:46:17
>>42
待った。
>>13を見ただけではそのような関数従属が存在するとは判断できないと
いう意味で、>>13が非正規形であると言い切れないというのは良い。
ただ、>>42のような関数従属の存在を前提とした場合、コッドの関係モデルが
一階述語論理に基づくものである以上、そのようなカリー化された関係を
スキーマに表現することはできない。
カリー化の操作そのものには問題はないのだが、「左辺の具体値を新たな
属性名として利用して」という部分で、元の関数従属を表現できなくなっている
ことに注意。

46:NAME IS NULL
09/03/08 16:40:11
>>45
その通りです。一連の操作で展開された関係従属の集合と元の関数従属は
同じものとして扱う事は出来ません。
なので>>13と>>15のスキーマは「異なるデータ構造」、つまり異なる属性集合
と異なる関係従属性の集合を表現したものなります。

元々の質問が>>15は>>13の正規化崩しでは無いか、というものであったので、
そうではないです、なぜなら元より異なるデータ構造を表現しているのだから、
というのが一連の趣旨です。

>>43-44
データ設計(モデリング)とスキーマの正規化ははっきり区別すべきです。
データベースで表現したい対象から属性とその間の関係を抽出するデータ
設計とスキーマの正規化はどの本でも異なる章に書かれているはずです。

元のデータ中に存在する関係従属性を出来るだけ保ったままリレーションを
無損失に分解していくのが正規化の一連のステップですが、>>13から>>15
のように肝心の関係従属性の集合をざっくり別のものに入れ替えるようでは
流石に正規化の範囲を超えています。
これはデータ設計の問題として議論すべき事柄です。

実際>>13のスキーマはちゃんと正規形なのです。なぜなら>>27にあるように
「作業Aスタッフ1の名前はプロジェクトコードにのみ関数従属する」他の属性
についても以下同文、というようにデータを設計したらです。
そのようなデータを表現する限りに置いて、このスキーマは正規形です。
ですのでツッコミの入れどころは正規化ではなく、データ設計のそのものと
なります。

47:NAME IS NULL
09/03/08 16:43:09
ありゃ、関数従属が全部関係従属になっている・・・Typoです orz

48:スレタイも読めないぼんくらに言っても無駄か...
09/03/08 18:05:06
君らが賢いのわかったから、どっかよそでやってくれないかな。

49:NAME IS NULL
09/03/08 18:30:35
やっぱりＸＭＬデーターベースだよねー
あとから変更できるしー

50:NAME IS NULL
09/03/08 19:10:34
>>46
こっちにレスしました。

DB設計を語るスレ２
ｽﾚﾘﾝｸ(db板:321番)

>>48
こういう話題振ると、叩き上げの上司がそういう反応したりしますねぇ。

51:NAME IS NULL
09/03/08 19:12:48
いい加減、煽るのやめろよﾊｹﾞ

52:NAME IS NULL
09/03/08 19:46:02
注意されて逆切れするタイプなんだろ。
スルー推奨。

53:NAME IS NULL
09/03/08 20:11:07
>>49
XQueryとXPathが全社共通になったらね。

54:NAME IS NULL
09/03/08 20:12:56
>>53
そんなの待ってられないから、
とりあえず、現時点でシェアトップのものを
使いたいんだけど？

55:NAME IS NULL
09/03/08 20:27:50
よし　ならば　DB2　v9.1 pureXMLだ

夏前にはv9.5が出るよん

56:NAME IS NULL
09/03/08 21:54:31
>>53
それを言うなら問題はXQuery Update Facilityだと思うけど・・・
XQueryやXPathは世に言うXMLDBなら大概は使えると思う。

57:NAME IS NULL
09/03/09 16:25:54
PHP+MySQLで、ある設定をひとつのユーザにつき10個までに制限したい
この場合の設定テーブルは
ID、ユーザーID、設定1、設定2、設定3・・・設定10
なのか
ID=ユーザーID*10+0、ユーザーID、設定
ID=ユーザーID*10+1、ユーザーID、設定
ID=ユーザーID*10+2、ユーザーID、設定
なのか・・・

どういう構造がいいんでしょうか？

58:NAME IS NULL
09/03/09 16:45:11
典型的には後者だけど、次のような点が検討項目になるかな

・「設定」って何？

(a) 設定={設定値A, 設定値C, 設定値J, ...}みたいな形で
　　最大10個まで設定値の好きな組み合わせを選べるものなのか、

(b) 設定1: 設定値F, 設定2: 設定値A, ...設定10: 設定値Kみたいに
　　設定1から10までの項目があって、それぞれに設定値をセット
　　するようなものなのか

・(a)だとして、設定値を選択した順番に意味はあるのか。

・(b)だとして、設定値のダブりはありか(設定2: 設定値F, 設定5: 設定値F)

・「設定」をテーブルで表現したとして、どういう検索をしたいのか

59:NAME IS NULL
09/03/09 17:54:11
設定値はユーザーが任意に入力できる正規表現の文字列なので、
多種多様な値が入ることになります。
検索はしないかもです。データベースでやる必要はないんでしょうか？
とりあえず後者のやり方でやってみます

60:NAME IS NULL
09/03/09 18:11:32
ユーザーIDで検索するだけってことだよね。
ところでユーザーIDも保持してるならIDを10倍して足す意味が無いような。

61:NAME IS NULL
09/03/09 18:24:50
>>59
後者だとして、「ID=ユーザーID*10+0」は多分使い勝手が
悪いので別の方法をとった方が良いよ。

単にユーザが正規表現を10個登録したいだけなら属性は
{ユーザID, 正規表現}だけで、この２属性の組にPK制約
かけておけば十分。

もし正規表現1は○○、正規表現２はＸＸ、みたいにある
正規表現が何番目の正規表現であるかが重要なのであれば、
{ユーザID, 正規表現No, 正規表現}といった３属性にして
{ユーザID, 正規表現No}にPK制約をかける。正規表現Noは
1から10までの、ユーザごとの正規表現の通し番号です。

「ID=ユーザーID*10+0」という表現はユーザIDと正規表現No
の二つを表現しているので、すぐ隣に「ユーザID」属性もある
ことを考えると意味的にオーバーラップしています。
こういう属性は後日色々とトラブルの元であることが経験的に
知られています。
例えば今日から正規表現数を20個まで増量!などした時など。

62:NAME IS NULL
09/03/09 20:28:49
なるほど。
{ユーザID, 正規表現}でPK制約しようと思いましたが、
フレームワークがCakePHPなんで
IDは制約として必要で複合キーもサポートされていないみたいです。
しょうがないのでやっぱり、ID=ユーザID*10+0にしてみようと思います
ありがとうございました。

63:NAME IS NULL
09/03/09 21:05:21
なんでわざわざ SQL のスレで聞くのか・・・

64:NAME IS NULL
09/03/09 21:51:22
タイトルに「質問」「質疑」って書いてあるスレ他にあまり無いし。
DB設計の方は「語る」スレらしいのでこっちに流れてくるのも
仕方がないのではないかと。

65:NAME IS NULL
09/03/09 22:13:58
SQLServer2000を使用しています。
テーブルAに項目１がありそのMAX値に+1をした結果を
テーブルAにインサートするストアドプロシージャを作りました。

複数プロセスから同時実行する際に排他ロックをかけたいのですが、
デッドロックのような状態になってしまいました。（３プロセス同時）

ロックの考え方が間違っているのでしょうか？

@param1 output
AS
Begin Transaction

SELECT @param1 = MAX(項目１)+1
FROM テーブルA
WITH (TABLOCK,XLOCK)

INSERT INTO テーブルA(項目１)VALUES(@param1)

Commit Transaction

66:NAME IS NULL
09/03/09 22:23:38
ロックについてはわからんけど。

auto_increment 列を使うのが一般的じゃない？

67:NAME IS NULL
09/03/09 22:57:06
>>62
やめたほうがいいって回答されてるのにあえてやるってのは、回答者への
嫌がらせなんだろうかw

PKをIDにしても、べつにUNIQUE NOT NULLな制約は付けられるでしょ？

68:NAME IS NULL
09/03/09 23:06:51
>>62
複合キーを使えないというのであれば、普通にサロゲートキーに
しておいた方が良いと思う。
「ユーザID*10+x」なんてキーにするなんて、仕様に自ら爆弾を
仕込むようなものですぜ。

69:NAME IS NULL
09/03/09 23:08:18
SQLiteって、CHECK制約が使えないってひどくないですか。

データーの入る範囲を、１－１００にしたい、とか思っても、
できないってことじゃないですか。

70:NAME IS NULL
09/03/09 23:24:44
>>69
MySQLの間違いじゃなくて？

71:NAME IS NULL
09/03/09 23:30:57
>>70
SQLiteのはなしだよ

72:NAME IS NULL
09/03/09 23:31:52
アプリ側で制約すればいいじゃん。

73:NAME IS NULL
09/03/09 23:42:23
>>66
ありがとう

ただ、他のテーブルも同じように排他ロックをかけようとしていたので
先ほどの条件でうまく排他制御できないことが気になってました。

もう少し自分で試してみます。

74:NAME IS NULL
09/03/09 23:42:55
>>71
う、う～ん？

URLﾘﾝｸ(www.dbonline.jp)

うっふん

75:NAME IS NULL
09/03/09 23:50:15
>>74
読んでる教科書が2005年発行のやつだったので
古かったようです。

76:NAME IS NULL
09/03/10 10:13:25
実際にやってみて困った話じゃないのかよ

77:NAME IS NULL
09/03/10 21:31:56
だって教科書読んでたら、
対応してない、って書いてあるんだもん

78:NAME IS NULL
09/03/10 22:54:44
でも他人様の成果物に対して「ひどい」なんて文句をここに
書き込む前にちょっとはクグろうや。
「sqlite check」で一瞬で見つかったよ。

79:NAME IS NULL
09/03/10 23:17:37
トリガーっていうの、
よくわかりません

80:NAME IS NULL
09/03/10 23:21:03
>>79
Insert とか Update とか Delete とかが発生したときに呼び出されるプロシージャ。

TableA のデータが削除されたタイミングで、TableB の関連データを削除する、
とかってときに使えるよ。

81:NAME IS NULL
09/03/11 00:10:13
ようはストアード・プロシージャの一種なのね。
違いがわからんかった。

82:NAME IS NULL
09/03/11 00:21:19
ユリウス暦ってのがよくわかりません

83:NAME IS NULL
09/03/11 00:21:51
スキーマとプラグマの違いがよくわかりません

84:NAME IS NULL
09/03/11 00:22:03
>>82
それは SQL に関係なさそうです。

85:NAME IS NULL
09/03/11 00:23:58
ランダムに１－５を生成して、
それをボタンが押されるごとに、
どんどん足していく、というプログラムなのですが、
ＳＱＬ側でランダムに数字を生成して足す、という
構造に今、しているのですが、

「○を足しました」

とかってＰＨＰ側で表示したいのですが、今、
いくつの数字をランダムに生成して、いくつ足したのか、
って、ＰＨＰ側で知る方法はあるのでしょうか？

それとも、前後の差分を取るしかない？

86:NAME IS NULL
09/03/11 00:25:57
>>84
だってユリウス暦を取得する、って関数があるんです。
昨日発見しました。

87:NAME IS NULL
09/03/11 00:28:24
>>85
SQL でどんな処理をしてるのか書こうな。

>>84
ユリウス歴が何かは Google とかで調べような。
SQL には直接関係ないから。

88:NAME IS NULL
09/03/11 00:35:53
>>79
もちっと厳密に言うと、active databaseという考え方が元になっていて、

・○○が起きたときに (Event: イベント)
・△△だったら (Contition: 条件)
・ＸＸしちゃう (Action: アクション)

のECAの三つ組みをデータベーススキーマとして登録する仕組み。
外部キー制約などで表現しきれないリレーションやその間の制約
を表現したり、データ更新のログをとったりするのに使えたりします。

>>82
どっちかというとこの質問はWikipediaの領分かな。
欧米における日本の旧暦みたいなものです。
今でも正教会とか、お祭りを今のグレゴリウス暦ではなく昔の
ユリウス暦で祝うところがまだあるんです。

しかしこれを実装するのであればDATE値から大安とか丑年とか
計算してくれる関数がないと不平等だよな。

89:NAME IS NULL
09/03/11 00:39:57
>>85
> ランダムに１－５を生成して、
> それをボタンが押されるごとに、
> どんどん足していく、

なんか、最近似たようなモンを見たような気がするが、なんかの課題なのか？

90:NAME IS NULL
09/03/11 01:10:43
SQLite3で、
あとからAUTOINCREMENT 指定するのは
不可能？

91:NAME IS NULL
09/03/11 01:29:47
>>89
どれ？

92:NAME IS NULL
09/03/11 01:50:35
前スレでの話しだな。> ランダム

で、その前スレから疑問に思ってたけど、乱数生成をSQL側でしようとするのが疑問だった。
最終的にはUPATE Table SET val = val + random().... 的な使われ方だけで
済むのならSQLで済ませてもいいけど、いろいろ制約(乱数の範囲やvalの最大値)があるみたいだし、
素直にPHPで生成させておけば、そんなに悩むものでも無かろう。

93:NAME IS NULL
09/03/11 01:53:49
>>92
それで生成させたランダム値を
PHP側で知りたいんですけど、無理ですか？

94:NAME IS NULL
09/03/11 01:55:05
ようはコンビニでおでんを買って、
「たれは、ごまだれにしますか？からしにしますか？それとも何もつけないようにしますか？」
って聞かれて
「ランダムでいいです」
って答えて、
「でも、ランダムに決定して、その結果は教えてね」
みたいな。

95:NAME IS NULL
09/03/11 01:57:29
>>93
できるだろ。テーブルに1件だけデータを入れておいて、Update のあと Select するとか。

96:NAME IS NULL
09/03/11 09:12:08
テンポラリーテーブルか。
うまいね。

97:NAME IS NULL
09/03/11 19:24:23
SQLite3の最新版で、
alterで、テーブルを作成したあとに、
autoincrementを付加することは可能なの？

98:NAME IS NULL
09/03/11 20:48:51
URLﾘﾝｸ(sqlzoo.net)

いいページ見つけた。
ボタンを押すとSQL文を生成してくれるっていう。

99:NAME IS NULL
09/03/11 21:03:16
見せる…　行ロックを無限に発生させてしまうSELECT文が…

100:NAME IS NULL
09/03/11 23:37:10
>>4に関連して「最新のものの次点のレコード」を抽出したい場合は
どうすればいいでしょうか。
DBはOracle10gです。

<出力イメージ>
1 | 2007-11-10 | ccc
3 | 2007-11-11 | eee

101:NAME IS NULL
09/03/12 00:16:43
>>100
1 | 2007-11-11 | aaa
の間違いじゃね。

Oracle使いじゃないけど、WITH句を使うとこんな感じかな。
WITH RemoveMax AS (SELECT * FROM Table WHERE (id,date_) NOT IN (SELECT id,max(date_) FROM Table GROUP BY id))
SELECT * FROM RemoveMax JOIN
(SELECT id,max(date_) AS date_ FROM RemoveMax GROUP BY id) AS T1
USING (id,date_);

WITH句なしで、
SELECT * FROM
(SELECT * FROM Table WHERE (id,date_) NOT IN (SELECT id,max(date_) FROM Table GROUP BY id))AS T1
JOIN
(SELECT id,max(date_)AS date_ FROM
(SELECT * FROM Table WHERE (id,date_) NOT IN (SELECT id,max(date_) FROM Table GROUP BY id))AS T2
GROUP BY id)AS T3
USING(id,date_);

フィールド名にdateは使えないのでdate_としています。

102:NAME IS NULL
09/03/16 20:39:51
create table categorizings(
product_id integer not null references products(id),
category_id integer not null references categories(id),
sub_category_id integer references sub_categories(id),
created_at datetime not null default current_datetime,
updated_at datetime not null default current_datetime
)
というテーブルがあるのですが、重複しているレコードがあるので、それを抽出しようとしています。
はじめは
select product_id, category_id, sub_category_id
from categorizings
group by product_id, category_id, sub_category_id
having count(*) > 1
としていたのですが、重複しているデータのcreated_atとupdated_atも抽出するようにと言われて、困ってます。
今は
select c1.*
from categorizings c1
where concat(c1.product_id, '-', c1.category_id, '-', c1.sub_category_id) in
　　(select concat(c2.product_id, '-', c2.category_id, '-', c2.sub_category_id)
　　 from product_categorizings c2
　　 group by c2.product_id, c2.category_id, c2.sub_category_id
　　 having count(*) > 1)
;
としているのですが、パフォーマンスが遅いし、これで正しいのかもよくわかりません。
なんかいい方法を紹介してください。よろしくお願いします。

MySQL 5.45

103:NAME IS NULL
09/03/16 20:55:12
未チェック。概念だけ

select A.*
from
categorizings A,
(select
product_id, category_id, sub_category_id,
count(*) as RNUM
from categorizings
group by product_id, category_id, sub_category_id
) B
where
A.product_id = B.product_id and
A.category_id = B.category_id and
A.sub_category_id = B.sub_category_id and
B.RNUM > 1

104:NAME IS NULL
09/03/16 23:19:26
>>102
パフォーマンスを気にするのなら、
product_id, category_id, sub_category_idのカラムを
複合primary keyに持つcategorizings_duplicatedなりの
別テーブルを作って前者のSQLをinsert intoして
それとjoinするのが最速かと。

105:NAME IS NULL
09/03/17 08:55:52 HhqqMBkm
vistaに11ｇの30日番をインストールしました。
sqlplusがコマンドプロンプトと変わりません。
よく見慣れた十字架のお墓みたいなアイコンのsqlplusだけをインストール
できないでしょうか？

106:NAME IS NULL
09/03/17 09:29:05
GUI版のSQL*Plusは11gから廃止されました。

107:NAME IS NULL
09/03/17 12:52:28 HhqqMBkm
>>102
ありがとうございます。

108:NAME IS NULL
09/03/17 20:57:32
以下のようなデータがあるとします。
id no data
1 1 aaa
1 2 bbb
1 4 ccc
1 5 ddd

このデータをidとnoで昇順でソートした後
noの番号を降りなおしたい。
つまり、以下のように更新したい
1 1 aaa
1 2 bbb
1 3 ccc
1 4 ddd

このようなsqlはどのように記述すればよいでしょうか。？

109:NAME IS NULL
09/03/17 21:33:45
>>108
idとnoの組合せで一意になる、かつDBMSがOracleと仮定して

update TableName a
set no = (select b.RN
　　　　　from (select id,
　　　　　　　　　　　 no,
　　　　　　　　　　　 row_number() over (order by id, no) as RN
　　　　　　　　from TableName) b
　　　　　where a.id = b.id
　　　　　and a.no = b.no)
;

110:NAME IS NULL
09/03/17 22:03:11
>>108
ついでにたぶん汎用解

update TableName a
set no = (select count(*) + 1
　　　　　from TableName b
　　　　　where a.id > b.id
　　　　　or (a.id = b.id and a.no > b.no))
;

111:>>108
09/03/18 01:43:48
>>109
>>110
ありがとうございます。
ちなみにmysqlです。

112:>>111
09/03/18 16:24:28
mysqlの場合は、以下のようにする。

update hoge as a inner join (select *,(select count(*) + 1 from hoge where no<x.no) as rownum from hoge as x) as b on a.id=b.id and a.no=b.no set a.no=b.rownum where a.id=1

113:NAME IS NULL
09/03/18 20:43:22
mysqlだとそんな奇怪なSQLになるのか・・・
とはいえ、>>110も標準SQLではないな。（UPDATE句に相関名は書けない）
相関名をやめて
update TableName
set no = (select count(*) + 1
　　　　　from TableName b
　　　　　where TableName.id > b.id
　　　　　or (TableName.id = b.id and TableName.no > b.no))
;
にすれば標準だと思うけど。

114:NAME IS NULL
09/03/18 23:45:02
Oracle10gにて
SQL中の文字列(WHEREの後ろとかLIKEの後ろ)にそのままでは使えない文字の全量、対応方法ってわかりますか？

115:NAME IS NULL
09/03/18 23:49:07
>>114
質問の意味が分からん

116:NAME IS NULL
09/03/18 23:50:26
予約語の一覧が欲しいのかな。
各RDBMSのマニュアル読んで、ユニークな列名を考えて下さい。

117:NAME IS NULL
09/03/18 23:59:52
>>114
質問の意図が、予約語と解釈する人もいれば、エスケープが必要な文字と
解釈する人もいそうな曖昧な質問の仕方だなw

118:NAME IS NULL
09/03/19 00:55:19
114です
すみません…

'％＿(シングルコーテ、パーセント、アンダーバー)のようなエスケープが必要な文字を知りたかったのです。

119:NAME IS NULL
09/03/19 14:20:58
>>118
なかなか回答つかないもんだな。
まぁ、本来はOracleスレで聞いた方がよかったんだろうが。
知りたい内容に対応する公式リファレンスは以下かな。

テキスト・リテラル
URLﾘﾝｸ(otndnld.oracle.co.jp)

LIKE条件
URLﾘﾝｸ(otndnld.oracle.co.jp)

デフォルトだとリテラル文字列は'、LIKE条件はさらに%_も
エスケープが必要となるが、それを変更する方法も提供されているということか。
バインド変数を使えば、これらのしがらみからも幾分は開放される。

120:NAME IS NULL
09/03/19 15:36:30
>>108
このテーブルってid た

121:NAME IS NULL
09/03/19 15:49:28
>>108
このテーブルってidとnoがprimary keyだと思うんだけど、更新するカラムをprimary keyのするのはよくないと思うんだけど、どうなんだろう。

もし、俺がテーブル設計するなら、以下のようなカラムを用意して、idとnoをprimary keyにして、no2を更新カラムにする。

id no no2 data

こうしないと、以下のデータがあったとして、
id no data
1 1 aaa
1 2 bbb
1 3 ccc

二行目以下のnoを、まとめて+1するようなsqlをじた、キー重複エラーが発生する。

122:NAME IS NULL
09/03/24 15:04:34 yR1qlEgs
ある条件でORDERBYした時の、あるレコードの前後のレコードを取得することって可能ですか？

全レコードを一旦取得して、ホスト言語で前後を抽出するか

ORDER　BYが一個のカラムなら、対象レコードのそのカラムより大きいものをCOUNTして、
OFFSETでとってくることも可能かな
でも、ORDER　BY対象がユニークなカラムじゃないとだめか・・

なんかどれも効率悪そうなんですけど、もっといい方法ありますか？
MySQLです。

123:NAME IS NULL
09/03/24 15:36:53
あるレコードの前後つーのは、
あるレコードの前→あるレコードより小さい中での最大
あるレコードの後→あるレコードより大きい中での最小

124:NAME IS NULL
09/03/24 15:54:42
ORDERBY対象がユニークなカラムじゃない
のに特定レコードの前後を取りたいとかアレなの？
氏ぬの？

125:NAME IS NULL
09/03/24 17:54:44
>>122
具体例を挙げないと、何をしたいのかわからん人がほとんどだと思う(俺も含め)。
ソートした結果の10位前後のレコードを取り出したいというのなら
「じゃあ select * from table order by key limit 9, 3; でいいじゃん」
と誰もが答えるだろう。

126:NAME IS NULL
09/03/24 18:32:38 yR1qlEgs
>>123
ああ、そうか、そう考えれば簡単ですね。ありがとうございます。

>>124
ありがとうございます。
例えば更新日時で時系列に次へ次へみたいにリンクを出したい時、
そういう場合どうすればいいのでしょう？
更新日時って同じタイムスタンプになる確率があるって聞いたんですけど
更新日時カラム自体をユニークにして万が一同じタイムスタンプになったら、再保存するようにするのでしょうか？

これが例えば変更されない登録日時なら主キーを使ってしまうって言う手もありますけど。

まあ、そもそも、更新されたら並び順が変わるし、このインターフェイス自体が問題あるきもしますけど。

127:122
09/03/24 18:42:55 yR1qlEgs
>>125
具体的には126にちょっと書きましたが
記事の表示ページに更新日時順で次へ、前へというリンクを出したいんです。

で、そのページはダイレクトに主キーで記事を指定してくることもあるのでLIMIT　1　OFSSET～っていうわけには行かない感じなんです

128:125
09/03/24 19:06:53
>>127
ページング処理をしたかったのね。
俺も業務上そのテの開発はよくやるけど、やっぱり limit x, y だよ。
記事を指定して、その記事が含まれるページ(例えば81～90件目を表示)とかに
飛ぶようなリクエストが発生するシステムであれば、
(順位, 更新日時, 記事ID) を持つソート済みテーブルをあらかじめ作成しておく。
レコード数が多いなら記事IDにindex付与。
更新日時が可変で順位が変わる可能性があるのなら、その頻度に応じてソート済みテーブルを再構築する。

129:NAME IS NULL
09/03/24 20:06:31
じゃ123でいいじゃん

130:NAME IS NULL
09/03/26 08:01:10
テーブルX(a,b)と
テーブルY(b,c)で
列bを結合キーにした場合、
テーブルXに無いレコードをテーブルYから削除するには
どのようなSQL文を書けば良いでしょうか

131:NAME IS NULL
09/03/26 08:05:03
where not in select b from A
でいいんじゃないの？

132:NAME IS NULL
09/03/26 08:05:42
ごめんAじゃなくてXだった

133:130
09/03/26 08:13:50
>>131
ありがとうございます

結合は不慣れでして…
ちゃんと勉強しないといかんな
(´･ω･｀)

134:NAME IS NULL
09/03/26 08:26:15
>>133
結合は慣れです。
経験を積めば楽に結合出来るようにもなります。
慣れるまではプロの手ほどきを受けるのも良いかもしれませんね。

135:NAME IS NULL
09/03/26 08:29:49
131で書いたのは結合じゃなくてサブクエリだけどね

136:NAME IS NULL
09/03/27 02:32:29 p8FYVGy/
質問です。

以下のような、木構造のデータがあるとします。
東京→大田区→蒲田→羽田1丁目
東京→大田区→蒲田→羽田2丁目
東京→千代田区→秋葉原
大阪→住之江区→緑町

これを、テーブルに以下のような形式で入れるとします。
name prev_name
東京東京
大田区東京
蒲田大田区
羽田1丁目蒲田
羽田2丁目蒲田
千代田区東京
秋葉原千代田区
大阪大阪
住之江区大阪
大阪市大阪
緑町住之江区

name=prev_nameなレコードが親トップとなります。（上記のデータですと、東京と大阪が親トップにあたります）

この状態から、name=大田区のレコードを削除されたと考えてください。
ここから、name=(蒲田、羽田一丁目、羽田二丁目）のデータを取得するにはどのようなSQLをかけばよいでしょうか？

※ 住所をこのような形式で管理するのはおかしい！と感じる方もいらっしゃると思いますが、
これは問題をわかりやすくするため、このようにしています。
実際に住所を上記のような管理をしようとしているわけではないので、あらかじめご了承ください。

137:NAME IS NULL
09/03/27 03:02:29
>>136
恐らく、あなたが欲しいであろう回答をもらうための
前提条件が十分に説明されていない。
name in ('蒲田', '羽田一丁目', '羽田二丁目')
で満たされてしまうからだ。

138:NAME IS NULL
09/03/27 03:09:42 p8FYVGy/
>>137
説明が足りず、すいません。

親がなくなってしまったname=蒲田のレコードと、その配下のレコード全部(つまり、羽田一丁目、羽田二丁目）を抽出したいという意味です。

139:NAME IS NULL
09/03/27 03:33:47
>>138
その説明でも曖昧さはまだ残るのだが…。
「親が存在しない、つまり不完全な構造になってしまった全てのレコード
およびその子ノードたち(一世代まで下)を列挙したい」
と勝手に解釈するなら
select name from table_name
where prev_name not in (select name from table_name)
union all
select name from table_name
where prev_name in
(select name from table_name
where prev_name not in (select name from table_name))

次ページ