请问下面这段sql如何去重,现在的问题是一个house有几个picture了查出来得就有几个重复结果select top 5 p.ID,p.HouseId,p.LastUpdateTime,p.FileUrl,z.AreaName as Zone,e.AreaName as Estate,c.PropName as Construction,h.Title as Title,h.Floor as Floor,h.TotalFloor as TotalFloor,h.Area as Area,h.Price as Price,pic.PicUrl as PicUrl
FROM tb_HousesPubed p
left join tb_HousesSell h on p.HouseId=h.HouseId
left join tb_Area z on h.Zone=z.Id
left join tb_Area e on h.Estate=e.Id
left join tb_HouseProperties c on h.Construction=c.ID
left join tb_PicHouse pic on pic.HouseId=h.HouseId
where h.IsPicture='True'
order by p.Hit desc
FROM tb_HousesPubed p
left join tb_HousesSell h on p.HouseId=h.HouseId
left join tb_Area z on h.Zone=z.Id
left join tb_Area e on h.Estate=e.Id
left join tb_HouseProperties c on h.Construction=c.ID
left join tb_PicHouse pic on pic.HouseId=h.HouseId
where h.IsPicture='True'
order by p.Hit desc
现在这条语句查出来最后一个字段是不同了,也就是前面多个字段重复但是最后一个字段是不重复的,所以我用group by好像也没什么用,有没有人说说改怎么个思路解决?
MAX(pic.PicUrl) as PicUrl然后在加group by
select top 5 p.ID,p.HouseId,p.LastUpdateTime,p.FileUrl,z.AreaName as Zone,e.AreaName as Estate,c.PropName as Construction,h.Title as Title,h.Floor as Floor,h.TotalFloor as TotalFloor,h.Area as Area,h.Price as Price,max(pic.PicUrl) as PicUrl
FROM tb_HousesPubed p
left join tb_HousesSell h on p.HouseId=h.HouseId
left join tb_Area z on h.Zone=z.Id
left join tb_Area e on h.Estate=e.Id
left join tb_HouseProperties c on h.Construction=c.ID
left join tb_PicHouse pic on pic.HouseId=h.HouseId
where h.IsPicture='True'
group by p.ID,p.HouseId,p.LastUpdateTime,p.Hit,p.FileUrl,z.AreaName,e.AreaName,c.PropName,h.HouseId,Title,Floor,TotalFloor,Area,Price,PicUrl
having count(p.ID)>=1
order by p.Hit desc
没放到GROUP BY里的列,全加MAX();
不要加HAVING