MySQL-使用部分字符串匹配中的表进行JOIN [英] MySQL - JOIN with table on partial string match

问题描述

尝试LEFT JOIN具有部分字符串匹配的表.我试过在LIKE条件下更改通配符，但它仍然只搜索完整的字符串.有什么建议?我已经在表中存储了产品缩写的列表.通过部分匹配来管理此列表，而不是必须拼出每个产品，将变得更加容易.

Trying to LEFT JOIN a table with partial string match. I've tried changing the wildcard in the LIKE condition, but it still only searches for the full string. Any suggestions? I've got a list of product abbreviations stored in a table. Would be easier to manage this list with partial matching versus having to spell out every product.

提前谢谢！

CREATE TABLE `order`
(
  order_id INT (11) NOT NULL,
  shipping_firstname VARCHAR (128) NOT NULL,
  shipping_lastname VARCHAR (128) NOT NULL
  );

CREATE TABLE order_option
(
  order_id integer NOT NULL,
  order_product_id INT (11) NOT NULL,
  `value` VARCHAR (10) NOT NULL
);

CREATE TABLE order_product
(
  order_id INTEGER NOT NULL,
  quantity INT (11) NOT NULL,
  order_product_id INT (11) NOT NULL,
  name VARCHAR (55) NOT NULL
);

CREATE TABLE product_abbreviations
(
  product_name VARCHAR (128) NOT NULL,
  product_abbr VARCHAR (55) NOT NULL
);

INSERT `order`
  (order_id, shipping_firstname, shipping_lastname)
VALUES
  (12345, 'Mason', 'Sklut'),
  (12346, 'John', 'Doe');

INSERT order_option
  (order_id, order_product_id, `value`)
VALUES
  (12345, 101, 'Large'),
  (12345, 101, 'Red'),
  (12346, 102, 'Small'),
  (12346, 102, 'Blue');

INSERT order_product
  (order_id, quantity, order_product_id, name)
VALUES
  (12345, 1, 101, 'T-shirt for Halloween'),
  (12345, 2, 101, 'T-shirt for Spring Break'),
  (12346, 5, 102, 'T-shirt for Pacific West');


INSERT product_abbreviations
  (product_name, product_abbr)
VALUES
  ('T-shirt for', 'Tee'),
  ('Halloween', 'Hallo'),
  ('Spring Break', 'SB'),
  ('Pacific West', 'PW');

LEFT JOIN `product_abbreviations` AS `pa` ON `pa`.`product_name` LIKE CONCAT('%',`op`.`name`,'%')

在此处查看完整查询:
https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=536e2f367cebc053e95709ec7d90 /a>

See full query here:
https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=536e2f367cebc053e95709ec7d90a7aa

预期输出:

Order ID  | Name        | Qty | Option | Size | Product    | Ref
----------------------------------------------------------------------------
12345     | Mason Sklut | 1   | R      | L    | Tee Hallo  | R / Tee Hallo L
12345     | Mason Sklut | 2   | R      | L    | Tee SB     | 2x R / Tee SB L
12346     | John Doe    | 5   | Bl     | S    | Tee PW     | 5x Bl / Tee PW S

推荐答案

我认为您希望模式与其他方式匹配.也就是说，产品名称应包含产品缩写:

I think that you want the pattern matching the other way around. That is, the product name should contain the product abbreviation:

LEFT JOIN `product_abbreviations` AS `pa` 
    ON `op`.`name` LIKE CONCAT('%', `pa`.`product_name` ,'%')

这篇关于MySQL-使用部分字符串匹配中的表进行JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！