SELECT SUBJ_ID
FROM EXAM_MARKS AS EX
WHERE
-- Количество студентов, сдавших более 20 экзаменов
( select count(S_ID) as S__NUM
from (
select STUDENT_ID as S_ID
from EXAM_MARKS
group by STUDENT_ID
HAVING count(MARK)>=20
) as t
) =
(select TOP 1 count(STUDENT_ID)
from dbo.EXAM_MARKS
WHERE STUDENT_ID in ( SELECT STUDENT_ID FROM EXAM_MARKS WHERE SUBJ_ID=EX.SUBJ_ID)
group by STUDENT_ID
HAVING count(MARK)>=20
)
Вложенные подзапросы
Довольно сложный для понимания(моего) запрос. Помогите пожалуйста.
есть таблица EXAM_MARKS
c полями
STUDENT_ID
SUBJ_ID
MARK
EXAM_DATE
Обращаю внимание, что нужно определить кол-во именно тех предметов, которые сдавали все студенты, сдавшие более 20 предметов.
Таким образом можно выделить множество1 студентов, которые сдали более 20 предметов.
И теперь нужно отобрать только те предметы, которые сдавал каждый студент из множества1
Насколько я понимаю, то подобный перебор на SQL не сделать.
Но можно посчитать количество или сумму.
Я пока смог сообразить только над следующей частью
Но что-то в этом запросе не так. Помогите пожалуйста.
MS SQL 2000
есть таблица EXAM_MARKS
c полями
STUDENT_ID
SUBJ_ID
MARK
EXAM_DATE
Обращаю внимание, что нужно определить кол-во именно тех предметов, которые сдавали все студенты, сдавшие более 20 предметов.
Таким образом можно выделить множество1 студентов, которые сдали более 20 предметов.
И теперь нужно отобрать только те предметы, которые сдавал каждый студент из множества1
Насколько я понимаю, то подобный перебор на SQL не сделать.
Но можно посчитать количество или сумму.
Я пока смог сообразить только над следующей частью
Код:
MS SQL 2000
Можно найти список предметов, которые сдавали студенты, сдавшие более 20 предметов. Для этого сделаем такой запрос:
Из этих предметов только часть - сдавали все студенты, сдававшие эти предметы.
Нужно определить количество предметов (т.е. их состав даже не важен),
которые сдавал каждый студент, сдававший более 20 предметов.
Но как быть дальше ??
Загвоздка в том, как проверить, что определенный предмет сдавали все эти студенты?
Можно выбрать список, студентов, которые сдавали определенный предмет.
В таком случае задача сводится к тому, чтобы определить, что
в этот список входят все студенты, которые мы отобрали в предыдущем запросе.
Код:
SELECT DISTINCT EX.SUBJ_ID
FROM EXAM_MARKS AS EX
WHERE EX.STUDENT_ID = ANY
(
SELECT EX2.STUDENT_ID
FROM EXAM_MARKS AS EX2
GROUP BY EX2.STUDENT_ID
HAVING count(EX2.SUBJ_ID) >= 2
)
FROM EXAM_MARKS AS EX
WHERE EX.STUDENT_ID = ANY
(
SELECT EX2.STUDENT_ID
FROM EXAM_MARKS AS EX2
GROUP BY EX2.STUDENT_ID
HAVING count(EX2.SUBJ_ID) >= 2
)
Из этих предметов только часть - сдавали все студенты, сдававшие эти предметы.
Нужно определить количество предметов (т.е. их состав даже не важен),
которые сдавал каждый студент, сдававший более 20 предметов.
Но как быть дальше ??
Загвоздка в том, как проверить, что определенный предмет сдавали все эти студенты?
Можно выбрать список, студентов, которые сдавали определенный предмет.
В таком случае задача сводится к тому, чтобы определить, что
в этот список входят все студенты, которые мы отобрали в предыдущем запросе.
Хм, думаю можно попробовать такой вариант:
Так как базы нет, то размышлял абстрактно, посмотри на месте.
Код:
- включаем в результат только те предметы, количество которых
- равно количеству студентов
SELECT subj_id FROM
- выбираем все предметы успевающих студентов
(SELECT subj_id FROM ex WHERE student_id IN
- выбираем ID успевающих студентов
(SELECT stud_id FROM ex WHERE COUNT(*) >= 20 GROUP BY stud_id) AS stud_pool
) AS subj_pool
WHERE count(*) = COUNT(stud_pool)
GROUP BY subj_pool.subj_id
- равно количеству студентов
SELECT subj_id FROM
- выбираем все предметы успевающих студентов
(SELECT subj_id FROM ex WHERE student_id IN
- выбираем ID успевающих студентов
(SELECT stud_id FROM ex WHERE COUNT(*) >= 20 GROUP BY stud_id) AS stud_pool
) AS subj_pool
WHERE count(*) = COUNT(stud_pool)
GROUP BY subj_pool.subj_id
Так как базы нет, то размышлял абстрактно, посмотри на месте.
тогда уж более правильный вариант:
Кстати, если подразумевается что "сдали" это не просто присутствие в табличке, а оценка выше 2, то надо добавить в условие where Mark > 2
Код:
select
em.SUBJ_ID
from
@EXAM_MARKS em
join (
select STUDENT_ID
from @EXAM_MARKS
group by STUDENT_ID
having count(*) > 20
) st on st.STUDENT_ID = em.STUDENT_ID
group by
em.SUBJ_ID
having
count(*) = (
select count(*) from(
select STUDENT_ID
from @EXAM_MARKS
group by STUDENT_ID
having count(*) > 20) tmp )
em.SUBJ_ID
from
@EXAM_MARKS em
join (
select STUDENT_ID
from @EXAM_MARKS
group by STUDENT_ID
having count(*) > 20
) st on st.STUDENT_ID = em.STUDENT_ID
group by
em.SUBJ_ID
having
count(*) = (
select count(*) from(
select STUDENT_ID
from @EXAM_MARKS
group by STUDENT_ID
having count(*) > 20) tmp )
Кстати, если подразумевается что "сдали" это не просто присутствие в табличке, а оценка выше 2, то надо добавить в условие where Mark > 2