Отфильтруйте нефильтрованный стол против белого списка

Вступление

Привет, я в настоящее время пытаюсь построить систему фильтрации на основе MySQL, и я столкнулся с проблемой сейчас, как это сделать. В приведенной ниже ссылке на образец таблицы я предоставил две таблицы: есть таблица белых списков, которая содержит элемент из белого списка, и несортированная таблица, представляющая собой список элементов, которые будут проверяться по белому списку.

PS: белый список время от времени меняется

Эта проблема

Если бы это было просто совпадение, если три столбца совпадают точно, это была бы очень простая работа, однако проблема в том, что если столбцы в белом списке имеют значение NULL, то это означает, что это подстановочный знак (это поведение можно изменить, если кто-то может предоставить лучшие способы сделать это), то есть любое значение в этом столбце будет принято. Все три столбца должны пройти, прежде чем запись будет принята.

И вот где он застревает =\

пример

Пример таблицы: http://rextester.com/HMYT21528 или ниже (тоже самое)

-- unsorted data

create table unsorted (
    ID int AUTO_INCREMENT,
    country char(10) null,
    region  char(10) null,
    item    char(10),
    PRIMARY KEY (ID)
);

insert into unsorted(country, region, item) VALUES 

(null, null, "Apple"), (null, "East", "Apple"), (null, "West", "Apple"), 
("US", null, "Apple"), ("US", "East", "Apple"), ("US", "West", "Apple"), 
("CHINA", null, "Apple"), ("CHINA", "East", "Apple"), ("CHINA", "West", "Apple"),

(null, null, "Banana"), (null, "East", "Banana"), (null, "West", "Banana"), 
("US", null, "Banana"), ("US", "East", "Banana"), ("US", "West", "Banana"),
("CHINA", null, "Banana"), ("CHINA", "East", "Banana"), ("CHINA", "West", "Banana"),

(null, null, "Cat"), (null, "East", "Cat"), (null, "West", "Cat"), 
("US", null, "Cat"), ("US", "East", "Cat"), ("US", "West", "Cat"),
("CHINA", null, "Cat"), ("CHINA", "East", "Cat"), ("CHINA", "West", "Cat"),


(null, null, "Donkey"), (null, "East", "Donkey"), (null, "West", "Donkey"),
("US", null, "Donkey"), ("US", "East", "Donkey"), ("US", "West", "Donkey"), 
("CHINA", null, "Donkey"), ("CHINA", "East", "Donkey"), ("CHINA", "West", "Donkey");

-- white list data

create table whitelist (
    country char(10) null,
    region  char(10) null,
    item    char(10) not null
);

insert into whitelist(country, region, item) VALUES 

("US", "West", "Apple"), 
(null, "East", "Banana"), 
("CHINA", null, "Cat"), 
(null, null, "Donkey");

В белом списке есть столбец, который выглядит следующим образом: [Страна: Нуль, Регион: Восток, Предмет: Банан]

Это означает, что для любой записи в несортированном списке, если элемент Банан из Восточного региона, мы примем его.

Другим примером может быть [Страна: ноль, Регион: ноль, Предмет: Осел]

Это означает, что мы возьмем любого осла из любой страны или региона, потому что почему бы и нет?

Ожидаемый результат

Из приведенного выше примера мой ожидаемый вывод из списка будет следующим

[ 6, US, West Apple ]

[ 11, Null, East, Banana ]
[ 14, US, East, Banana ]
[ 17, China, East, Banana ]

[ 25, China, Null, Cat ]
[ 26, China, East, Cat ]
[ 27, China, West, Cat ]

[ 28, Null, Null, Donkey ]
[ 29, Null, East, Donkey ]
[ 30, Null, West, Donkey ]
[ 31, US, Null, Donkey ]
[ 32, US, East, Donkey ]
[ 33, US, West, Donkey ]
[ 34, China, Null, Donkey ]
[ 35, China, East, Donkey ]
[ 36, China, West, Donkey ]

Изменить: ответить на комментарии

@ Слоан Трэшер

SELECT a.*
FROM `unsorted` a
JOIN `whitelist` b
ON IF(b.`country` IS NULL,true,a.`country` = b.`country`) AND
    IF(b.`region` IS NULL,true,a.`region` = b.`region`) AND
    IF(b.`item` IS NULL,true,a.`item` = b.`item`);

Поскольку в белом списке нет столбца 'id', поэтому предложение where может вызвать ошибку, а select должен выбирать несортированные для результата; если бы я выбрал таблицу b, я бы увидел только фильтры вместо отфильтрованные данные, которые я хотел бы видеть.

Источник

3 ответа

Решение

Я думаю, что это должно сделать то, что вы просите:

SELECT
    b.*
FROM `unsorted` a
JOIN `whitelist` b
ON IF(b.`country` IS NULL,true,a.`country` = b.`country`) AND
    IF(b.`region` IS NULL,true,a.`region` = b.`region`) AND
    IF(b.`item` IS NULL,true,a.`item` = b.`item`)
WHERE NOT b.`id` IS NULL;
Источник

Я думаю, что вы можете делать то, что вы хотите с exists подзапрос:

select u.*
from unsorted u
where exists (select 1
              from whitelist wl
              where (wl.country = u.country or wl.country is null) and
                    (wl.region = u.region or wl.region is null) and
                    (wl.item = u.item or wl.item is null)
             );
Источник

Вот еще один способ написать это:

SELECT a.*
FROM unsorted a JOIN whitelist b
 ON a.country <=> COALESCE(b.country, a.country)
AND a.region  <=> COALESCE(b.region, a.region)
AND a.item    <=> COALESCE(b.item, a.item)

Логика похожа на ответ @SloanThrasher, но использует несколько разных логических операторов.

Источник
Другие вопросы по тегам