1、非主键去重
select p.RoomId,ps.PName,ps.id from Projector p left join ProjectorSub ps on ps.ProjectorID=p.Id where 1=1 and roomid='10'
去重后:
步骤一:
select ps.PName,max(ps.id)psid from Projector p left join ProjectorSub ps on ps.ProjectorID=p.Id where 1=1 and roomid='10' group by PName
步骤二:
select p.RoomId,ps.PName,ps.id from Projector p left join ProjectorSub ps on ps.ProjectorID=p.Id where 1=1 and roomid='10' and ps.id in( select max(ps.id)psid from Projector p left join ProjectorSub ps on ps.ProjectorID=p.Id where 1=1 and roomid='10' group by PName)