MySQL 按照条件统计多张表记录数总数

一、诉求

  数据库中有 4 张订单表，分别为 tbl_test_order01、tbl_test_order02、tbl_test_order03，tbl_test_order04，分别用于存储四个季度的订单表数据。各表的表结构均相同，以 tbl_test_order01 为例，其表结构如下所示。
  诉求：统计全年成功交易订单笔数。

mysql> show full columns from tbl_test_order01;
+------------+-----------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+-------------------
---------------------------------------------------+
| Field      | Type      | Collation       | Null | Key | Default           | Extra                       | Privileges                      | Comment
                                                   |
+------------+-----------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+-------------------
---------------------------------------------------+
| order_id   | char(21)  | utf8_general_ci | NO   | PRI |                   |                             | select,insert,update,references | 订单号
                                                   |
| order_at   | int(11)   | NULL            | NO   |     | 0                 |                             | select,insert,update,references | 订单金额,单位是分
                                                   |
| order_st   | char(2)   | utf8_general_ci | NO   |     |                   |                             | select,insert,update,references | 订单状态 00:成功 0
1:已退货 02:新建 03:失败 04:已关闭                 |
| order_dt   | char(8)   | utf8_general_ci | NO   |     |                   |                             | select,insert,update,references | 订单日期 yyyymmdd
                                                   |
| rec_crt_ts | timestamp | NULL            | NO   |     | CURRENT_TIMESTAMP |                             | select,insert,update,references | 创建时间
                                                   |
| rec_upd_ts | timestamp | NULL            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间
                                                   |
+------------+-----------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+-------------------
---------------------------------------------------+
6 rows in set (0.02 sec)

二、实现方法

  解决以上诉求，有多种方法，笔者采用 “派生表” 和 “视图” 两种方法，分述如下。

  2.1 派生表

  派生表是从 select 语句返回的虚拟表。派生表类似于临时表，但是在 select 语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。当 select 语句的 from 子句中使用独立子查询时，我们将其称为派生表。也就是在一个查询（外部查询）中嵌套另一个查询语句（内部查询），并使用内部查询的结果值作为外部查询条件。
  使用派生表实现上述诉求的 SQL 语句如下所示。

mysql> SELECT SUM(bishu) FROM (SELECT COUNT(1) AS bishu FROM tbl_test_order01 WHERE order_st = "00" UNION ALL SELECT COUNT(1) AS bishu FROM tbl_test_order02 WHERE order_st = "00" UNION ALL SELECT COUNT(1) AS bishu FROM tbl_test_order03 WHERE order_st = "00" UNION ALL SELECT COUNT(1) AS bishu FROM tbl_test_order04 WHERE order_st = "00" ) AS tbl_order;
+------------+
| SUM(bishu) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)

  其中 tbl_order 为派生表名。如果 SQL 语句中不写派生表名，会报如下错误，提示用户，每个派生表必须有自己的别名，派生表名不可缺少。

Every derived table must have its own alias

  2.2 视图

  使用 SQL 语法 CREATE VIEW <视图名> AS <SELECT语句>创建基于上述 4 张表的视图，命名为 view_test_order，SQL 语句如下：

mysql> DROP VIEW IF EXISTS view_test_order;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW view_test_order AS SELECT * FROM tbl_test_order01 UNION ALL SELECT * FROM tbl_test_order02 UNION ALL SELECT * FROM tbl_test_order03 UNION ALL SELECT * FROM tbl_test_order04;
Query OK, 0 rows affected (0.06 sec)

  查看数据库中的表和视图：

mysql> show full tables;
+----------------------------+------------+
| Tables_in_onldb            | Table_type |
+----------------------------+------------+
| tbl_test_order01           | BASE TABLE |
| tbl_test_order02           | BASE TABLE |
| tbl_test_order03           | BASE TABLE |
| tbl_test_order04           | BASE TABLE |
| view_test_order            | VIEW       |
+----------------------------+------------+
5 rows in set (0.02 sec)

  查看视图 view_test_order 的详细信息：

mysql> desc view_test_order;
+------------+-----------+------+-----+---------------------+-------+
| Field      | Type      | Null | Key | Default             | Extra |
+------------+-----------+------+-----+---------------------+-------+
| order_id   | char(21)  | NO   |     |                     |       |
| order_at   | int(11)   | NO   |     | 0                   |       |
| order_st   | char(2)   | NO   |     |                     |       |
| order_dt   | char(8)   | NO   |     |                     |       |
| rec_crt_ts | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
| rec_upd_ts | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
+------------+-----------+------+-----+---------------------+-------+
6 rows in set (0.02 sec)

