首先要为newbee问题道歉,我对MySQL还是很陌生。
我有3张桌子可以做一个简单的摄影比赛:
注册:
ID
UserName
name
LastName
Email
Mobile
照片库:
ID
ImageName
user_id
Status=1
投票:
Photo_ID
Date
Time
Status
我需要计算给定时间段内每张照片的票数,如果出现平局,我需要首先检查获得最后一票的那张票。
我提出的查询是:
SELECT v.Photo_ID AS "Photo ID",
g.ImageName AS "Photo URL",
r.UserName AS "Username",
r.name AS "Name",
r.LastName AS "Surname",
r.Email AS "E-Mail",
r.Mobile AS "Phone",
v.Date AS "Last Vote Date",
v.Time AS "Last Vote Time",
COUNT(Photo_ID) AS "Total Votes"
FROM photovote v
INNER JOIN photogallery g ON v.Photo_ID = g.ID
INNER JOIN registration r ON g.user_id = r.ID
WHERE v.Status =1
AND g.Status =1
AND v.Date >20130728
AND v.Date <20130805
GROUP BY 1
ORDER BY 10 DESC, 8 DESC, 9 DESC
但是,ORDER BY无法正常工作。任何提示都是高度赞赏。
谢谢,安德里亚
我需要计算给定时间段内每张照片的票数,如果出现平局,我需要首先检查获得最后一票的那张票。
只是为了获得按投票和最后投票时间排序的photo_id:
select Photo_ID, count(*) as votes, max(Timestamp(Date,Time)) as lastVoteTime
from photovote
where Date > [start] and Date < [end] and status = 1
group by Photo_ID
order by votes desc, lastVoteTime asc;
您可以将此查询的结果与其他表联接以获得所需的结果。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句