GROUP BY和JOIN的ORDER BY问题

安德里亚·科雷利(Andrea Corelli)

首先要为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无法正常工作。任何提示都是高度赞赏。

谢谢,安德里亚

manuskc

我需要计算给定时间段内每张照片的票数,如果出现平局,我需要首先检查获得最后一票的那张票。

只是为了获得按投票和最后投票时间排序的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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章