Question

我试图命令MySQL在一行的配对总数中选择发言。例如,如果表象......

id | dessert | choice --------------------- 1 | pie | apple pie, chocolate pie 2 | cake | chocolate cake, lemon cake, white chocolate cake, triple chocolate cake 3 | donut | frosted donut, chocolate donut, chocolate cream donut

......以及如果有人查询<代码>chocolate,结果应当命令:

dessert | matches ----------------- cake | 3 donut | 2 pie | 1

However, I m struggling to make the query ordered like this. I ve looked at other examples here, but they seem overly complicated for what I guessed would be a fairly simple thing.

是否有直截了当的办法来做到这一点? 我对我的SQL说了新话,如果这是一个显而易见的问题,那么我会感到愤怒。

提前感谢你们的想法!

P.S. I m无法在本表中使用fulltext,因此我可以按相关性来决定结果。

Answer 1

How about dividing your data into two tables - one defining the desserts and one defining the choices? This would make the queries simpler and faster.

论文:

id | dessert
------------
1  | pie
2  | cake
3  | donut

选择表:

id | choice
-----------
1  | apple pie
1  | chocolate pie 
2  | chocolate cake
2  | lemon cake
2  | white chocolate cake
2  | triple chocolate cake
3  | frosted donut
3  | chocolate donut
3  | chocolate cream donut

然后,你可以做这样的事情:

select `dessert`, count(*) as `matches`
       from `desserts` join `choices` using (`id`)
       where `choice` like  %chocolate% 
       group by `id`
       order by `matches` desc

Answer 2

仅提一字,然后比较长篇的区别

SELECT id
     , desert
     , ( LENGTH(choice) - LENGTH(REPLACE(choice, chocolate ,  )) )
       / LENGTH( chocolate ) AS matches
FROM desert_table
WHERE choice LIKE  %chocolate% 
ORDER BY 3 DESC

友情链接