Помогите с запросом, пожалуйста

XXL

Дата: 21.01.2009 10:32:22

Здравствуйте.
У меня имеется запрос, выводящий все оценки студента. А мне нужно показать только максимальные оценки. Пока у меня не получается это сделать.
Вот запрос:

SELECT dbo.Disciplina.disciplina_name, dbo.TreaningLoadVid.treaningloadvid_name,
dbo.Ocenka.ocenka_chislo, dbo.Ocenka.ocenka_propshort,
dbo.VedomostContent.ocenka_data,dbo.VedomostContent.id_rekvstudent, dbo.VedomostContent.id_vedomostcontent,  dbo.VedomostTitul.id_treaningloadvid, 
dbo.VedomostTitul.id_vedomosttitul, dbo.VedomostTitul.vedomosttitul_numbe, dbo.VedomostTitul.vedomosttitul_status,
dbo.VedomostTitul.ugodB, dbo.VedomostTitul.semestr, dbo.VedomostTitul.vedomosttitul_data, dbo.VedomostTitul.id_disciplina
FROM dbo.VedomostContent INNER JOIN
dbo.Ocenka ON dbo.VedomostContent.id_ocenka = dbo.Ocenka.id_ocenka INNER JOIN 
dbo.VedomostTitul ON dbo.VedomostContent.id_vedomosttitul = dbo.VedomostTitul.id_vedomosttitul INNER Join 
dbo.Disciplina ON dbo.VedomostTitul.id_disciplina = dbo.Disciplina.id_disciplina INNER JOIN 
dbo.TreaningLoadVid ON dbo.VedomostTitul.id_treaningloadvid = dbo.TreaningLoadVid.id_treaningloadvid
WHERE dbo.VedomostContent.id_rekvstudent = 'c1bc70c8-6615-4402-b969-49c5c4a20cd9' 
AND dbo.VedomostTitul.semestr = '1'
ORDER BY dbo.Disciplina.disciplina_name

автор

В общем, мне нужно, чтобы по математике выводилась только одна строка, где оценка "4".
Если кто подскажет, буду очень благодарна:))

Паганель

Дата: 21.01.2009 10:37:44

Как-то так:

declare @t table(student_id int, predmet_id int, ocenka int)
insert into @t(student_id, predmet_id, ocenka)
select 1, 1, 4 union all
select 1, 2, 3 union all
select 1, 2, 4 union all
select 1, 2, 4 union all
select 2, 1, 3 union all
select 2, 2, 4 union all
select 3, 1, 3

select top 1 with ties *
  from @t
 order by row_number() over(partition by student_id, predmet_id order by ocenka desc)

student_id  predmet_id  ocenka
----------- ----------- -----------
1           1           4
1           2           4
2           1           3
2           2           4
3           1           3

(5 row(s) affected)

el Pueblo

Дата: 21.01.2009 10:41:18

2 XXL
И опять таки версию сервера надо угадать :)

XXL

Дата: 21.01.2009 10:47:46

el Pueblo

2 XXL
И опять таки версию сервера надо угадать :)

SQL 2005

SergSuper

Дата: 21.01.2009 11:44:24

Паганель

Как-то так:

declare @t table(student_id int, predmet_id int, ocenka int)
insert into @t(student_id, predmet_id, ocenka)
select 1, 1, 4 union all
select 1, 2, 3 union all
select 1, 2, 4 union all
select 1, 2, 4 union all
select 2, 1, 3 union all
select 2, 2, 4 union all
select 3, 1, 3

select top 1 with ties *
  from @t
 order by row_number() over(partition by student_id, predmet_id order by ocenka desc)

student_id  predmet_id  ocenka
----------- ----------- -----------
1           1           4
1           2           4
2           1           3
2           2           4
3           1           3

(5 row(s) affected)

а почему с MAX-ом нельзя?

select student_id,  predmet_id, max(ocenka) from @t group by student_id,  predmet_id

Паганель

Дата: 21.01.2009 11:46:41

SergSuper

а почему с MAX-ом нельзя?

В моем примере - можно
В задаче автора - вряд ли, там еще даты какие-то...

iap

Дата: 21.01.2009 11:46:42

SergSuper

а почему с MAX-ом нельзя?

select student_id,  predmet_id, max(ocenka) from @t group by student_id,  predmet_id

А с остальными полями что делать будете?

WarAnt

Дата: 21.01.2009 12:38:50

SELECT dbo.Disciplina.disciplina_name, dbo.TreaningLoadVid.treaningloadvid_name,
dbo.Ocenka.ocenka_chislo, dbo.Ocenka.ocenka_propshort,
dbo.VedomostContent.ocenka_data,dbo.VedomostContent.id_rekvstudent, dbo.VedomostContent.id_vedomostcontent, dbo.VedomostTitul.id_treaningloadvid,
dbo.VedomostTitul.id_vedomosttitul, dbo.VedomostTitul.vedomosttitul_numbe, dbo.VedomostTitul.vedomosttitul_status,
dbo.VedomostTitul.ugodB, dbo.VedomostTitul.semestr, dbo.VedomostTitul.vedomosttitul_data, dbo.VedomostTitul.id_disciplina
FROM dbo.VedomostContent INNER JOIN
dbo.Ocenka ON dbo.VedomostContent.id_ocenka = dbo.Ocenka.id_ocenka INNER JOIN
dbo.VedomostTitul ON dbo.VedomostContent.id_vedomosttitul = dbo.VedomostTitul.id_vedomosttitul INNER Join
dbo.Disciplina ON dbo.VedomostTitul.id_disciplina = dbo.Disciplina.id_disciplina INNER JOIN
dbo.TreaningLoadVid ON dbo.VedomostTitul.id_treaningloadvid = dbo.TreaningLoadVid.id_treaningloadvid

(SELECT max(ocenka_data) ocenka_data, ocenka_chislo, id_rekvstudent, id_disciplina
FROM VedomostContent vc
JOIN Ocenka oc ON vc.id_ocenka = oc.id_ocenka
JOIN VedomostTitul vt ON vt.id_vedomosttitul = vc.id_vedomosttitul
JOIN (SELECT max(ocenka_chislo) ocenka_chislo,id_rekvstudent, id_disciplina FROM
VedomostContent vc
JOIN Ocenka oc ON vc.id_ocenka = oc.id_ocenka
JOIN VedomostTitul vt ON vt.id_vedomosttitul = vc.id_vedomosttitul
GROUP BY id_rekvstudent, id_disciplina) a ON
a.ocenka_chislo = oc.ocenka_chislo AND
a.id_rekvstudent = vc.id_rekvstudent AND
a.id_disciplina=vt.id_disciplina
GROUP BY ocenka_chislo, id_rekvstudent, id_disciplina) mo ON
mo.ocenka_chislo = Ocenka.ocenka_chislo AND
mo.id_rekvstudent = VedomostContent.id_rekvstudent AND
mo.id_disciplina=VedomostTitul.id_disciplina

WHERE dbo.VedomostContent.id_rekvstudent = 'c1bc70c8-6615-4402-b969-49c5c4a20cd9'
AND dbo.VedomostTitul.semestr = '1'
ORDER BY dbo.Disciplina.disciplina_name

както так:)

WarAnt

Дата: 21.01.2009 12:40:35

забыл join к подзапросу добавить:)

XXL

Дата: 21.01.2009 14:03:12

WarAnt

автор