mysql - 如何使用单个查询获取存储在不同变量中的多个表行的计数？

Question

我有 4 个不同的表，我想获取每个表的行数并获取 4 个不同变量的计数(*)。

我使用的查询是:

 SELECT count(*) as count1 FROM `table1` 
 UNION ALL 
 SELECT count(*) as count2 FROM `table2`
 UNION ALL 
 SELECT count(*) as count3 FROM `table3`
 UNION ALL
 SELECT count(*) as count4 FROM `table4`

查询给出了每行的计数，但所有值都存储在相同的变量名称中。

 [
  {
    "count1": 16171
  }, 
  {
    "count1": 5928
  }, 
  {
    "count1": 33318
  }, 
  {
    "count1": 5877
  }
 ]

有什么方法可以得到输出；

 [
  {
    "count1": 16171
  }, 
  {
    "count2": 5928
  }, 
  {
    "count3": 33318
  }, 
  {
    "count4": 5877
  }
 ]

提前致谢。

Answer 1

UNION ALL 联合行。听起来您只想列出选择列表中的每个计数。试试这个:

SELECT
  (SELECT COUNT(*) FROM `table1`) AS count1,
  (SELECT COUNT(*) FROM `table2`) AS count2,
  (SELECT COUNT(*) FROM `table3`) AS count3,
  (SELECT COUNT(*) FROM `table4`) AS count4
;