Как эффективно получить пересекающиеся значения в одной таблице?
Предположим, у меня есть следующая таблица some_table, которая выглядит так:
email | friend_email |
----------------------
s1 | f1 |
----------------------
s2 | f1 |
----------------------
s1 | f2 |
----------------------
s2 | f2 |
----------------------
s3 | f2 |
----------------------
s1 | f3 |
----------------------
s2 | f3 |
----------------------
s4 | f3 |
Затем я хочу найти отдельные и общие значения в разделе «электронная почта» между f1, f2 и f3. Возвращаемый результат должен быть только s1 и s2.
Используемый оператор SQL будет выглядеть так:
SELECT DISTINCT email FROM some_table WHERE friend_email IN ('f1') AND email in (SELECT DISTINCT email FROM some_table WHERE friend_email IN ('f2')) AND email in (SELECT DISTINCT email FROM some_table WHERE friend_email IN ('f3'));
Проблема в том, что он станет очень длинным, когда у меня будет больше friend_emails, которые нужно добавить.
В моем коде node.js я хочу использовать '?' (я думаю, что они называются константами?) в операторах sql, но мне это не удалось. Пример кода ниже,
var getCommonFriends = "SELECT DISTINCT email FROM some_table WHERE friend_email = '?'";
var getCommonFriendsAppend = " AND email in (SELECT DISTINCT email FROM some_table WHERE friend_email = '?')";
var friends = ["f1", "f2", "f3"];
var combinedSqlStatement = getCommonFriends;
var totalFriends = friends.length;
for(var i = 0; i < totalFriends; i++){
combinedSqlStatement = combinedSqlStatement + getCommonFriendsAppend;
}
Мой вопрос в том, как мне придумать оператор sql, который выполняет вышеуказанное и использует константы, известные как '?'
Ответы 1
Я думаю, вы могли бы сделать здесь простой агрегационный запрос:
SELECT email
FROM some_table
WHERE friend_email IN ('f1', 'f2', 'f3')
GROUP BY email
HAVING COUNT(DISTINCT friend_email) = 3;
Эта опция агрегируется по элементам электронной почты, а затем утверждает, что все определенные группы друзей присутствуют / связаны с этим элементом. Это решение хорошо масштабируется, потому что для расширения круга друзей вам нужно только добавить еще одного друга в предложение IN и изменить утверждение счетчика отдельных в конце запроса.
Демо
Вот версия запроса, которая может быть более удобной в Node JS:
SELECT email
FROM some_table
WHERE friend_email IN
(
SELECT ? FROM dual UNION ALL
SELECT ? FROM dual UNION ALL
SELECT ? FROM dual
)
GROUP BY email
HAVING COUNT(DISTINCT friend_email) = 3;
Затем свяжите буквальные значения 'f1', 'f2' и 'f3' с указанным выше запросом.