query structure - ignoring entries for the same event from multiple users?
One table in my MySQL database tracks game plays. It has the following structure:
SCENARIO_VICTORIES
[ID] [scenario_id] [game] [timestamp] [user_id] [winning_side] [play_date]
ID
is the autoincremented primary key. timestamp
records the moment of submission for the record. winning_side
has one of three possible values: 1, 2, or 0 (meaning a draw)
One of the queries done on this table calculates the victory percentage for each scenario, when that scenario's 开发者_如何学编程page is viewed. The output is expressed as:
- Side 1 win %
- Side 2 win %
- Draw %
and queried with:
SELECT winning_side, COUNT(scenario_id)
FROM scenario_victories WHERE scenario_id='$scenID'
GROUP BY winning_side ORDER BY winning_side ASC
and then processed into the percentages and such.
Sorry for the long setup. My problem is this: several of my users play each other, and record their mutual results. So these battles are being doubly represented in the victory percentages and result counts. Though this happens infrequently, the userbase isn't large and the double entries do have a noticeable effect on the data.
Given the table and query above - does anyone have any suggestions for how I can "collapse" records that have the same play_date & game & scenario_id & winning_side
so that they're only counted once?
It's a bit late but ideally you'd filter these duplicates out when they're inserted.
That said, you should be able to do a DISTINCT
inner query like so:
SELECT winning_side, COUNT(scenario_id)
FROM (
SELECT DISTINCT play_date, game, scenario_id, winning_side
FROM scenario_victories
WHERE scenario_id = '$scenID'
)
GROUP BY winning_side ORDER BY winning_side ASC
精彩评论