sqlserver去重

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)