Ïðèâåò. Åñòü òàêîé çàïðîñ.
select simple_raspisanie.DISCIPLINA_ID,simple_vedomost.student_id
,sum(case when simple_vedomost.mark_id = 1 then 1 else 0 end) as mark_id1
,sum(case when simple_vedomost.mark_id = 2 then 1 else 0 end) as mark_id2
,sum(case when simple_vedomost.mark_id = 3 then 1 else 0 end) as mark_id3
,sum(case when simple_vedomost.mark_id = 4 then 1 else 0 end) as mark_id4
,sum(case when simple_vedomost.mark_id = 5 then 1 else 0 end) as mark_id5
,sum(case when simple_vedomost.mark_id = 6 then 1 else 0 end) as mark_id6
,sum(case when simple_vedomost.mark_id = 7 then 1 else 0 end) as mark_id7
,sum(case when simple_vedomost.mark_id = 8 then 1 else 0 end) as mark_id8
,sum(case when simple_vedomost.mark_id = 9 then 1 else 0 end) as mark_id9
, count(propuski.c) asd
, sum(case when SP_PROPUSK_PRICHINA_WHAT_FULL.flag = 4 then
case when sp_propusk_prichina_what_full.para_no =
simple_raspisanie.para then 1 else 0 end
else 0
end)
, sum(case when SP_PROPUSK_PRICHINA_WHAT_FULL.flag = 1 then
case when sp_propusk_prichina_what_full.lesson_type_id =
simple_raspisanie.lesson_type_id then 1 else 0 end
else 0
end)
, sum(case when SP_PROPUSK_PRICHINA_WHAT_FULL.flag = 2 then
case when sp_propusk_prichina_what_full.lesson_type_id =
simple_raspisanie.lesson_type_id then 1 else 0 end
else 0
end)
, sum(case when SP_PROPUSK_PRICHINA_WHAT_FULL.flag = 3 then
case when sp_propusk_prichina_what_full.disciplina_id =
simple_raspisanie.disciplina_id then 1 else 0 end
else 0
end)
from simple_raspisanie
left join simple_vedomost on simple_vedomost.simple_raspisanie_id =
simple_raspisanie.c
left join students_pool on students_pool.c = simple_vedomost.student_id
left join propuski on propuski.student_id = simple_vedomost.student_id and
simple_raspisanie.datee between propuski.from_date and propuski.to_date
left join SP_PROPUSK_PRICHINA_WHAT_FULL on
SP_PROPUSK_PRICHINA_WHAT_FULL.propusk_prichina_id = propuski.prichina_id
where exists(select c from propuski where propuski.student_id =
simple_vedomost.student_id)
group by 2,1
ïðîáëåìà : Åñëè ëèíêîâàòü òàáëèöó SP_PROPUSK_PRICHINA_WHAT_FULL, òî
íåïðàâèëüíî ñ÷èòàåò mark_id1-9.
Ñ÷èòàåò îíà íåïðàâèëüíî ïîòîìó ÷òî â íåé íà îäèí propusk_prichina_id æåò
áûòü íåñêîëüêî çàïèñåé. Åñëè èõ áîëüøå îäíîé - ïîëó÷àåòñÿ íåïðàâèëüíûé
ïîäñ÷åò mark_id1-9.
×òî â çàïðîñå ïîäïðàâèòü ÷òîáû íîðìàëüíî îòðàáîòàë?
Ñ óâàæåíèåì,
Àíäðåé
P.S. Ó ìåíÿ ÷òî-òî ïî÷òà ïëîõî õîäèò. Äðóãîé ïîñò ïîñûëàë íåäåëþ íàçàä - òàê
îí ñäåñü è íå ïîÿâèëñÿ. Êàê ïðîâåðèòü ïî÷òîâèê?
--~--~---------~--~----~------------~-------~--~----~
-~----------~----~----~----~------~----~------~--~---