mysql> SHOW CREATE VIEW view_test_order \G
*************************** 1. row ***************************
                View: view_test_order
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test_order` AS select `tbl_test_order01`.`order_id`
AS `order_id`,`tbl_test_order01`.`order_at` AS `order_at`,`tbl_test_order01`.`order_st` AS `order_st`,`tbl_test_order01`.`order_dt` AS `order_dt`,`tbl_test_orde
r01`.`rec_crt_ts` AS `rec_crt_ts`,`tbl_test_order01`.`rec_upd_ts` AS `rec_upd_ts` from `tbl_test_order01` union all select `tbl_test_order02`.`order_id` AS `ord
er_id`,`tbl_test_order02`.`order_at` AS `order_at`,`tbl_test_order02`.`order_st` AS `order_st`,`tbl_test_order02`.`order_dt` AS `order_dt`,`tbl_test_order02`.`r
ec_crt_ts` AS `rec_crt_ts`,`tbl_test_order02`.`rec_upd_ts` AS `rec_upd_ts` from `tbl_test_order02` union all select `tbl_test_order03`.`order_id` AS `order_id`,
`tbl_test_order03`.`order_at` AS `order_at`,`tbl_test_order03`.`order_st` AS `order_st`,`tbl_test_order03`.`order_dt` AS `order_dt`,`tbl_test_order03`.`rec_crt_
ts` AS `rec_crt_ts`,`tbl_test_order03`.`rec_upd_ts` AS `rec_upd_ts` from `tbl_test_order03` union all select `tbl_test_order04`.`order_id` AS `order_id`,`tbl_te
st_order04`.`order_at` AS `order_at`,`tbl_test_order04`.`order_st` AS `order_st`,`tbl_test_order04`.`order_dt` AS `order_dt`,`tbl_test_order04`.`rec_crt_ts` AS
`rec_crt_ts`,`tbl_test_order04`.`rec_upd_ts` AS `rec_upd_ts` from `tbl_test_order04`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.views WHERE table_name = "view_test_order" \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: onldb
          TABLE_NAME: view_test_order
     VIEW_DEFINITION: select `onldb`.`tbl_test_order01`.`order_id` AS `order_id`,`onldb`.`tbl_test_order01`.`order_at` AS `order_at`,`onldb`.`tbl_test_ord
er01`.`order_st` AS `order_st`,`onldb`.`tbl_test_order01`.`order_dt` AS `order_dt`,`onldb`.`tbl_test_order01`.`rec_crt_ts` AS `rec_crt_ts`,`onldb`.`tbl_te
st_order01`.`rec_upd_ts` AS `rec_upd_ts` from `onldb`.`tbl_test_order01` union all select `onldb`.`tbl_test_order02`.`order_id` AS `order_id`,`onldb`.`tbl
_test_order02`.`order_at` AS `order_at`,`onldb`.`tbl_test_order02`.`order_st` AS `order_st`,`onldb`.`tbl_test_order02`.`order_dt` AS `order_dt`,`onldb`.`t
bl_test_order02`.`rec_crt_ts` AS `rec_crt_ts`,`onldb`.`tbl_test_order02`.`rec_upd_ts` AS `rec_upd_ts` from `onldb`.`tbl_test_order02` union all select `on
ldb`.`tbl_test_order03`.`order_id` AS `order_id`,`onldb`.`tbl_test_order03`.`order_at` AS `order_at`,`onldb`.`tbl_test_order03`.`order_st` AS `order_st`,`sc
onldb`.`tbl_test_order03`.`order_dt` AS `order_dt`,`onldb`.`tbl_test_order03`.`rec_crt_ts` AS `rec_crt_ts`,`onldb`.`tbl_test_order03`.`rec_upd_ts` AS `rec_u
pd_ts` from `onldb`.`tbl_test_order03` union all select `onldb`.`tbl_test_order04`.`order_id` AS `order_id`,`onldb`.`tbl_test_order04`.`order_at` AS `orde
r_at`,`onldb`.`tbl_test_order04`.`order_st` AS `order_st`,`onldb`.`tbl_test_order04`.`order_dt` AS `order_dt`,`onldb`.`tbl_test_order04`.`rec_crt_ts` AS `
rec_crt_ts`,`onldb`.`tbl_test_order04`.`rec_upd_ts` AS `rec_upd_ts` from `onldb`.`tbl_test_order04`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.02 sec)

  通过视图查询全年成功交易笔数：

mysql> SELECT COUNT(1) AS bishu FROM view_test_order WHERE order_st = "00";
+-------+
| bishu |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

  当各表数据量很大时，经过几轮测试，发现方法 1 比方法 2 快很多。

文章参考：

• mysql派生表（Derived Table）简单解析&使用的小例子
• Mysql 按照条件 统计多张表记录数总数
• MySQL——查看视图
• MySQL教程92-MySQL查看视图