דוגמא: הצג את רשימת החיסורים לכל תלמיד
פתרון 1 – JOIN :
SELECT Talmidim.TalmidName, count(*) as Hisurim
FROM Attendence INNER JOIN
Talmidim ON Attendence.TalmidID = Talmidim.TalmidID
where WasHeThere=0
group by Talmidim.TalmidName
פתרון 2 – תת-שאילתה:
select Talmidim.TalmidName ,
(select count(*)
from Attendence
where WasHeThere=0
and Talmidim.TalmidID=Attendence.TalmidID
) as Hisurim
from Talmidim
פתרון 3 – JOIN עם תת-שאילתה:
select Talmidim.TalmidName ,count (Hisurim.LectureID)
from Talmidim
join
(select TalmidID, LectureID
from Attendence
where WasHeThere=0
) as Hisurim
on Talmidim.TalmidID=Hisurim.TalmidID
group by TalmidName
פתרון 4 – שידרוג לפתרון 2 – מורידים שורות עם אפסים:
select Talmidim.TalmidName ,(select count(*) as aaa
from Attendence
where WasHeThere=0
and Talmidim.TalmidID=Attendence.TalmidID
) as Hisurim
from Talmidim
where TalmidID in
(select TalmidID
from Attendence
where WasHeThere=0
)
פתרון 5 – טבלה זמנית:
select TalmidID,count(*) as hisurim
into #temp
from Attendence
where WasHeThere=0
group by TalmidID
————————————–
select TalmidName ,isnull((select hisurim from #temp where #temp.TalmidID=tt.TalmidID),0)
from Talmidim tt
————————————–
select TalmidName ,hisurim
from Talmidim tt
join #temp on #temp.TalmidID=tt.TalmidID
פתרון 6 – פונקציה סקלארית:
alter function dbo.HisurimByTalmidID
(
@Tid int
)
returns int
as
begin
declare @tozaa int
set @tozaa = (select count(*) as hisurim
from Attendence
where WasHeThere=0
and @Tid=Attendence.TalmidID
)
return @tozaa
end
go
select TalmidName, dbo.HisurimByTalmidID(TalmidID)
from Talmidim
פתרון 7 – פונקציית טבלה פשוטה:
create function dbo.HisurimAllTalmidim()
returns table
as
return (
select TalmidID,count(*) as hisurim
from Attendence
where WasHeThere=0
group by Attendence.TalmidID
)
go
select TalmidName, sss.hisurim
from Talmidim
join dbo.HisurimAllTalmidim() as sss
on Talmidim.TalmidID = sss.TalmidID
פתרון 8 – פונקציה מורכבת:
alter function dbo.HisurimAllTalmidim2()
returns @tozaa table
(
TalmidID int,
hisurim int
)
as
begin
insert @tozaa(TalmidID,hisurim)
select TalmidID,count(*)
from Attendence
where WasHeThere=0
group by Attendence.TalmidID
return
end
go ——————————————————-
select TalmidName, sss.hisurim
from Talmidim
join dbo.HisurimAllTalmidim() as sss
on Talmidim.TalmidID = sss.TalmidID
