SQL Help needed! -
RedFusion - 30.12.2016
Hello!
I'd really appreciate some help from someone with experience in MySQL (multithreaded)
What i want to accomplish is selecting deaths from a table within a datetime range, and then sort them by amount of kills by each user ID. (it's for a leaderboard)
Let's call the column names like this:
pawn Код:
deathid // auto incrementing index
deaduserid // userid of dead player
killuserid // userid of killer player
How would one fix this in an efficient way?
Pointing me in the right direction would be appreciated, but if you can provide a long answer i'd appreciate it even more ofcourse.
Re: SQL Help needed! - iLearner - 30.12.2016
Quote:
selecting deaths from a table within a datetime range, and then sort them by amount of kills by each user ID
|
I am a bit confused on this.
Re: SQL Help needed! -
RedFusion - 30.12.2016
I think i explained it poorly.
Basically, whenever a player dies a row gets inserted to this table.
The userid of the killed player, killer player and an auto incremented ID is saved to the table.
Let's call the table "deaths".
What i would like to do, is to find out who killed the most players during a certain month.
Re: SQL Help needed! -
BiosMarcel - 30.12.2016
Here is the table structure you'd need:
PHP код:
CREATE TABLE deaths (ID INT AUTO_INCREMENT, DeathID INT NON NULL, KillerID INT NON NULL, TimeOfDeath TIMESTAMP DEFAULT CURDATE());
This will set the TimeOfDeath to the actual Date & Time as soon as you ceate a new tupel (row).
So, next lets go over to the Select Statements
PHP код:
SELECT DeathID, KillerID, TimeOfDeath, COUNT(KillerID) AS Kills FROM deaths WHERE MONTH(TimeOfDeath)=!!!INSERT MONTH HERE!!! GROUP BY KillerID ORDER BY Kills DESC
So, if i didn't do anything wrong, this should give you all the kills within 1 Month grouped by the killers id and ordered by kills
Re: SQL Help needed! -
Vince - 30.12.2016
Selecting rows within a range is simple enough, but if you're not storing time and date with each kill then you don't have anything to apply that range to.
You can count kills for each userid, however:
PHP код:
select killuserid, count(*) as numkills from kills group by killuserid order by numkills desc limit 10
Re: SQL Help needed! -
RedFusion - 30.12.2016
Oh it's that simple?
I've tried some different queries now and it's working perfectly.
Thanks for the help!!