mysql - 如何获取当天的照片？

Question

我在 MySQL 数据库中有两个表:

照片:

PID - PhotoID [PK], 
DateOf - DateTime of uploading, 
UID -UserID (owner of the photo)

评分:

WhoUID - UserID who rated, 
DateOf - DateTime of rate, 
RatingValue - +1 or -1 (positive or negative), 
RatingStrength - coefficient (different for each user who vote)
PID - PhotoID, [FK]

真实评级值 = RatingValue * RatingStrength

获得“今日照片”的可能性有哪些？

规则，例如:

• 当天的照片必须在现场至少 24 小时(自上传时间起)
• 当天的照片必须至少有 10 票
• 当天的照片是自上传后 24 小时内具有最大真实评分值的照片
• 当天的新照片不得已在 photo_of_day 表中

UPD1。10 票表示 - 每张照片在 ratings 表中至少有 10 条记录

UPD2。是否可以获取确切日期时间的“今日照片”？例如，如何获取“2011-03-11”或“2011-01-25”当天的照片？

Answer 1

select p.PID from photos p, ratings r where
r.PID = p.PID and                             ; Link ratings to photo
p.DateOf >= '$day' and p.DateOf <= '$day' and ; Get the photos uploaded on the specified date
datediff(p.DateOf, now()) > 1 and             ; photo of the day must be on site at least 24 hours
count(r.*) >= 10 and                          ; photo should have at least 10 ratings
not exists(select p.PID from photo_of_day)    ; photo should not appear in photo_of_day
group by p.PID                                ; group the results by PID
order by sum(r.RatingValue*r.RatingStrength) desc ; order descending by RealRating
limit 1                                       ; limit the results to only one

这个查询可能需要一段时间，所以不要在每个页面请求上都这样做是有意义的。您通过在午夜运行一次的脚本将结果存储在 photo_of_day 中一次。