求救:返回20条记录,
求救:
select * from
(
(
select top 80 p.PropertyID as [Propertyid],Trade [交易类型] ,e.EstateName+(case len(pt.propertyid) when 32 then '(图)' else '' end) as [物业名称],e.Address as [所在地段],p.Square as [面 积],[总 价]=case Trade when '出租' then convert(varchar(50) ,RentPrice)+RentUnitName when '出售' then cast(p.price as varchar(50))+UnitName end,(cast(countF as varchar)+'房'+cast(countT as varchar)+'厅') as 户型,TrustDate as [委托日期] from ([Property] p left join Estate e on p.EstateID=e.EstateID) left join photo as pt on (p.PropertyID=pt.PropertyID or e.EstateID=pt.PropertyID) where Status='有效' and p.PropertyUsage like '%' and Trade like '%' and (e.Address like '%%' or e.Address2 like '%%' or p.Address like '%%') and e.EstateName like '%%' order by p.Propertyid desc
)
union all
(
select top 100 p.PropertyID as [Propertyid],Trade [交易类型] ,e.EstateName+(case len(pt.propertyid) when 32 then '(图)' else '' end) as [物业名称],e.Address as [所在地段],p.Square as [面 积],[总 价]=case Trade when '出租' then convert(varchar(50) ,RentPrice)+RentUnitName when '出售' then cast(p.price as varchar(50))+UnitName end,(cast(countF as varchar)+'房'+cast(countT as varchar)+'厅') as 户型,TrustDate as [委托日期] from ([Property] p left join Estate e on p.EstateID=e.EstateID) left join photo as pt on (p.PropertyID=pt.PropertyID or e.EstateID=pt.PropertyID) where Status='有效' and p.PropertyUsage like '%' and Trade like '%' and (e.Address like '%%' or e.Address2 like '%%' or p.Address like '%%') and e.EstateName like '%%' order by p.Propertyid desc
)
) temp_prod
group by [PropertyID],[交易类型],[物业名称],[所在地段],[面 积],[总 价],[户型],[委托日期] having count(PropertyID)<=1
order by Propertyid desc
返回的不是20条记录
其中Property的类型是char32