--select u.lastname,sum(pt.point)as sumPoint --from [user] u --join [UserPoint] upt --on u.userId = up.userId --join pointtype pt on pt.pointtypecode = upt.pointtypecode --group by u.lastname
with NamesAndSumPoint (lastName ,sumPoint) AS
( select u.lastname,sum(pt.point)as sumPoint from [user] u join [UserPoint] up on u.userId = up.userId join pointtype pt on pt.pointtypecode = up.pointtypecode group by u.lastname )
select lastName, max(sumPoint) AS maximom from NamesAndSumPoint group by lastName
برای نمایش آواتار خود در این وبلاگ در سایت Gravatar.com ثبت نام کنید. (راهنما)
جواب تمرین CTE
use CMS
go
--select u.lastname,sum(pt.point)as sumPoint
--from [user] u
--join [UserPoint] upt
--on u.userId = up.userId
--join pointtype pt on pt.pointtypecode = upt.pointtypecode
--group by u.lastname
with NamesAndSumPoint (lastName ,sumPoint)
AS
(
select u.lastname,sum(pt.point)as sumPoint
from [user] u
join [UserPoint] up
on u.userId = up.userId
join pointtype pt on pt.pointtypecode = up.pointtypecode
group by u.lastname
)
select lastName, max(sumPoint) AS maximom
from NamesAndSumPoint
group by lastName