以下のテーブル構成があるとします。(投稿&タグ付けできるサイト)
▼postsテーブル
|pid|msg|cnt|
|1|はてなは初心者|100|
|2|今日は良い天気|150|
|3|旅行に行きたい|200|
▼tagsテーブル
|tid|pid|tag|
|1|1|はてな|
|2|1|初心者|
|3|3|旅行|
閲覧者がキーワード検索を行う際、postsテーブルのmsgとtagsテーブルのtagを対象に、どちらに合致したかによって係数を変えてスコア付けして、スコア順にソートした結果を得たいと考えています。
↓間違ったSQLですがイメージとしてはこのようなことを実現したいのです。
select distinct p.*, p.cnt * (if msgに合致=1 else tagに合致=2) as SCORE
from posts as p left join tags as t on p.pid=t.pid
where p.msg like ? or t.tag like ? order by SCORE desc
どうすれば良いのか、皆さんのお知恵を頂ければ嬉しいです。
Distinct でもいいかもしれませんが。
2012/03/12 12:31:21ありがとうございます。
2012/03/12 17:52:20実験してみまして以下のようなSQLでどちらのwhere条件に合致したかのフラグ情報が取れました。(重複レコードもなくせました。)
なんとかなりそうです。
細かい内容にも関わらず、色々とありがとうございました。
感謝!
select uni.pid, uni.msg, sum(uni.FLAG) as TAG_FLAG from (
(select p.*, 0 as FLAG
from posts as p left join tags as t on p.pid=t.pid
where p.msg like ?)
union
(select p.*, 1 as FLAG
from posts as p left join tags as t on p.pid=t.pid
where t.tag like ?)
) as uni group by uni.pid order by TAG_FLAG desc