Блог

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

 col 1   ;  col 2
-----     -----
  a     ----    b
  a     ----    h
  a     ----    d
  b     ----    f
  b     ----    z
  c     ----    z
  c     ----    d
  c     ----    b
  

Товары в столбце 1 не могут быть проданы вместе с товарами в столбце 2.

вторая таблица по сути является таблицей «заказов». Для каждой транзакции есть номера транзакций и номера строк. в каждой строке транзакции есть проданный товар. Обычно за транзакцию продается много товаров. Таблица настроена аналогично этому:

 trans # ;    trans line  ;   item
-------     -----------    -----
12345    ----       1    ----        a
12345      ----     2      ----      b
12345       ----    3    ----        a
45678      ----     1   ----         z
45678       ----    2      ----      f
  

То, что я пытаюсь сделать, это взять все данные транзакции и согласовать их с данными из списка неподходящих комбинаций элементов. Как вы можете видеть, транзакция 12345 нарушает первое правило, потому что ‘a’ продается с ‘b’. Это общая идея.

 SELECT * FROM orders ord1, orders ord2, conditions con 
WHERE ord1.trans = ord2.trans 
AND ord1.item = con.Product1 AND ord2.item = con.Product2
  

Я думаю, что это своего рода решение, в котором первая таблица НЕСОВМЕСТИМА, а вторая таблица УПОРЯДОЧЕНА. приведенный ниже запрос выдаст вам результат, однако в вашей фактической базе данных вам могут потребоваться некоторые изменения:

 select o1.item   '@'   o2.item
from orders o1 full join  orders o2 on o1.orderid = o2.orderid
where exists (select * from incompat i where o1.item   '@'   o2.item = i.col1   '@'   i.col2)
  

Вы можете перечислить все возможные пары в транзакции, объединив транзакции по их идентификатору. Затем проверьте, не нарушает ли какая-либо пара правило

 select distinct a.id
from transactions a
join transactions b on a.id = b.id and a.line_num <> b.line_num
join inappropriates c on a.item = c.col1 and b.item = c.col2;
  

Как насчет использования самостоятельного полного объединения в таблице 2, чтобы получить все комбинации в каждом порядке. Затем просто внутреннее соединение с таблицей 1.

 select * into #t1 from
(
    select 'a' as col1, 'b' as col2
    union
    select 'a' as col1, 'h' as col2
    union
    select 'a' as col1, 'd' as col2
) q


select * into #t2 from
(
select 1 as trans, 1 as line, 'a' as item
union
select 1 as trans, 2 as line, 'b' as item
union
select 1 as trans, 3 as line, 'a' as item
union
select 2 as trans, 1 as line, 'z' as item
union
select 2 as trans, 2 as line, 'f' as item
) q

select o.* from #t1 as v
inner join
(
    select distinct a.trans, a.item as item1, b.item as item2
    from #t2 as a
    full join #t2 as b on a.trans = b.trans and a.line <> b.line
) as o on v.col1 = o.item1 and v.col2 = o.item2
  

Просто, просто используйте предложение EXISTS .

 SELECT
  COUNT(*) 
FROM 
  Trans AS Trans1 
JOIN Trans AS Trans2 ON 
  Trans1.transNb = Trans2.transNb 
WHERE CONCAT(Trans1.item, '|', Trans2.item) IN 
  (SELECT CONCAT(item1, '|', item2) FROM xCombination) 
  

Если значение COUNT отличается от 0, то у вас недопустимая комбинация в порядке.

В основном мы выполняем декартово произведение всех элементов в том же порядке с Trans1 и Trans2. Затем мы объединяем каждую комбинацию двух элементов в одной строке с разделителем. Если строка может быть найдена в таблице xCombination, то в этом порядке что-то не так.

Вот скрипка SQL.

Вот запрос выбора:

Код для создания таблицы и вставки значений

 Create Table InvalidCombos(item1 int, item2 int)

Create Table TransactionInfo(transactionId int, item int)

Insert Into InvalidCombos Values(1,2), (1,8), (1,4), (2,6), (2,26), (3,26), (3,4), (3,2)

Insert Into TransactionInfo Values(12345,1), (12345,2), (45678,26), (45678,6)
  

Вот инструкция select с результатами:

 Select ic.item1, ic.item2, transinfo.transactionId From invalidcombos ic 
  INNER JOIN ( 
    Select ti1.transactionId, ti1.item as item1, ti2.item as item2 
      From TransactionInfo ti1 
      LEFT JOIN TransactionInfo ti2 ON ti1.transactionid = ti2.transactionid 
      and ti1.item <> ti2.item
    )
    transinfo ON (transinfo.item1 = ic.item1 AND transinfo.item2 = ic.item2)