Select Top 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small From 出售 As a left join 图片房源 as b on a.id = b.Info_ID where b.TypeInfo = '出售' group by b.id,a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small Order By a.ID Desc 有group by 不解决问题
SELECT TOP 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small FROM 出售 AS a RIGHT JOIN 图片房源 AS b ON a.id = b.Info_ID AND b.TypeInfo = '出售' ORDER BY a.ID DESC 试下,猜的.
try:Select Top 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small From 出售 As a left join 图片房源 as b on a.id = b.Info_ID where b.TypeInfo = '出售' and not exists (select 1 from 图片房源 where Info_ID=b.Info_ID and ID>b.ID) Order By a.ID Desc
在“图片房源”最后两条记录的Info_id是重复的。如果要结果中只有一个可这样做。 Select Distinct Top 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small From 出售 As a right join 图片房源 as b on a.id = b.Info_ID where b.TypeInfo = '出售' Order By a.ID Desc 这样不OK了。
出售表中的ID与图片房源中的Info_ID是一对多的关系
1 147284 UpLoadPic/2008-9/285985.jpg UpLoadPic/2008-9/2085985.jpg_s.jpg 出租
2 147284 UpLoadPic/2008-9/200986.jpg UpLoadPic/2008-9/20086.jpg_s.jpg 出租
3 147284 UpLoadPic/2008-9/2087.jpg UpLoadPic/2008-9/2008.jpg_s.jpg 出租
4 147283 UpLoadPic/2008-9/2265.jpg UpLoadPic/2008-9/s_20065.jpg 出租
5 147283 UpLoadPic/2008-9/2266.jpg UpLoadPic/2008-9/s_2008266.jpg 出租
6 147283 UpLoadPic/2008-9/20089267.jpg UpLoadPic/2008-9/s_200867.jpg 出租
7 61448 UpLoadPic/2008-9/20057.jpg UpLoadPic/2008-9/s_29157.jpg 出售
8 61448 UpLoadPic/2008-9/20088.jpg UpLoadPic/2008-9/s_2009158.jpg 出售出售表id 标题 户型 建筑面积 出售价格
61448 XX小区 二居室 50m2 150
61445 XX小区 二居室 50m2 150
61446 XX小区 二居室 50m2 150
Select Top 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small
From 出售 As a left join 图片房源 as b on a.id = b.Info_ID
where b.TypeInfo = '出售'
group by b.id,a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small
Order By a.ID Desc
有group by 不解决问题
SELECT TOP 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small
FROM 出售 AS a RIGHT JOIN 图片房源 AS b
ON a.id = b.Info_ID AND b.TypeInfo = '出售'
ORDER BY a.ID DESC 试下,猜的.
From 出售 As a left join 图片房源 as b on a.id = b.Info_ID
where b.TypeInfo = '出售' and not exists (select 1 from 图片房源 where Info_ID=b.Info_ID and ID>b.ID)
Order By a.ID Desc
7 61448 UpLoadPic/2008-9/20057.jpg UpLoadPic/2008-9/s_29157.jpg 出售
8 61448 UpLoadPic/2008-9/20088.jpg UpLoadPic/2008-9/s_2009158.jpg 出售 如果 出售 表中也有重复的ID
那就肯定有重复的数据了不知楼主要得到什么样的效果,楼主可以给出你的查询出的重复数据吗?
在“图片房源”最后两条记录的Info_id是重复的。如果要结果中只有一个可这样做。
Select Distinct Top 6 a.id,a.标题,a.户型,a.建筑面积,a.出售价格,b.FilePath_Small
From 出售 As a right join 图片房源 as b on a.id = b.Info_ID
where b.TypeInfo = '出售' Order By a.ID Desc 这样不OK了。