mySQL Join，如何从多个表中获取行数？
mySQL Join，如何从多个表中获取行数？
提问于 2011-04-03 16:27:45
假设我有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个变量呢？
回答 4
Stack Overflow用户
发布于 2011-04-03 16:34:11
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'];
}(未测试的代码)
Stack Overflow用户
发布于 2011-04-03 16:30:43
为什么不使用三个select count(*) from tablename?
Stack Overflow用户
发布于 2011-04-03 16:33:45
您可以尝试(这是未经测试的)：
select "tablea" as name, count(*) as total from tablea where stuff=1
union "tableb", count(*) from tableb where stuff=1
union "tablec", count(*( from tablec where stuff=1你可能需要做一些语法上的改变，这台机器不能访问mysql来解析它。
这应该会给你
name total
tablea 5
tableb 10
tablec 2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5528479
复制相关文章
相似问题
腾讯云开发者
