Php Mysql 多重查询_PHP-SQL多重查询-太重了？

是的,要获得所需的结果集,查询太多了,开销也太多了,繁重的工作也花了很多.

您可以通过一条语句并在一行上从$tbl_players表中获得所有九个计数.您拥有的那9条独立的语句正在准备9条独立的结果集,并且您正在将结果集中的所有这些行提取给客户端以获取计数. (也许某个地方有一些优化,但是MySQL正在准备所有这些行以返回给客户端.)使用如下语句,让MySQL只返回您想要的计数会更有效.

SELECT SUM(IF(p.Console = 'XBOX360' ,1,0)) AS xbox

, SUM(IF(p.Console = 'PS3' ,1,0)) AS ps3

, SUM(IF(p.Console = 'PC' ,1,0)) AS pc

, SUM(IF(p.Console = 'XBOX360' AND p.fathread LIKE 'http%' ,1,0)) AS xbfa

, SUM(IF(p.Console = 'PS3' AND p.fathread LIKE 'http%' ,1,0)) AS psfa

, SUM(IF(p.Console = 'PC' AND p.fathread LIKE 'http%' ,1,0)) AS pfa

, SUM(IF(p.Console = 'XBOX360' AND p.crthread LIKE 'http%' ,1,0)) AS xbfr

, SUM(IF(p.Console = 'PS3' AND p.crthread LIKE 'http%' ,1,0)) AS pscr

, SUM(IF(p.Console = 'PC' AND p.crthread LIKE 'http%' ,1,0)) AS pcr

FROM $tbl_players p

这样一来,您将返回具有9个值的单行.这将减少您获得的大量代码,并显着提高性能.

同样,您也可以通过一条语句从$tbl_interactive表中获得所有这些计数：

SELECT SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Player' ,1,0)) AS xbfi

, SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Club' ,1,0)) AS xbci

, SUM(IF(t.Console = 'PS3' AND t.Type = 'Player' ,1,0)) AS psfi

, SUM(IF(t.Console = 'PS3' AND t.Type = 'Club' ,1,0)) AS psci

, SUM(IF(t.Console = 'PC' AND t.Type = 'Player' ,1,0)) AS pcfi

, SUM(IF(t.Console = 'PC' AND t.Type = 'Club' ,1,0)) AS pcci

FROM $tbl_interactive t

WHERE t.Console IN ('XBOX360','PS3','PC')

AND t.Type IN ('Player','Club')

AND $date < t.EndTime

while($row = mysql_fetch_array($tbl_interactive_counts))

{

$xboxfi = $row["xbfi"];

$xboxci = $row["xbci"];

$ps3fi = $row["psfi"];

$ps3ci = $row["psci"];

$pcfi = $row["pcfi"];

$pcci = $row["pcci"];

}

跟进：

当没有满足谓词的行(即与WHERE子句匹配)时,上述SQL语句有可能返回NULL而不是零.为了使该语句返回一个零计数来代替NULL,我们可以使用MySQL IFNULL()函数作为方便的速记：

SELECT IFNULL(SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Player' ,1,0)),0) AS xbfi

, IFNULL(SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Club' ,1,0)),0) AS xbci

, IFNULL(SUM(IF(t.Console = 'PS3' AND t.Type = 'Player' ,1,0)),0) AS psfi

, IFNULL(SUM(IF(t.Console = 'PS3' AND t.Type = 'Club' ,1,0)),0) AS psci

, IFNULL(SUM(IF(t.Console = 'PC' AND t.Type = 'Player' ,1,0)),0) AS pcfi

, IFNULL(SUM(IF(t.Console = 'PC' AND t.Type = 'Club' ,1,0)),0) AS pcci

FROM $tbl_interactive t

WHERE t.Console IN ('XBOX360','PS3','PC')

AND t.Type IN ('Player','Club')

AND $date < t.EndTime

请注意,我们只是包装了一个表达式：IFNULL(expr,0)

那只是一个等效的简写：IF(expr IS NULL,0,expr)

也等同于ANSI标准：CASE WHEN expr为NULL则0 ELSE expr END

(使用IFNULL,我们只需指定一次expr.)

或者,您可以让MySQL返回NULL,并在PHP中使用零来处理NULL的替换. (请注意,在$row数组中获取的值仍将包含NULL,但会将零分配给标量：

$xboxfi = is_null($row["xbfi"]) ? 0 : $row["xbfi"] ;