sql—在mysql中首先选择最匹配的行

nnvyjq4y 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(253)

我的项目运行在laravel5.4，mysql 5.7上
我有四张table

recipes             (id, name)
ingredient_recipe   (id, recipe_id, ingredient_id, amount)
ingredients         (id, name, cat_id)
ingredient_category (id, name)
``` `recipes` 以及 `ingredients` 有 `many to many relations` 通过 `ingredient_recipe` table。每种食谱都可以有许多配料。每种成分都有其分类 `cat_id` 哪些参考文献 `id` 在 `ingredient_category` table。
我需要选择所有配方的成分类别ID等于要求的值，并把最匹配的值在顶部。例如，请求的成分类别ID是 `[23,56,76,102,11]` . 比如说食谱 `foo` 有哪些种类的配料相匹配 `23,56,76` ,  `bar` 匹配 `23,56` 以及 `baz` 匹配 `23` . 他们应该被命令- `foo, bar, baz` . 我怎么能这样点呢？
这是我的sql代码

--select recipes
SELECT * from recipes where exists
--select recipe's ingredients
(select ingredients.id
from ingredients
inner join
ingredient_recipe on ingredients.id =
ingredient_recipe.ingredient_id where recipes.id =
ingredient_recipe.recipe_id
and exists
--select ingredient categories, where id ..
(select ingredient_category.id
from ingredient_category
where ingredients.cat_id = ingredient_category.id
and id IN (23,56,76,102,11)))

但这段代码并没有把大部分匹配的配方放在最前面。我知道我可以像在这个例子中那样选择，然后过滤它们，但是在sql中有没有办法呢？

sql mysql Database laravel sorting

来源：https://stackoverflow.com/questions/48276636/select-most-matched-rows-at-first-in-mysql

2条答案

按热度按时间

tsm1rwdh1#

您可以使用groupby和categories表的左联接来获取类别数的计数，然后根据该计数进行排序。

SELECT 
    a.`id`,
    a.`name`,
    c.`id`.
    c.`name`,
    count(d.`id`) as `numcategories`,
    GROUP_CONCAT(d.`name`)
FROM `recipes` a
JOIN `ingredient_recipe` b
ON a.`id` = b.`recipe_id`
JOIN `ingredients` c
ON b.`ingredient_id` = c.`id`
LEFT JOIN `ingredient_category` d
ON c.`cat_id` = d.`id`
GROUP BY a.`name`,c.`name`
ORDER BY count(d.`id`) DESC, a.`name`,c.`name`

赞(0）回复(0）举报 2021-06-21

aiazj4mn2#

通过连接表将配方表连接到配料表 ingredient_recipe ，然后按配方聚合。对于每个配方，我们可以计算出Map到您的列表的配料的数量，并且我们可以排序结果集，首先显示较高的匹配项。

SELECT
    r.id,
    r.name,
    COUNT(CASE WHEN i.cat_id IN (23, 56, 76, 102, 11) THEN 1 END) AS match_cnt 
FROM recipes r
INNER JOIN ingredient_recipe ir
    ON r.id = ir.recipe_id
INNER JOIN ingredients i
    ON ir.ingredient_id = i.id
GROUP BY r.id
ORDER BY match_cnt DESC;

我们还可以添加一个 HAVING 条款，例如过滤掉不符合最低匹配成分数量的配方。我们也可以 LIMIT 子句来限制匹配的总数。

赞(0）回复(0）举报 2021-06-21

我来回答

sql—在mysql中首先选择最匹配的行

2条答案

相关问题

热门标签

最新问答