StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I originally thought this was quite simple, but I was mistaken in that, as there are some complicating factors you have to be aware of.</p> <p>First of, you'll have to do a cross product of the two tables. The cleanest way to do this is to have two tables in the <code>FROM</code> statement. You'll get something like this:</p> <pre><code>SELECT SUM(number_of_view) AS view_number, author, badge FROM table1, table2 </code></pre> <p>This will each row from the first table once for each badge. We haven't summed up the views yet, so that's the next thing we do. The first try is this:</p> <pre><code>SELECT SUM(number_of_view) AS view_number, author, badge FROM table1, table2 GROUP BY number_of_view </code></pre> <p>However, you'll see now that the view counts aren't right and we'll see only one badge per person, while we wanted a row with each badge for each user. In fact, the badges are grouped as well, which is why the displayed number_of_view is the actual number of view(s) times the number of badges in the system. To fix this we do add the badge to the group by column, so it doesn't get squashed into the other results:</p> <pre><code>SELECT SUM(number_of_view) AS view_number, author, badge FROM table1, table2 GROUP BY number_of_view, badges </code></pre> <p>If your second table has an id column, I would use that instead because it is better for performance (depending a bit on the actual type used for the badge field), but there was none in the table as give and this does work.</p> <p>Now, we need to filter out the badges that a user hasn't earned to make sure that only the right badges are left. This would go something like this:</p> <pre><code>SELECT SUM(number_of_view) AS view_number, author, badge FROM table1, table2 GROUP BY number_of_view, badges HAVING number_of_view >= table2.`from` AND number_of_view < table2.to </code></pre> <p>(Note that I had to escape from, as it is a keyword in SQL.) Here we will find, though, that the query doesn't know the table2.from column. This is because the <code>HAVING</code> part of a query doesn't look at the tables (<code>WHERE</code> does that) but looks at the selected columns instead (since that is what was filtered by <code>GROUP BY</code>). So, we need to add these columns to the selected ones:</p> <pre><code>SELECT SUM(number_of_view) AS view_number, author, badge, `from`, to FROM table1, table2 GROUP BY number_of_view, badges HAVING number_of_view >= table2.`from` AND number_of_view < table2.to </code></pre> <p>We finally get what we want. You can see the query in action here: <a href="http://sqlfiddle.com/#!8/e78c8/1" rel="nofollow">http://sqlfiddle.com/#!8/e78c8/1</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload