ＳＥＬＥＣＴ文で最大値を抽出

質問

(samp_tv)
c-id | s-di | name1 | name2
----------------------------
1 | 100 | A | A
1 | 101 | B | D
1 | 102 | C | F
2 | 103 | D | E
2 | 104 | E | J
2 | 105 | F | K

(samp_dic)
id | c-num | word
--------------------
1 | 11 | A
1 | 11 | B
1 | 11 | C
2 | 12 | D
2 | 12 | E
2 | 12 | F
3 | 13 | G

行いたい動作は、samp_tv.name1とname2にsamp_dic.wordが存在し、最もwordが存在するs-idとc-numをSELECTで抽出したいです。

SELECT s-id,c-num FROM samp_tv,samp_dic WHERE c-id=s-id AND (name1 LIKE '%'||word||'%' OR name2 LIKE '%'||word||'%');

上記のSQLでは、nameに存在する全てのs-idとc-numが出てきます。
s-idごとに最も出現数の高いc-numを抽出するにはどうしたらいいのでしょうか？
よろしくお願い致します。

回答No.3

k_o_r_o_c_h_a_n · ベストアンサー

select
a.code,a.sid,
(select id
from
(select
y.code,y.sid,x.id,
(select count(*) from samp_dic as z where z.num=y.code and z.id=x.id and y.t1 like '%'||z.word||'%') +
(select count(*) from samp_dic as z where z.num=y.code and z.id=x.id and y.t2 like '%'||z.word||'%') as Tx
from (select distinct num,id from samp_dic) as x,samp_tv as y
order by 1,2,4 desc) as b
where a.code=b.code and a.sid=b.sid limit 1) as "どうだろ？"
from samp_tv as a

効率は度外視して、こんな感じで、一文で書ける。
(少なくとも、ver7.2.1では動いてる)

回答No.2

bin-chan

ひとつのSELECT文では無理（っぽい）。
Oracleでいうビュー、ACCESSでいうクエリはPostgresSQLではなんと表現するのかな？４つになりましたぞ。
まとめようもあろうが、とりあえず回答します。
(ACCESSで作成・試験しました）

１．T1、またはT2にWORDを含むものを検出。
２．１．をS-ID別ID別にグループ化し、件数を集計。
３．２．をS-ID別にグループ化し最大件数を集計。
４．２．の「件数」が３．の「最大件数」であるS-IDとIDを表示。
の４つです。

結果は以下の通り。124は「最大」が共に２件なので表示されます。

s-id｜id
--------------
123　｜　1001
124　｜　1001
124　｜　1002
125　｜　1002

ACCESSのクエリのまま貼り付けます。適宜修正してください。
１．qry(1)対象データ
SELECT samp_dic.num, samp_dic.id, samp_dic.word, samp_tv.code, samp_tv.[s-id], samp_tv.t1, samp_tv.t2
FROM samp_dic INNER JOIN samp_tv ON samp_dic.num = samp_tv.code
WHERE (((samp_tv.t1) Like ('*' & [word] & '*'))) OR (((samp_tv.t2) Like ('*' & [word] & '*')));

２．qry(2)S-ID別ID別件数
SELECT qry(1)対象データ.[s-id], qry(1)対象データ.id, Count(qry(1)対象データ.id) AS idのカウント
FROM qry(1)対象データ
GROUP BY qry(1)対象データ.[s-id], qry(1)対象データ.id;

３．qry(3)最大件数
SELECT [qry(2)S-ID別ID別件数].[s-id], Max([qry(2)S-ID別ID別件数].idのカウント) AS idのカウントの最大
FROM [qry(2)S-ID別ID別件数]
GROUP BY [qry(2)S-ID別ID別件数].[s-id];

４．qry(4)結果データ
SELECT [qry(2)S-ID別ID別件数].[s-id], [qry(2)S-ID別ID別件数].[id]
FROM [qry(2)S-ID別ID別件数]
WHERE [qry(2)S-ID別ID別件数].[idのカウント] in ( SELECT [qry(3)最大件数].[idのカウントの最大] FROM qry(3)最大件数 where [qry(2)S-ID別ID別件数].[s-id]=[qry(3)最大件数].[s-id] );

補足

大変助かりました。補足ですが、
結果データ
s-id｜id
--------------
123　｜　1001
124　｜　1001
124　｜　1002
125　｜　1002
で
s-id(124)は２件存在しますが、
１件だけにしたい場合は、
４．qry(4)結果データのSQL分にどのような文を
追加したら良いのでしょうか？

よろしくお願い致します。

回答No.1

bin-chan

２つのテーブルの関連付けの定義が明確で無いようですが？

上記の例から、どのような結果を得たいのか、を補足ください。

補足

すみません。例が悪いですね。
(samp_tv)
code | s-id | t1 | t2
-----------------------
10 | 123 |世界の不思議　｜グルメ・秘境の旅
10 | 124 |旅行宿巡り　　｜世界のグルメ料理
10 | 125 |食する　　　　｜世界の美味しい料理
10 | 126 |　　：　　　　｜　　　：
11 | 127 |　　：　　　　｜　　　：
11 | 128 |　　：　　　　｜　　　：

(samp_dic)
num | id | word
-------------------
10 | 1001 |　世界
10 | 1001 |　秘境
10 | 1001 |　旅行
10 | 1002 |　グルメ
10 | 1002 |　美味
10 | 1002 |　料理

上記のテーブルより
各s-idに対して、t1/t2にwordの出現回数が多い、
idを取得したいです。
上記では、s-id(123)の場合、t1とt2には、
世界・秘境（id=1001)とグルメ(id=1002)が存在しますが、id=1001のグループのwordの方が２回出現しているので、

out_num | out_id
-------------------
123 | 1001
124 | 1002
： | ：
： |　：

SELECT s_id , id FROM samp_tv,samp_dic WHERE code=num AND (t1 LIKE'%'||word||'%' OR t2 LIKE '%'||word||'%');

上記のSQLでは、t1とt2に出現する単語数分s_idとidが出力されます。
必要なデータはs-idと最も単語が出現したidです。

よろしくお願い致します。