select b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,consultation b ,productphotos c where a.productId = b.productId and a.productId = c.productId
select top(1) b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,consultation b ,productphotos c where a.productId = b.productId and a.productId = c.productId order by c.ReleaseTime desc
SELECT commt.Id,commt.Title,commt.Content,commt.ProductID,prod.ProductTitle,prod.PromotionPrice,prodimg.ProductPhoto FROM commentary AS commt INNER JOIN product AS prod on(commt.ProductID=prod.Id) INNER JOIN productphotos AS prodimg ON(prod.Id=prodimg.ProductID)结果如图所示:(显示交叉结果集,并不是我想要的。)
select top(1) b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,consultation b ,productphotos c where a.productId = b.productId and a.productId = c.productId group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice order by c.ReleaseTime desc
这是我调试的代码:select top(1) b.id,b.title,b.content,b.ProductID,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,commentary b ,productphotos c where a.Id = b.ProductID and a.Id = c.ProductID group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime order by c.ReleaseTime desc select b.id,b.title,b.content,b.ProductID,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,commentary b ,productphotos c where a.Id = b.ProductID and a.Id = c.ProductID group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime order by c.ReleaseTime desc
这是我刚刚调试的查询语句:select top(1) b.id,b.title,b.content,b.ProductID,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,commentary b ,productphotos c where a.Id = b.ProductID and a.Id = c.ProductID group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime order by c.ReleaseTime desc select b.id,b.title,b.content,b.ProductID,a.producttitle,a.promotionPrice,c.ProductPhoto from product a ,commentary b ,productphotos c where a.Id = b.ProductID and a.Id = c.ProductID group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime order by c.ReleaseTime desc
try SELECT commt.Id,commt.Title,commt.Content,commt.ProductID,prod.ProductTitle,prod.PromotionPrice,prodimg.ProductPhoto FROM commentary AS commt INNER JOIN product AS prod on(commt.ProductID=prod.Id) INNER JOIN productphotos AS prodimg ON(prod.Id=prodimg.ProductID) WHERE prodimg.releasetime=(select top 1 releasetime from productphotos where ProductID=commt.ProductID and releasetime>=commt.dateandtime order by releasetime)
我现在商品图片表中每一个产品可能有三张以上的照片,不是一张,利用join查询,如何筛选出最新上传的产品的那个图片?
from product a ,consultation b ,productphotos c
where a.productId = b.productId and a.productId = c.productId
from product a ,consultation b ,productphotos c
where a.productId = b.productId and a.productId = c.productId
order by c.ReleaseTime desc
SELECT commt.Id,commt.Title,commt.Content,commt.ProductID,prod.ProductTitle,prod.PromotionPrice,prodimg.ProductPhoto
FROM commentary AS commt INNER JOIN product AS prod on(commt.ProductID=prod.Id)
INNER JOIN productphotos AS prodimg ON(prod.Id=prodimg.ProductID)结果如图所示:(显示交叉结果集,并不是我想要的。)
from product a ,consultation b ,productphotos c
where a.productId = b.productId and a.productId = c.productId
group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice
order by c.ReleaseTime desc
若把top(1)去掉的话就会出现重复,而把top(1)加上去的话,查询出来的记录就有一条。但是我数据库里面是有两条记录的。
from product a ,commentary b ,productphotos c
where a.Id = b.ProductID and a.Id = c.ProductID
group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime
order by c.ReleaseTime desc
select b.id,b.title,b.content,b.ProductID,a.producttitle,a.promotionPrice,c.ProductPhoto
from product a ,commentary b ,productphotos c
where a.Id = b.ProductID and a.Id = c.ProductID
group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime
order by c.ReleaseTime desc
from product a ,commentary b ,productphotos c
where a.Id = b.ProductID and a.Id = c.ProductID
group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime
order by c.ReleaseTime desc
select b.id,b.title,b.content,b.ProductID,a.producttitle,a.promotionPrice,c.ProductPhoto
from product a ,commentary b ,productphotos c
where a.Id = b.ProductID and a.Id = c.ProductID
group by b.id,b.title,b.content,b.productId,a.producttitle,a.promotionPrice,c.ProductPhoto,c.ReleaseTime
order by c.ReleaseTime desc
这个就是问题的所在,一个ProductID可能对应有多个图片,一个咨询记录对应一个ProductID,就意味这一个咨询记录可能对应多个图片,
但你现在想一个咨询记录取一张图片,如何取?依据是什么?
(百年树人),您好。
我的思路是这样的,首先用内边接查询出咨询表与商品表之间的共同的数据,然后查询出某个商品最新上传的图片信息,最后,能否合并到一起成为结果集。按道理就应该可以实现了。
SELECT commt.Id,commt.Title,commt.Content,commt.ProductID,prod.ProductTitle,prod.PromotionPrice,prodimg.ProductPhoto
FROM commentary AS commt
INNER JOIN product AS prod on(commt.ProductID=prod.Id)
INNER JOIN productphotos AS prodimg ON(prod.Id=prodimg.ProductID)
WHERE prodimg.releasetime=(select top 1 releasetime from productphotos where ProductID=commt.ProductID and releasetime>=commt.dateandtime order by releasetime)
(百年树人),您好。
刚刚调试了一下,无结果集。而去掉and releasetime>=commt.dateandtime之后,显示正常。有点不理解。在此,我非常感谢您这么晚仍在帮我解决问题。谢谢。
(百年树人),您说的是啊。
很荣幸能够通过这个问题结识到您,希望能够与您成为好朋友。
我的联系方式:15026976866 QQ:215645471。如有需要帮助,请联系我。