如何将循环中的这两个查询转换为单个JOINed查询？

问题描述：

I am currently trying to get data from my table (mostKills by Weapon in a table with over 300 kills). Initially I did a normal query

$q = $mysql->query("SELECT * FROM `kills`") or die($mysql->error);

but when I tried to

            $query2 = $mysql->query("SELECT `killerID`, COUNT(`killerID`) AS tot_kills FROM `kills` WHERE `killText` LIKE '%$gun%' GROUP BY `killerID` ORDER BY `tot_kills` DESC;") or die($mysql->error);
            $kData = $query2->fetch_assoc();

            $query3 = $mysql->query("SELECT `Username` FROM `players` WHERE `ID` = '" . $kData['killerID'] . "'") or die($mysql->error);
            $uData = $query3->fetch_assoc();

            $array[$gun]['Kills']++;
            $array[$gun]['Gun'] = $gun;
            $array[$gun]['BestKiller'] = $uData['Username'];
            $array[$gun]['killAmount'] = $kData['tot_kills'];

            function sortByKills($a, $b) {
                return  $b['Kills'] - $a['Kills'];
            }

            usort($array, 'sortByKills');

            foreach($array as $i => $value) 
            {
                // table here
            }

I had to do it in a while loop, which caused there to be around 600 queries, and that is obviously not acceptable. Do you have any tips on how I can optimize this, or even turn this into a single query?

I heared JOIN is good for this, but I don't know much about it, and was wondering if you guys could help me

答

Try this...

I added a inner join and added a username to your select clause. The MIN() is just a way to include the username column in the select and will not have an impact on you result as long as you have just 1 username for every Killerid

SELECT `killerID`
    , COUNT(`killerID`) AS tot_kills
    , MIN(`Username`) AS username
FROM `kills`
INNER JOIN `players`
    ON `players`.`id` = `kills`.`killerid`
WHERE `killText` LIKE '%$gun%'
GROUP BY `killerID`
ORDER BY `tot_kills` DESC

答

SELECT kills.killerID, count(kills.killerID) as killTotal, players.Username FROM kills, players WHERE kills.killText LIKE '%$gun%' AND players.ID` = kills.killerID GROUP BY kills.killerID ORDER BY kills.tot_kills DESC

答

Here is a good place to learn some more about joins. http://www.sitepoint.com/understanding-sql-joins-mysql-database/

The best way is to have your own knowledge so you can be able to tune up your select queries.

Also put more indexes to your DB, and try to search and join by index.

如何将循环中的这两个查询转换为单个JOINed查询？

相关推荐