MySQL 使用双左连接更新行，限制第一次匹配 [英] MySQL Update rows with double left join, limiting first match

问题描述

我有三个表(

我需要通过比较 Products.name 和 Filters.filter 来获得橙色文本.

我发现子字符串匹配可以这样完成:

关于 Products.name LIKE CONCAT('%',Filters.filter,'%');

我只需要使用第一个过滤器匹配项.所以蘑菇汤"将匹配汤"而不是蘑菇".

完成这项任务的最佳方法是什么?

如果可能，请提供一个经过测试的 SQLFiddle 链接.

我尝试了什么:(请随意跳过下面的所有内容)

尝试双连接:

更新产品left join Filters on Products.name LIKE CONCAT('%',Filters.filter,'%')在 Filters.category_name = Categories.name 上加入类别设置 Products.category_id = Categories.id, Products.filter = Filters.filter;

但这不会导致使用 first 匹配，因为第一次加入返回 all 过滤器匹配(每个产品多行)，并且在第二次加入之后类别是从上一场比赛开始.

例如:蘑菇汤进入蘑菇成分"过滤器类别，而不是汤餐".

还尝试过加入 + 排序方式:

选择 Products.name, Filters.*从产品left join Filters on Products.name LIKE CONCAT('%',Filters.filter,'%')按 Products.name 分组；

这会根据需要返回每个产品的第一个匹配项，但我不能在同一个查询中进行第二次左连接，我也不能在更新"功能而不是选择"之后让分组依据"工作.

当 sqlfiddle 崩溃时:

创建表产品(`name` varchar(30),`category_name` varchar (30),`category_id` 整数);插入产品(`名称`)值('蘑菇')，('蘑菇罐')，('罐头汤'), ('蘑菇汤'),('番茄汤'), ('番茄');创建表过滤器(`filter` varchar(30),`category_name` varchar(30));插入过滤器(`filter`, `category_name`)VALUES ('Can of', '罐头食品'), ('Soup', 'Meals'),('蘑菇', '成分'), ('番茄', '成分');创建表类别(`id` 整数，`名称` varchar(30));插入类别(`id`，`name`)值(1，罐头食品")，(2，成分")，(3，膳食")；

解决方案

您使用 sqlfiddle 以及尝试使用 Select 查询解决问题的尝试使任务变得非常简单.

它按照我猜想的方式工作，我需要做的就是在类别表中再添加一个左联接(IDK 为什么您无法加入类别，因为它工作正常).

所以.我已将您的选择查询编辑如下:

从 Products 中选择 Products.name, Filters.*,Categories.id左连接过滤器在 Products.name LIKE CONCAT('%',Filters.filter,'%')左加入分类在 Categories.name = Filters.category_name按产品分组.名称；

您将通过此查询获得所需的结果.

现在，为了使用此查询的结果更新 Products 表，您可以执行以下操作:

更新产品左连接过滤器在 Products.name LIKE CONCAT('%',Filters.filter,'%')左加入分类在 Categories.name = Filters.category_name设置 Products.category_name = Filters.category_name,Products.category_id = Categories.id;

点击这里查看工作演示

希望对你有帮助！

I have three tables (SQLFiddle with tables created)

Orange text is what I need to get by comparing Products.name with Filters.filter.

I figured out that substring match can be done like this:

on Products.name LIKE CONCAT('%',Filters.filter,'%');

I only need the first filter match to be used. So "Mushroom soup" will match "Soup" and not "Mushroom".

What is best approach for this task?

A tested SQLFiddle link, if possible, please.

What I tried: (feel free to skip everything below)

Tried double join:

update Products
left join Filters on Products.name LIKE CONCAT('%',Filters.filter,'%')
join Categories on Filters.category_name = Categories.name
set Products.category_id = Categories.id, Products.filter = Filters.filter;

But this doesn't result in first match being used, because first join returned all filter matches (multiple lines per 1 product), and after second join categories are from last match.

For example: Mushroom soup got into "Mushroom-Ingredients" filtercategory, instead of "SoupMeals".

Also tried join + order by:

select Products.name, Filters.* 
from Products
left join Filters on Products.name LIKE CONCAT('%',Filters.filter,'%')
group by Products.name;

This returns the first match per product as I needed, but I can't do a second left-join in the same query, neither can I get "group by" to work after "update" function instead of "select".

For when sqlfiddle collapses:

CREATE TABLE Products
(
    `name` varchar(30), 
    `category_name` varchar (30), 
    `category_id` int
);

INSERT INTO Products (`name`)
VALUES ('Mushrooms'), ('Can of mushrooms'),
       ('Can of soup'), ('Mushroom soup'),
       ('Tomato soup'), ('Tomato');

CREATE TABLE Filters
(
     `filter` varchar(30), 
     `category_name` varchar(30)
);

INSERT INTO Filters (`filter`, `category_name`)
VALUES ('Can of', 'Canned food'), ('Soup', 'Meals'),
       ('Mushroom', 'Ingredients'), ('Tomato', 'Ingredients');

CREATE TABLE Categories
(
     `id` int, 
     `name` varchar(30)
);

INSERT INTO Categories (`id`, `name`)
VALUES (1, "Canned food"), (2, 'Ingredients'), (3, 'Meals');

解决方案

You made the task quite easy with your sqlfiddle as well as your attempt to solve the problem with Select query.

It works the way you want I guess and all I need to do is to add one more left join with category table (IDK why you were unable to join Category as it's working properly).

So. I've edited your select query as follows:

select Products.name, Filters.*,Categories.id from Products
left join Filters
on Products.name LIKE CONCAT('%',Filters.filter,'%')
left join Categories
on Categories.name = Filters.category_name
GROUP BY Products.name;

You will get the results you want with this query.

Now, in order to update Products table with the result of this query, you can do following:

update Products
left join Filters
on Products.name LIKE CONCAT('%',Filters.filter,'%')
left join Categories
on Categories.name = Filters.category_name
set Products.category_name = Filters.category_name, 
    Products.category_id = Categories.id;

Click here for Working Demo

Hope it helps!

这篇关于MySQL 使用双左连接更新行，限制第一次匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！