SQL: отношение многие ко многим, состояние IN
Java имеет пул строк, в котором Java управляет распределением памяти для объектов String. См. String Pools в Java
Когда вы проверяете (сравниваете) два объекта с помощью оператора ==, он сравнивает равенство адресов в пуле строк. Если два объекта String имеют одинаковые адреса, то он возвращает true, в противном случае false. Но если вы хотите сравнить содержимое двух объектов String, вы должны переопределить метод equals.
equals - фактически метод класса Object, но он переопределяется в класс String и дается новое определение, которое сравнивает содержимое объекта.
Example:
stringObjectOne.equals(stringObjectTwo);
Но помните, что это относится к случаю String. Если вы хотите сравнить регистр без учета регистра, вы должны пойти для метода equalsIgnoreCase класса String.
Давайте посмотрим:
String one = "HELLO";
String two = "HELLO";
String three = new String("HELLO");
String four = "hello";
one == two; // TRUE
one == three; // FALSE
one == four; // FALSE
one.equals(two); // TRUE
one.equals(three); // TRUE
one.equals(four); // FALSE
one.equalsIgnoreCase(four); // TRUE
5 ответов
Вам необходимо расширить свой запрос для всех элементов в списке:
SELECT "transactions".*
FROM "transactions"
WHERE EXISTS (SELECT 1 FROM "items_transactions"
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE "items_transactions".transaction_id = "transactions".id
AND "items".id = <first item in list>)
AND EXISTS (SELECT 1 FROM "items_transactions"
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE "items_transactions".transaction_id = "transactions".id
AND "items".id = <second item in list>)
...
Вы также можете обработать его, используя IN и COUNT DISTINCT , я не уверен, что будет быстрее. Что-то вроде (полностью непроверено):
SELECT "transactions".*
FROM "transactions"
INNER JOIN (SELECT "items_transactions".transaction_id
FROM "items_transactions"
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE "items".id IN (<list of items>)
GROUP BY "items_transactions".transaction_id
HAVING COUNT(DISTINCT "items".id) = <count of items in list>) matches ON transactions.transaction_id = matches.transaction_id
Я думаю, это делает то, что вы хотите.
Я бы поместил список нужных вам товаров в таблицу (подойдет временная) и присоединился к ней. Затем подсчитайте количество разных элементов и сопоставьте это количество с количеством транзакций элементов.
Я привел пример DDL и данных, которые я использовал.
Create table #trans
(
transId int identity(1,1),
trans varchar(10)
)
Create Table #itemTrans
(
transId int,
itemId int
)
Create table #items
(
itemId int identity(1,1),
item varchar(10)
)
Create table #itemsToSelect
(
itemId int
)
Insert Into #trans
Values ('Trans 1')
Insert Into #trans
Values ('Trans 2')
Insert Into #trans
Values ('Trans 3')
Insert Into #Items
Values ('Item 1')
Insert Into #Items
Values ('Item 2')
Insert Into #Items
Values ('Item 3')
Insert Into #Items
Values ('Item 4')
Insert Into #itemTrans
Values (1, 1)
Insert Into #itemTrans
Values (1, 2)
Insert Into #itemTrans
Values (1, 3)
Insert Into #itemTrans
Values (2, 1)
Insert Into #itemTrans
Values (2, 3)
Insert Into #itemTrans
Values (3, 4)
Insert Into #itemsToSelect
Values (1)
Insert Into #itemsToSelect
Values (2)
Insert Into #itemsToSelect
Values (3)
Select t.transId
From #items i
Join #itemTrans it on i.itemId = it.itemId
Join #trans t on it.transId = t.transId
Join #itemsToSelect its on it.ItemId = its.ItemId
Where it.TransId is not null
Group by t.transId
Having count(distinct(it.itemId)) = (Select count(distinct(itemId)) from #itemsToSelect)
Я не выполнял это, но это должно дать вам желаемый результат:
SELECT t.* FROM items i
INNER JOIN items_transactions it ON i.id = it.item_id
INNER JOIN transactions t ON it.transaction_id = t.id
WHERE i.id IN (1,2,3)
Последний бит запроса выглядит неправильно:
WHERE (items.id IN (<list of items>))
оператор in похож на большой оператор OR, а не на оператор AND, поэтому оптимизатор расширяет его как:
WHERE (items.id = 123 OR items.id = 456 OR items.id = 789)
EDIT
Я считаю, что вам нужно выполнить коррелированный подзапрос к таблице элементов.
SELECT transactions.*
WHERE (SELECT count(*)
FROM items_transactions
WHERE items_transactions.transaction_id = transactions.transaction_id
AND items_transactions.item_id IN (<list of items>)
) = <number of items>
Хотя это, вероятно, будет делать сканирование транзакций, вложенный коррелированный подзапрос для каждой из них... не особенно эффективно, так что, может быть:
SELECT transactions.*
WHERE EXISTS (SELECT 1 FROM items_transactions
WHERE items_transactions.transaction_id = transactions.transaction_id
AND items_transactions.item_id IN (<list of items>)
)
AND
(SELECT count(*)
FROM items_transactions
WHERE items_transactions.transaction_id = transactions.transaction_id
AND items_transactions.item_id IN (<list of items>)
) = <number of items>
или что-то подобное, чтобы убедить БД сначала найти транзакции, связанные хотя бы с одним из элементов, а затем проверить, что каждая транзакция связана со всеми элементами.
Как кто-то заметил, вы также можете просто генерировать предложения join для каждого элемента, что может быть лучше, если количество элементов не велико.