首先,对于新手的问题,我是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工作不正常。任何暗示都是高度谨慎的。
谢谢,Andrea
用以下命令替换ORDER BY:
ORDER BY `Last Vote Time` DESC, `Last Vote Date` DESC, `Total Votes` DESC;
由于"(空格)字符,您需要使用反勾号。此外,ORDER BYs的排序也会有所不同。
此外,您需要在GROUP BY中包含更多字段,否则数据将在MYSQL中丢失(其他DBMS会使您的查询无效)。
将这些添加到您的群组BY:
GROUP BY v.Photo_id, `Last Vote Date`,`Last Vote Time`
(如果其他任何东西随着时间的推移而改变(例如名称),那么这可能会在以后引起问题,但这是另一回事)
我需要在给定的时间片上为每张照片计算选票,如果票数相等,我需要先检查最后一张选票
只需获取按投票和上次投票时间排序的照片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;
您可以将此查询的结果与其他表连接,以获得所需的结果。