时间戳

首页 > 解决方案 > 如何使用 MySQL `SELECT FROM` 作为查询本身的一部分的表？

mysql - 如何使用 MySQL `SELECT FROM` 作为查询本身的一部分的表？

问题描述

假设我有多个具有相同架构的表：

CREATE TABLE `tbl01`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` TINYTEXT,
  `data` INT
);

CREATE TABLE `tbl02`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` TINYTEXT,
  `data` INT
);

CREATE TABLE `tbl03`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` TINYTEXT,
  `data` INT
);

-- etc. ------------------

INSERT INTO `tbl01` (`name`, `data`) VALUES
('row 1', 1),
('row 2', 1),
('row 3', 3);

INSERT INTO `tbl02` (`name`, `data`) VALUES
('cube', 1),
('circle', 0);

INSERT INTO `tbl03` (`name`, `data`) VALUES
('one', 1);

然后是一个表，其中一列中包含所有其他表的名称：

CREATE TABLE `AllTbls`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tblnm` VARCHAR(64) NOT NULL UNIQUE,
  `desc` TINYTEXT,
  `flgs` BIGINT UNSIGNED
);

INSERT INTO `AllTbls` (`tblnm`, `desc`, `flgs`) VALUES
('tbl01', 'Table 1', 0),
('tbl02', 'Table two', 1),
('tbl03', '3rd table', 0);

因此，如果我想编写一个查询来检索其中的内容AllTbls并在一列中包含每个相应表中的行数，我认为以下方法是这样做的：

SELECT *, `tblnm` as TblName, (SELECT COUNT(*) FROM TblName) as cntRws 
  FROM `AllTbls` ORDER BY `id` ASC LIMIT 0,30;

但这会返回一个错误：

#1146 - 表 'database.TblName' 不存在

我知道我可以在多个查询中执行此操作（使用编程语言中的循环），但是可以在一个查询中执行此操作吗？

PS。我在用着MySQL v.5.7.28

标签： mysqlselectcount

解决方案

简单的答案是：“你不能”

表名不应该像变量一样使用，以这种方式保存数据。你应该有一张桌子：

tblContractCounts
Client, ContractCount
-------------------
IBM, 1
Microsoft, 3
Google, 2

不是三个表：

tblIBMContractCounts
ContractCount
1

tblMicrosoftContractCounts
ContractCount
3

tblGoogleContractCounts
ContractCount
2

如果您的表的数量是已知的并且是固定的，您也许可以通过创建一个将它们重新组合在一起的视图来解决问题，或者着手将它们全部放入一个表中，使用单独的视图命名为旧名称，这样事情就可以继续工作直到你可以改变它们。如果一直添加新表，则说明数据建模存在缺陷，需要进行更正。在这种情况下，您必须使用编程语言（前端或存储过程）来构建单个查询：

//pseudo code
strSql = ""
for each row in dbquery("Select name from alltbls")
  strSql += "select '" + row.name + "' as tbl, count(*) as ct from " + row.name + " union all "
next row
strSql += "select 'dummy', 0"

result = dbquery(strSql)

不一定非要由您的前端来执行此操作——您也可以在 mysql 中执行此操作并利用动态 sql/EXECUTE。请参阅THIS ANSWER我们如何使用上述逻辑连接字符串，以便字符串包含 sql 查询，然后执行查询。信息模式将为您提供获取所有当前表名列表所需的信息

但是您所做的只是解决您的数据建模已损坏的事实；我建议改为修复它

ps：INFORMATION_SCHEMA 对带有名称的表进行了粗略的计数，在这种特殊情况下，这可能足以满足您的需求

select table_name, table_rows from infornation_schema.tables where table_name like ...

推荐阅读