１文で最大値を求める方法

質問

テーブル（buf1)
g_num
------
11
テーブル（sample1）

　text |code
-------------------------------------------------
(1)ＮＢＡバスケットは今シーズン熱い戦いが繰り広げられるだろう | 11
(2)戦争は映画の中でけの戦いにしてほしい | 11
(3)名探偵と言えば、ホームズ？今はコナンになるのか？ | 12

テーブル（c_text)
g_code | num | word
----------------------
11 | 1100 |ＮＢＡ
11 | 1100 |バスケ
11 | 1100 |ＭＪ
11 | 1100 |ダンク
11 | 1101 |映画
11 | 1101 |監督
11 | 1102 |戦争
11 | 1102 |戦い
12 | 1200 |データベース

１．テーブル（sample1）のcodeがテーブル（buf1）のg_numと等しいレコード。
　　ここでは、(1)と(2)のレコードが対象となる。
２．codeとテーブル（c_text)のg_codeが等しいwordを用いて、
　　textを文字検索を行う（LIKEを用いて部分一致検索）を行う。
text LIKE '%'|| word ||'%'
３．textにwordが存在するnumの出現回数を数えて、最も出現回数の
　　高いnumをテーブル(sample1)の各レコードのnum値とする。

レコード(1)の場合、num(1100)が２回(ＮＢＡとバスケ）出現しており、
num(1102)が１回（戦い）が出現しているがnum=1100の方が出現回数が高いため
レコード(1)のnumは1100とする。

最終的欲しいのは、

text |code|num
-------------------------------------------------
ＮＢＡバスケットは今シーズン熱い戦いが繰り広げられるだろう | 11 |1100
戦争は映画の中でけの戦いにしてほしい| 11 |1102

です。
説明がわかりにくくて申し訳ございません。
なるべく一時テーブルなど作成せずに
ＳＱＬ一文で処理がしたいです。

どうぞよろしくお願い致します。

回答No.3

mitu_fu

ここのカテゴリってSQLSERVERですよね？
>>text LIKE '%'|| word ||'%'
>>今SQLite
このあたりを見ると違うDBのような・・
知識不足でごめんなさい
以下のSQLでうまくいく予定ですが
長文SQLでごめんなさい

SELECT A.text,A.code,A.num From (
SELECT sample1.text,sample1.code,c_text.num,Count(*) as 出現回数
FROM sample1,buf1,c_text
WHERE sample1.code = buf1.g_num
AND sample1.code = c_text.g_code
AND sample1.text LIKE '%' + c_text.word + '%'
Group By sample1.text,sample1.code,c_text.num
) A ,( SELECT B.text,MAX(出現回数) 出現回数最大
From ( SELECT sample1.text,c_text.num,
Count(*) as 出現回数
FROM sample1,buf1,c_text
WHERE sample1.code = buf1.g_num
AND sample1.code = c_text.g_code
AND sample1.text LIKE '%'+c_text.word + '%'
Group By sample1.text,c_text.num ) as B
Group By B.text
) BB
WHERE A.text = BB.text
AND A.出現回数 = 出現回数最大

回答No.2

xcrOSgS2wY

SQL Serverのバージョンが7.0以上でないとtopは使えません。

バージョンは大丈夫なのにtopが使えないとすれば、原因は

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q260526

の記述にある障害と同じものかもしれません。

なお、topなしでSQLを記述するのはわたしには無理そうですので他の方にお譲りします。

回答No.1

xcrOSgS2wY

パズルとして面白かったのとサンプルのデータがちょっと楽しかったので、挑戦してみました。

select distinct left(s.text,10) text, s.code, c.num
　　from sample1 s, c_text c, buf1 b
　　where s.code = c.g_code and
　　　　　s.code = b.g_num and
　　　　　c.num = (
　　　　　　　select top 1 c2.num
　　　　　　　　　from c_text c2
　　　　　　　　　where s.code = c2.g_code and
　　　　　　　　　　　　s.text like '%' + c2.word + '%'
　　　　　　　　　group by c2.g_code, c2.num
　　　　　　　　　order by count(*) desc, c2.num)

（結果）
text code num
-------------------- ----------- -----------
(1)ＮＢＡバスケッ 11 1100
(2)戦争は映画の中 11 1102

※制限：like演算子による一致の件数が等しくなる複数のnumがある場合、そのうちいちばん小さいnumを返す

なお、いろいろな場合に本当にちゃんと動くのか、また動作の効率がどうか等は一切検証していません。

補足

今SQLiteを使用しているため理由はわからないですが、select top 1　～　が使用できません。
そこで、再度質問ですが、
テーブル（sample1）のtextにwordが一致する最も出現頻度の高いnumを抽出するＳＱＬをよろしくお願い致します。
max（）を使うのでしょうか？？わかりません。。。