mysql 在一个请求中计算多个表

Question

我有以下输出:

    root@localhost [~]# mysql -e "SELECT TABLE_ROWS from information_schema.Tables where TABLE_SCHEMA= 'testdb' && TABLE_NAME = 'requests';"
    +------------+
    | TABLE_ROWS |
    +------------+
    |    9566846 |
    +------------+

    root@localhost [~]# mysql -e "select count(*) from testdb.requests where created_at like '2012%';"
    +----------+
    | count(*) |
    +----------+
    |   301438 |
    +----------+
    root@localhost [~]# mysql -e "select count(*) from testdb.requests where created_at like '2013%';"
    +----------+
    | count(*) |
    +----------+
    |    24917 |
    +----------+

如何更好，使用 mysql 请求对一个请求执行相同的操作以获得新的输出，如

    +------------------+-----------------------+
    | year             | count(*)              |
    +------------------+-----------------------+
    | 2009             | 1066268               |
    | 2010             | 6799553               |
    | 2011             | 1374685               |
    | 2012             | 301438                |
    | 2013             | 24917                 |
    | total            | 9566846               |
    +------------------+-----------------------+

提前谢谢你，Evgheni

Answer 1

试试这个

SELECT YEAR(created_at) AS `year`,
       COUNT(*) AS `count` 
  FROM testdb.requests 
 GROUP BY YEAR(created_at)
 UNION ALL
SELECT 'total' AS `year`, 
       TABLE_ROWS AS `count`
  FROM information_schema.Tables 
 WHERE TABLE_SCHEMA= 'testdb' AND 
       TABLE_NAME = 'requests'

或

SELECT YEAR(created_at) AS `year`,
       COUNT(*) AS `count` 
  FROM testdb.requests 
 GROUP BY YEAR(created_at)
 UNION ALL
SELECT 'total' AS `year`, 
       COUNT(*) AS `year`
  FROM testdb.requests 

它产生这样的输出:

+-------+-------+
| year  | count |
+-------+-------+
| 2012  |     6 |
| 2013  |     7 |
| total |    13 |
+-------+-------+

这里是 sqlfiddle