如何按照表在MYSQL中被使用的次数降序列出表？

Question

我需要让脚本列出关系表的名称以及每个表被使用的总次数。

结果必须按照每个关系表被使用的总次数的降序排列。

到目前为止，我的代码如下：

WITH ALLDRINKS_COUNT AS (
SELECT 'ALLDRINKS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ALLDRINKS%'),
SERVES_COUNT AS(
SELECT 'SERVES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%SERVES%'),
ORDERS_COUNT AS (
SELECT 'ORDERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ORDERS%'),
LIKES_COUNT AS (
SELECT 'LIKES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LIKES%'), 
LOCATED_COUNT AS (
SELECT 'LOCATED' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LOCATED%'), 
DRINKERS_COUNT AS (
SELECT 'DRINKERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%DRINKERS%') 

SELECT * FROM ALLDRINKS_COUNT
UNION 
SELECT * FROM SERVES_COUNT
UNION
SELECT * FROM ORDERS_COUNT
UNION 
SELECT * FROM LIKES_COUNT
UNION
SELECT * FROM LOCATED_COUNT
UNION 
SELECT * FROM DRINKERS_COUNT;

下面是输出的样子：

+------------+----------+
| TABLE_NAME | COUNT(*) |
 +------------+----------+
| ALLDRINKS  |        5 |
| SERVES     |       12 |
| ORDERS     |        6 |
| LIKES      |        5 |
| LOCATED    |        1 |
| DRINKERS   |        2 |
+------------+----------+
6 rows in set (0.00 sec)

所需的输出为：

+------------+----------+
| TABLE_NAME | COUNT(*) |
+------------+----------+
| SERVES     |       12 |
| ORDERS     |        6 |
| ALLDRINKS  |        5 |
| LIKES      |        5 |
| DIRNKERS   |        2 |
| LOCATED    |        1 |
+------------+----------+
6 rows in set (0.00 sec)

Answer 1

尝试像这样子查询您的查询

SELECT TABLE_NAME, COUNT_A FROM(

WITH ALLDRINKS_COUNT AS (
SELECT 'ALLDRINKS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ALLDRINKS%'),
SERVES_COUNT AS(
SELECT 'SERVES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%SERVES%'),
ORDERS_COUNT AS (
SELECT 'ORDERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ORDERS%'),
LIKES_COUNT AS (
SELECT 'LIKES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LIKES%'), 
LOCATED_COUNT AS (
SELECT 'LOCATED' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LOCATED%'), 
DRINKERS_COUNT AS (
SELECT 'DRINKERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%DRINKERS%') 

SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM ALLDRINKS_COUNT
UNION 
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM SERVES_COUNT
UNION
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM ORDERS_COUNT
UNION 
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM LIKES_COUNT
UNION
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM LOCATED_COUNT
UNION 
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM DRINKERS_COUNT) t
ORDER BY COUNT_A DESC;