Найти записи как И в MYSQL
Я хочу найти идентификатор студента, который должен иметь (professorId = 3 и CourseId = 4) и (professorId = 3 и CourseId = 2) и (professorId = 5 и CourseId = 8). иногда эта скобка равна одной или более 20
У меня есть два настольных класса и классы студентов, подобные этому:
Таблица студентовКлассы:
id | studentId | classId
1 1 2
2 1 3
3 1 4
4 2 3
5 2 5
6 3 2
7 4 3
8 4 2
9 1 8
Таблица классов:
id | professorId | CourseId
1 1 1
2 3 4
3 2 5
4 3 2
5 4 2
6 2 3
7 5 6
8 5 8
9 5 9
Было бы очень просто, если бы каждая скобка работала как или. Но я хочу найти ученика, у которого есть все эти вещи в виде и.
Этот запрос представляет собой сценарий «ИЛИ»:
select `studentsClasses`.`studentId` from `studentsClasses`
inner join `class` on (`studentsClasses`.`classId`=`class`.`id`
AND ((professorId = 3 And courseId = 4) OR (professorId = 3 And courseId = 2)
OR (professorId = 5 And courseId = 8)))
Но я хочу И такой сценарий:
select `studentsClasses`.`studentId` from `studentsClasses`
inner join `class` on (`studentsClasses`.`classId`=`class`.`id`
AND ((professorId = 3 And courseId = 4) AND (professorId = 3 And courseId = 2)
AND (professorId = 5 And courseId = 8)))
Но приведенный выше запрос неверен.
Например, результат запроса с этим значением должен быть:
studentId
---------
1
Эти скобки можно изменить. например, когда вы устанавливаете флажок «Фильтры» @mitkosoft
Ответы 3
Один из способов — использовать условие CASE WHEN и SUM над ним.
SELECT
studentId,
SUM(
CASE
WHEN c.professorId = 3 AND c.courseId = 4 THEN 1
WHEN c.professorId = 3 AND c.courseId = 2 THEN 1
WHEN c.professorId = 5 AND c.courseId = 8 THEN 1
ELSE 0
END) AS filter_cnt
FROM studentsClasses s
JOIN class c ON c.id = s.classId
GROUP BY 1
HAVING filter_cnt = 3
Другой способ — использовать отдельное соединение для каждого условия. Неэффективное решение, но может подойти, если данные не слишком велики.
WITH class_1 AS (
SELECT DISTINCT studentId FROM studentsClasses s
JOIN class c ON c.id = s.classId AND c.professorId = 3 AND c.courseId = 4
),
class_2 AS (
SELECT DISTINCT studentId FROM studentsClasses s
JOIN class c ON c.id = s.classId AND c.professorId = 3 AND c.courseId = 2
),
class_3 AS (
SELECT DISTINCT studentId FROM studentsClasses s
JOIN class c ON c.id = s.classId AND c.professorId = 5 AND c.courseId = 8
)
SELECT c1.studentId FROM class_1 c1
JOIN class_2 c2 ON c2.studentId = c1.studentId
JOIN class_3 c3 ON c3.studentId = c1.studentId
Спасибо @Климент Мерзляков. К сожалению, если я использую этот метод, из-за большого объема данных, а также большого количества соединений скорость запроса данных будет низкой, возможно, до 20 соединений.
О да, я понял. Я бы еще попробовал. Я не уверен, что есть лучшее решение. Это хороший вопрос, я подумаю над ним и сообщу, если что-нибудь возникнет. Также интересно, есть ли у кого-нибудь хорошее решение.
Сколько у вас уникальных комбинаций proffesorId и courseId? Если он невелик, то можно попробовать что-нибудь со сводными таблицами.
Я думаю около 1000 уникальных комбинаций. Однако, пожалуйста, предоставьте и этот метод. По крайней мере, если мне это не подходит, то подойдет и другим людям с меньшими данными.
О, на самом деле решение с соединениями не работает
Обновлено, решение с объединениями теперь должно работать.
Хорошо, @farzad, я придумал другое решение — использовать CASE WHEN. Обновил ответ. Не могли бы вы это проверить?
спасибо большое @Климент Мерзляков . Вы предложили замечательный метод.
Просто сделайте GROUP BY. Используйте HAVING, чтобы убедиться, что присутствуют все три комбинации профессор/курс.
SELECT s.studentId
FROM studentsClasses s
JOIN class c ON c.id = s.classId
WHERE (c.professorId = 3 AND c.courseId = 4)
OR (c.professorId = 3 AND c.courseId = 2)
OR (c.professorId = 5 AND c.courseId = 8)
GROUP BY s.studentId
HAVING COUNT(DISTINCT c.courseId) = 3
Благодаря COUNT(DISTINCT c.courseId) это будет работать, даже если ученик посещает один и тот же урок несколько раз.
Демо: https://dbfiddle.uk/XrbiAbNh (пролистните до конца).
Есть две таблицы, связанные идентификатором класса, студент не находится в той же таблице, что и профессор/курс.
Сейчас обновлено. Присоединиться добавлено
Один из вариантов — использовать оператор IN для пары столбцов, группируя их по учащимся. HAVING Count() = Количество перечисленных пар — 3 в этом примере и до 20 или независимо от того, что это такое в вашем реальном контексте...
WITH -- S a m p l e D a t a :
studentsClasses ( id, studentId, classId ) AS
( Select 1, 1, 2 Union All
Select 2, 1, 3 Union All
Select 3, 1, 4 Union All
Select 4, 2, 3 Union All
Select 5, 2, 5 Union All
Select 6, 3, 2 Union All
Select 7, 4, 3 Union All
Select 8, 4, 2 Union All
Select 9, 1, 8
),
Class ( id, professorId, CourseId ) AS
( Select 1, 1, 1 Union All
Select 2, 3, 4 Union All
Select 3, 2, 5 Union All
Select 4, 3, 2 Union All
Select 5, 4, 2 Union All
Select 6, 2, 3 Union All
Select 7, 5, 6 Union All
Select 8, 5, 8 Union All
Select 9, 5, 9
)
-- S Q L :
Select s.studentId
From studentsClasses s
Inner Join Class c ON c.id = s.classId
Where (c.professorId, c.CourseId) IN( (3, 4), (3, 2), (5, 8) )
Group By 1
Having Count(s.studentId) = 3 -- 3 is number of pairs matched
/* R e s u l t :
studentId
---------
1 */
См. скрипку здесь.
Можете ли вы уточнить и показать желаемый результат? Кроме того, вам нужен общий запрос или конкретный, который точно соответствует
AND ((professorId = 3 And courseId = 4) AND (professorId = 3 And courseId = 2) AND (professorId = 5 And courseId = 8)))критериям? Не уверен, каким должен быть результат, если у вас есть «иногда эта скобка равна одному или более 20».