主查询详细信息
问题描述:
主表包含ID和PersonName。
课程表包含ID,CourseName。
详细信息表包含ID,MasterID,CourseID,起始日期,结束日期主查询详细信息
我要创建报告,显示人(PERSONNAME),他们把唯一的最后一道菜的名单(所以每个人只列出一次):
PERSONNAME - CourseName - 起始日期 - 结束日期
答
select m.PersonName, c.CourseName
from Master m
join Detail d on d.MasterID = m.ID
join Course c on c.ID = d.CourseID
where d.StartDate = (select max(d2.StartDate)
from Detail d2
where d2.MasterID = m.ID
)
答
Select personname,coursename from details
inner join course on course.id = details.courseid
inner join master on master.id = details.masterid
inner join (select max(startdate) , courseid,masterid
from details group by masterid,courseid) as tb1
on tb1.courseid = details.courseid and tb1.masterid = details.masterid