当前位置: 动力学知识库 > 问答 > 编程问答 >

performance - Order by matched records

问题描述:

First of, sorry for the title, I'm not sure how to name the question.

Basically, I'm writing a basic search engine based on a location and categories.

I have a table events, a table events_categories and a table categories. Object have a column city and a column state

One event have and belongs to many categories, and one category have and belongs to many events.

I currently have the following query:

select

*

from events

LEFT OUTER JOIN "events_categories"

ON "events_categories"."event_id" = "events"."id"

LEFT OUTER JOIN "categories"

ON "categories"."id" = "events_categories"."category_id"

WHERE "categories"."id" = 2

OR "categories"."id" = 3

order by city!='Paris', state!='TX'

It works but I'd like to order my result based on the number of category matched and I also have duplicated row. I'd like to remove them

Here is the sqlfiddle I use

The expected result should be:

  • meetup #2
  • meetup #1
  • meetup #4
  • meetup #16
  • meetup #5
  • meetup #7
  • meetup #8

网友答案:

To sort by the number of matching categories, you could group by the event name, and sort by count(*) desc:

select  e.name
,       count(*)
from    events e
join    events_categories ec
on      ec.event_id = e.id
join    categories c
on      c.id = ec.category_id
where   c.id in (2,3)
group by
        e.name
order by 
        count(*) desc

Example at SQL Fiddle.

网友答案:

This seems to do what you want. Although the last two rows might not be in the order you require but that is because the values for all sort conditions are the same for those two rows:

select events.id as event_id, 
       events.name as event_name,
       events.city,
       events.state,
       categories.id as category_id,
       categories.name as category_name,
       count(*) over (partition by events.id) as cat_count
from events
  LEFT JOIN events_categories ON events_categories.event_id = events.id
  LEFT JOIN categories ON categories.id = events_categories.category_id 
WHERE categories.id IN (2,3)
order by city <> 'Paris', 
         state <> 'TX',
         cat_count desc;

SQLFiddle: http://sqlfiddle.com/#!12/6c7a5/1

分享给朋友:
您可能感兴趣的文章:
随机阅读: