mySQL Join，如何从多个表中获取行数？

Question

假设我有 3 个表:

p、r 和 l

我想选择每个表中有多少行，并将它们定义为表名称的变量。

$sevenQ = mysql_query("SELECT count(*) FROM `p` AS p WHERE `added` LIKE '" . $today . "%' union 
SELECT count(*) FROM `r` AS r WHERE `added` LIKE '" . $today . "%' union
SELECT count(*) FROM `l` AS l WHERE `added` LIKE '" . $today . "%'");

$sevenQ['p'] = total amount of rows in p;
$sevenQ['r'] = total amount of rows in r;
$sevenQ['l'] = total amount of rows in l;

如何返回此类查询的前 3 个变量？

Answer 1

select count(*) from tablea where ... union select count(*) from tableb where ... union select count(*) from tablec where ...;

您将获得三行，其中包含三个表的行数。 (在mysql 5上测试)

关于您的更新:

你的例子无法运行。 AS 关键字的使用方式也是错误的。你应该这样做:

$res = mysql_query("SELECT \"p\" AS name, count(*) AS cnt FROM `p` WHERE `added` LIKE '" . $today . "%' union 
SELECT \"r\" AS name, count(*) AS cnt FROM `r` WHERE `added` LIKE '" . $today . "%' union
SELECT \"l\" AS name, count(*) AS cnt FROM `l` WHERE `added` LIKE '" . $today . "%'");

while( $row = mysql_fetch_array($res) ){
    $sevenQ[$row['name']] = $row['cnt'];
}

(未经测试的代码)