问题描述:

I have two tables, created thus (only pertinent info shown)

CREATE TABLE levels

(

id INT(6) UNSIGNED AUTO_INCREMENT,

gameid INT(6),

created DATETIME,

PRIMARY KEY(id,gameid)

)

CREATE TABLE votes

(

levelid INT(6) NOT NULL REFERENCES levels(id),

vote TINYINT(1),

date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(levelid)

)

Users get to vote on levels (either +1 or -1) and thus creating a votes record that points back to a level.

What I want to do with the data is this, in non-syntax:

SELECT * FROM levels ORDER BY

(select sum(vote) from votes where votes.levelid=levels.id and date is within the last week)

How could I turn that into a legitimate mySQL query? Everything I try gives me an extraordinarily cryptic error message ("Something's wrong! Check the manual!") and I'm only an advanced beginner at mySQL.

The goal is to return all my levels, sorted by whoever has the best votes sum in the last week... that way new levels will make their way on and older ones will fall off.

Thanks in advance!

Edit:

So here's an example of expected results:

Here's my data (rendered in English for clarity):

Levels:

LEVEL1 (id=1)

LEVEL2 (id=2)

votes:

+1 for LEVEL1 two days ago

+1 for LEVEL1 ten days ago

+1 for LEVEL1 twenty days ago

+1 for LEVEL2 one day ago

+1 for LEVEL2 three days ago

-1 for LEVEL2 thirty days ago

So I can do a query like this:

SELECT * FROM LEVELS ORDER BY (select sum(vote) from votes where levelid=levels.id)

and that gets me +3 for LEVEL1, and +1 for LEVEL2.

BUT, in the last seven days, I should see +1 for LEVEL1, and +2 for LEVEL2, thus returning LEVEL2 first.

Also, I get the general idea that "order by (select..." would be pretty slow.

One more edit:

This query works:

SELECT * FROM levels ORDER BY (SELECT SUM(vote) FROM votes WHERE levelid=levels.id AND votes.date>=( CURDATE() - INTERVAL 7 DAY )) DESC

...but is that the best way to do this sort of thing?

网友答案:

You can do this with a left join:

SELECT levels.*, coalesce(sum(votes.vote),0) as total_vote
FROM levels 
LEFT JOIN votes ON votes.levelid = levels.id
WHERE votes.date >=( CURDATE() - INTERVAL 7 DAY ))
GROUP BY levels.id
ORDER BY coalesce(sum(votes.vote),0) desc;

The coalesces here ensure that levels that haven't been voted on get a score (instead of null) and are sorted correctly.

相关阅读:
Top