Как я могу выполнить условное соединение на основе совпадения с подстановочными знаками в mysql?
У меня есть две таблицы значений, первая содержит (среди прочего) номера моделей продуктов, а вторая содержит информацию о гарантиях. У меня нет исчерпывающего списка всех продуктов (частично из-за того, что будут выпущены новые продукты, о которых я не буду знать, но мне нужно, чтобы заявленная гарантия была точной), поэтому вторая таблица содержит значения для подстановочного знака. совпадение, напр. что-то вроде «1080», «HD», «4K» и т. д. Я знаю, что продукты с определенными номерами моделей имеют определенные гарантии, связанные с ними, т. е. продукты 1080 или HD имеют 3-летнюю гарантию, в то время как 4K ( и другие) равны 5 (например).
Мое текущее немасштабируемое решение состоит в использовании оператора CASE, но для этого требуется отдельная строка для оператора case для каждого совпадения подстановочных знаков известной модели; это означает, что ETL устареет, потенциально быстро, что проблематично.
CREATE TABLE sales AS
MODEL varchar(255), sold_date as DATETIME;
INSERT INTO sales (model, sold_date)
VALUES ('PANASONIC_HD_ANDROID', '2020-01-01');
INSERT INTO sales (model, sold_date)
VALUES ('PANASONIC_1080_ANDROID', '2020-01-01');
INSERT INTO sales (model, sold_date)
VALUES ('SONY_HD_ROKU', '2020-01-01');
INSERT INTO sales (model, sold_date)
VALUES ('SONY_1080_ROKU', '2020-01-01');
INSERT INTO sales (model, sold_date)
VALUES ('SONY_4K_ROKU', '2020-01-01');
CREATE TABLE warranties AS
model_wildcard VARCHAR(255),
warranty_length INT;
INSERT INTO warranties (model_wildcard, warranty_length) VALUES ('HD', 3);
INSERT INTO warranties (model_wildcard, warranty_length) VALUES ('4K', 5);
INSERT INTO warranties (model_wildcard, warranty_length) VALUES ('1080', 3);
То, что я хочу сделать, это что-то вроде этого
SELECT sales.model, sales.sold_date, DATE_ADD(sales.sold_date, INTERVAL S.warranty_length)
FROM sales as S
LEFT JOIN warranties W on S.model LIKE W.model_wildcard
что, очевидно, не сработает, но я думаю, что это передает идею того, что я пытаюсь сделать.
Ответы 1
SELECT *,
ss.sold_date + INTERVAL ww.warranty_length YEAR warranty_till
FROM sales ss
LEFT JOIN warranties ww ON LOCATE(ww.model_wildcard, ss.model);
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5b8777058c71635e1b082fddf8eb902b
Вы должны понимать, что возможно, что имя модели не соответствует какому-либо шаблону (используется LEFT JOIN, в соответствующих столбцах будет возвращено NULL). Или, наоборот, он может соответствовать более чем одному шаблону (будут возвращены все совпадения).
PS. ON INSTR(ss.model, ww.model_wildcard) также можно использовать.
LIKE без подстановочных знаков в шаблоне выполняет прямое сравнение (т. е. действует как псевдоним оператора
=). Если вы хотите использовать его, вы должны сохранить такие значения, как'%HD%'вwarranties.warranty_length. Или добавьте символы процента с помощью CONCAT() в запросе.